Type the following VBScript into Notepad (with Word Wrap turned off)
and save it with a .vbs extension as
ExportAdUsers.vbs:
Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' File: ExportADUsers.vbs
' Updated: Dec 2003
' Version: 1.0
' Author: Dan Thomson, myITforum.com columnist
' I can be contacted at dethomson@hotmail.com
'
' Usage: This script should be run using cscript.
' cscript ExportADUsers.vbs
'
' Input: None
'
' Notes: This script exports all users whose accounts are not disabled,
' not expired, or do not have NoExport in their Notes section.
' There is also a constant "Users2Skip" to which you should add
' any names which should not be exported.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
' The name of the Access database to use
Const AccessDatabase = "EmployeeInfo.mdb"
' The name of the Access table to use
Const AccessTable = "tblEmployeeInfo"
' List of users who should NOT be exported
' This list should contain the user's logon name
' Separate each name by a comma
Const Users2Skip = "Guest"
' Constant for the account being disabled
Const ADS_UF_ACCOUNTDISABLE = 2
' Constant for the search to search subtrees
Const ADS_SCOPE_SUBTREE = 2
Const adOpenStatic = 3
Const adLockOptimistic = 3
' General variable declarations
Dim objConnectionDB, objRecordsetDB
Dim objConnectionAD, objCommandAD, objRecordsetAD
Dim dtStart
Dim strSQL
Dim objRootDSE, strDNSDomain
Dim strDN, intUAC, strSam, strDisplayName, strManagerDN, dtExpireDate
Dim blnProcessUser
Dim objUser, objManager
' Get the start time of the script
dtStart = TimeValue(Now( ))
'Create and open ADO connection to the Access database
Set objConnectionDB = CreateObject("ADODB.Connection")
Set objRecordsetDB = CreateObject("ADODB.Recordset")
' Open the database
objConnectionDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & AccessDatabase & ";"
' Open the recordset
objRecordsetDB.Open AccessTable, objConnectionDB, adOpenStatic, adLockOptimistic
' Define the SQL statement used to clear out previous
' user info which was exported from AD
strSQL = "DELETE FROM " & AccessTable & " WHERE ImportedFromAD = 'True'"
Wscript.Echo "Removing previously exported records from the " & _
AccessDatabase & " database."
objConnectionDB.Execute strSQL, , 129
' Determine the DNS domain from the RootDSE object.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
' Create and open an ADO connection to AD
Set objConnectionAD = CreateObject("ADODB.Connection")
Set objCommandAD = CreateObject("ADODB.Command")
objConnectionAD.Provider = "ADsDSOObject"
objConnectionAD.Open "Active Directory Provider"
' Set connection properties
With objCommandAD
.ActiveConnection = objConnectionAD
' Use SQL syntax for the query
' This retrieves all values named in the SELECT section for
' user accounts which do not have the Notes section = NoExport.
' The recordset is sorted ascending on the displayName value.
.CommandText = _
"Select userAccountControl, distinguishedName," & _
" sAMAccountname, displayName" & _
" FROM 'LDAP://" & strDNSDomain & "'" & _
" WHERE objectCategory = 'person' AND" & _
" objectClass = 'user' AND info <> 'NoExport'" & _
" ORDER BY displayName"
.Properties("Page Size") = 1000
.Properties("Timeout") = 30
.Properties("Searchscope") = ADS_SCOPE_SUBTREE
.Properties("Cache Results") = False
End With
Wscript.Echo "Running the query to find users."
Set objRecordSetAD = objCommandAD.Execute
' Move to the first record in the recordset
objRecordSetAD.MoveFirst
' Loop until we reach the end of the recordset
Do While NOT objRecordsetAD.EOF
' Blank out/reset a few variables..just in case.
strDN = ""
intUAC = ""
strSam = ""
strDisplayName = ""
strManagerDN = ""
dtExpireDate = ""
blnProcessUser = True
' Get the userAccountControl value. This lets us, among other things,
' determine if the account is disabled.
intUAC = objRecordsetAD.Fields("userAccountControl")
' Process user if account is not disabled.
If (NOT intUAC AND ADS_UF_ACCOUNTDISABLE) Then
' Get the user's logon name
strSam = objRecordsetAD.Fields("sAMAccountname")
' Determine if the user is included in the list of logon names to skip.
If Instr(UCase(Users2Skip), UCase(strSam)) Then blnProcessUser = False
' Get the user's display name
strDisplayName = objRecordsetAD.Fields("displayName")
' Set boolean value to skip this user if the user's display name is
' blank.
If strDisplayName = "" Then blnProcessUser = False
' If our simple checks went ok, we can now process this user.
If blnProcessUser = True Then
' Get the distinguished name of this user
' The syntax is something like:
' CN=Joe E. Law,OU=Sales,OU=US,DC=mydomain,DC=local
strDN = objRecordsetAD.Fields("distinguishedName")
' Bind to the user object
Set objUser = GetObject("LDAP://" & strDN & "")
' Process the user
With objUser
Wscript.Echo "Processing user: " & strDisplayName
' Get the user's account expiration date
dtExpireDate = CDate(.AccountExpirationDate)
' Process the user if the user's account expiration date is not
passed
If (dtExpireDate = "") OR _
(dtExpireDate = CDate("01/01/1970")) OR _
(dtExpireDate >= Date( )) Then
'Add new record to the Access database
objRecordsetDB.AddNew
' Get user data from AD and populate the new record in the
' Access database
' You can use the .Get("xxx") or .xxx formats to retrieve the data
' All fields on the left MUST exist in the Access table
objRecordsetDB("FirstName") = .Get("givenName")
objRecordsetDB("MiddleName") = .initials
objRecordsetDB("LastName") = .sn
objRecordsetDB("DisplayName") = .displayName
objRecordsetDB("Description") = .description
objRecordsetDB("OfficeLocation") = .physicalDeliveryOfficeName
objRecordsetDB("WorkPhone") = .telephoneNumber
objRecordsetDB("Email") = .mail
objRecordsetDB("WebPage") = .wwwHomePage
objRecordsetDB("Street") = .streetAddress
objRecordsetDB("POBox") = .postOfficeBox
objRecordsetDB("City") = .l
objRecordsetDB("StateOrProvince") = .st
objRecordsetDB("PostalCode") = .postalCode
objRecordsetDB("CountryOrRegion") = .co
objRecordsetDB("HomePhone") = .homePhone
objRecordsetDB("Pager") = .pager
objRecordsetDB("MobilePhone") = .mobile
objRecordsetDB("FaxNumber") = .facsimileTelephoneNumber
objRecordsetDB("Notes") = .info
objRecordsetDB("Title") = .title
objRecordsetDB("Department") = .department
objRecordsetDB("CompanyName") = .company
' Get the distiguished name of the manager
strManagerDN = .manager
' If manager value is not blank then process
If strManagerDN <> "" Then
' Bind to manager's account
Set objManager = GetObject("LDAP://" & strManagerDN & "")
' Populate the Access database with the display name of the
manager
objRecordsetDB("Manager") = objManager.displayName
' Release this object reference
Set objManager = Nothing
End If
' Define that this record was exported from AD
objRecordsetDB("ImportedFromAD") = "True"
' Commit the record
objRecordsetDB.Update
' Release this object reference
Set objUser = Nothing
End If
End With
End If
End If
' Move to the next record in the AD recordset
objRecordsetAD.MoveNext
Loop
' Close the Access database recordset
objRecordsetDB.Close
' Close the Access database connection
objConnectionDB.Close
' Release these object references
Set objRecordsetDB = Nothing
Set objConnectionDB = Nothing
' Close the AD recordset
objRecordsetAD.Close
' Close the AD connection
objConnectionAD.Close
' Release these object references
Set objRecordsetAD = Nothing
Set objConnectionAD = Nothing
' Let the user know how long this process took
WScript.Echo "The script completed in approximately " & _
Second(TimeValue(now( )) - dtStart) & _
" seconds."
' That's all folks!
Wscript.Quit
dgw