Learn how to generate a text based report for your Active Directory with the help of VB Script.
14
How to Get Active Directory Accounts Information Using VB Script
Window Server 2003/2008 Active Directory has lacks tool to run reports on things like current User Accounts, Disable Accounts, Organizational Units etc. There are many tools out there like ADManager Plus or AD Reports that will do that, but most of them are limited for free use only.
So how do you get your hands on this report for free?
The Magic Script
If you know Visual Basic Scripting (VBS), you can pull out any report from any Active Directory object really easy.
As shown below, this VB Script will generate a list of information about the user Username, First Name, Last Name and Email Address. This list will then be exported to Microsoft Excel as comma separated value (CSV) file.
<%
Option Explicit
' Generate an excel csv file with user.csv file name.
Const REPORT_FILE = "users.csv"
Const ADS_SCOPE_SUBTREE = 2
Const ADS_UF_ACCOUNTDISABLE = 2
Dim objFileSystem, objFile, objConnection, objCommand, objRootDSE, objRecordSet
Dim strUsername, strFirstname, strLastname, strEmail
Dim intUAC
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objFile = objFileSystem.OpenTextFile(REPORT_FILE, 2, True, 0)
objFile.WriteLine "AD Username,First Name,Last Name,E-mail,Company"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
objCommand.CommandText = "SELECT sAMAccountName, userAccountControl, givenName, sn, mail, company " &_
"FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") &_
"' WHERE objectClass='user' AND objectCategory='person'"
Set objRootDSE = Nothing
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Cache Results") = False
Set objRecordSet = objCommand.Execute
While Not objRecordSet.EOF
intUAC = objRecordSet.Fields("userAccountControl")
If intUAC And ADS_UF_ACCOUNTDISABLE Then
' Account is Disabled, ignore it.
Else
On Error Resume Next
strUsername = "" : strUsername = objRecordSet.Fields("sAMAccountName")
strFirstname = "" : strFirstname = objRecordSet.Fields("givenName")
strLastname = "" : strLastname = objRecordSet.Fields("sn")
strEmail = "" : strEmail = objRecordSet.Fields("mail")
strCompany = "" : strEmail = objRecordSet.Fields("company")
On Error Goto 0
objFile.WriteLine strUsername & "," & strFirstname & "," & strLastname & "," & strEmail & "," & strCompany
End If
objRecordSet.MoveNext
Wend
objConnection.Close
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
%>
Script Execution
Just copy and paste this code to your notepad or any text editor you have, and save it as with .vbs extension. To run them, just double click on the file and it will automatically generate a csv file in the same root directory from where you save the .vbs file.
Here is the sample image on how your report will look like:
Extra Information
Of course, that is not the only information that you can get from the Active Directory. Below are some of the field name that you can query out to be included in your csv file.
Add this to code line #26 and add in the appropriate information in code line #45 and you are done. But before that, make sure you give this extra information a title in code line #16.
- Username: sAMAccountName
- First name: givenname
- Initials: middleName
- Last name: sn
- First and Last name: displayName
- Description: description
- Address: physicaldeliveryofficename
- Telephone: telephonenumber
- Mail: mail
- Homepage: wwwhomepage
- Street: streetAddress
- vPostofficebox: postofficebox
- City: l (that is L lowercase)
- State: st
- Postal code: postalcode
- Country: co
- Home phone: homephone
- Pager No.: pager
- Mobile phone No.: mobile
- Fax No.: facsimiletelephonenumber
- IP Phone: ipphone
- Remarks: info
- Title: title
- Department: department
- Company: company
- Manager: manager
To discover more LDAP attributes, go to start > run and typed in this command:
CSVDE -f Exportfile.csv. Then open Exportfile.csv with Microsoft Excel application.
http://www.moniestudios.com – da best. Keep it going!
Thank you
Tania
I am happy you liked them, Thanks.
Any tutorial?
I have a list of tutorial waiting to be published here, but it has to go through some stage for proof read and double checking to make sure it is ready before publishing them. Any specific tutorial that you are looking for?
Building website from scratch
Great idea! I’ll be doing tutorial like that really soon. In the mean time, navigate to my other tutorial or article, see if you can find something that interest you ^_^. Sooner or later you will need that as well!
da best. Keep it going! Thank you
I have been looking looking around for this kind of information. Will you post some more in future? I’ll be grateful if you will.
More to come ^_^
You know, I don’t read blogs. But yours is really worth being read.
Hi Monie.
Really good example but I do get an error if I try to get the “description” field. I can get it fine it seems but when it tries to write to the logfile it gives me an error, “Microsoft VBScript runtime error: Type mismatch”.
The forest functional level is Windows 2000 but it is a 2003 server.
I have seen that this could be due to the description field is a “multi-value” field but I am not aware how to get (or print out) the info.
Do you know what this could be?
Did you mean you have problem running this code “CSVDE -f Exportfile.csv”?
Jeevan, you’re a machine. Good stuff.
I’m wrestling with a problem you might want to get your brain around. Basically creating a simple IT Request type of form in VBS (I was thinking of using Visual Studio for layout and general sexiness) BUT I also want them to email to an approver who can approve/deny the request on a website AND I want the forms saved in a database (either SQL or MySQL). The workflow tools out there are overkill for my three-forms but I need something more than the standard submission form.
Thoughts?
Unfortunately, I am a web based programmer (PHP, ASP) and yet have a little knowledge with VB. So sorry that I can’t help you on this. You might want to use web based instead of VBS?