Saturday, 15 March 2014

VBScript to Export AD users to CSV file

In this article I am going write VBScript code to Find and Export Active Directory user's properties into CSV file and Search AD users by LDAP filter and Export to CSV file.

Summary:


VBScript to Find and Export Active Directory Users to CSV file

1. Copy the below example VBScript code and paste it in notepad or a VBScript editor.
2. Here, I have given CSV file path as "ADUsers.csv", this will create ADUsers.csv file where you placed and execute this VB Script file. You can give your own file path like "C:\Users\Administrator\Desktop\ADUsers.csv"
3. Save the file with a .vbs extension, for example: ExportADUsers.vbs
4. Double-click the VBScript file (or Run this file from command window) to Export AD users into csv file.

 Click to get vbscript source code as a file Download ExportADUsers.vbs

' ExportADUsers.vbs
' Sample VBScript to Find and Export AD users into CSV file .
' Author: http://www.morgantechspace.com/
' ------------------------------------------------------' 

Option Explicit

' Initialize required variables.
Dim adoCommand, adoConnection
Dim varBaseDN, varFilter, varAttributes
Dim objRootDSE, varDNSDomain, strQuery, adoRecordset
Dim objFSO, objCSVFile

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")

varDNSDomain = objRootDSE.Get("defaultNamingContext")
varBaseDN = "<LDAP://" & varDNSDomain & ">"

' varBaseDN is Domain DN, you can give your own OU DN instead of 
' getting from "defaultNamingContext"
' like varBaseDN = "<LDAP://OU=TestOU,DC=Domain,DC=com>" 

' Filter for user objects.
varFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
varAttributes = "name,samaccountname,distinguishedname,mail"

' Construct the LDAP syntax query.
strQuery = varBaseDN & ";" & varFilter & ";" & varAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 1000
adoCommand.Properties("Timeout") = 20
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Create CSV file 
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Here, I have given CSV file path as "ADUsers.csv", this will create ADUsers.csv file
' where you placed and execute this VB Script file. You can give your own file path
' like "C:\Users\Administrator\Desktop\ADUsers.csv"

Set objCSVFile = objFSO.CreateTextFile("ADUsers.csv", _ 
    ForWriting, True)

' Write selected AD Attributes as CSV columns(first line)
 objCSVFile.Write varAttributes 

 objCSVFile.Writeline ' New Line

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF

   ' Retrieve values and write into CSV file.

     objCSVFile.Write adoRecordset.Fields("name").Value & "," 
     objCSVFile.Write adoRecordset.Fields("samaccountname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("distinguishedname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("mail").Value & ""
     objCSVFile.Writeline  ' New Line

    ' Move to the next record in the recordset.
    adoRecordset.MoveNext
Loop

 objCSVFile.Close

' close ado connections.
adoRecordset.Close
adoConnection.Close

' Active Directory User properites are exported Successfully as CSV File

VBScript to Export AD Users to CSV file from Specific OU (Organization Unit)

1. Copy the below example VBScript code and paste it in notepad or a VBScript editor.
2. Change the value for 'varBaseDN'  into your own OU's DN .
3. Save the file with a .vbs extension, for example: ExportADUsersFromOU.vbs
4. Double-click the VBScript file (or Run this file from command window) to Export AD users into csv file.

 Click to get vbscript source code as a file Download ExportADUsersFromOU.vbs

' ExportADUsersFromOU.vbs
' Sample VBScript to Find and Export AD users into CSV file from Specific OU .
' Author: http://www.morgantechspace.com/
' ------------------------------------------------------' 

Option Explicit

' Initialize required variables.
Dim adoCommand, adoConnection
Dim varBaseDN, varFilter, varAttributes
Dim objRootDSE, strQuery, adoRecordset
Dim objFSO, objCSVFile

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")

' varBaseDN is the OU DN for AD Serach Scope, you can give your own OU's Distinguished Name here.

varBaseDN = "<LDAP://OU=TestOU,DC=Domain,DC=Com>"


' Filter for user objects.
varFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
varAttributes = "name,samaccountname,distinguishedname,mail"

' Construct the LDAP syntax query.
strQuery = varBaseDN & ";" & varFilter & ";" & varAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 1000
adoCommand.Properties("Timeout") = 20
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Create CSV file 
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Here, I have given CSV file as "ADUsers.csv", this will create ADUsers.csv file
' where you placed and execute this VB Script file. You can give your own file path
' like "C:\Users\Administrator\Desktop\ADUsers.csv"

Set objCSVFile = objFSO.CreateTextFile("ADUsers.csv", _ 
    ForWriting, True)

' Write selected AD Attributes as CSV columns(first line)
 objCSVFile.Write varAttributes 

 objCSVFile.Writeline ' New Line

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF

   ' Retrieve values and write into CSV file.

     objCSVFile.Write adoRecordset.Fields("name").Value & "," 
     objCSVFile.Write adoRecordset.Fields("samaccountname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("distinguishedname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("mail").Value & ""
     objCSVFile.Writeline  ' New Line

    ' Move to the next record in the recordset.
    adoRecordset.MoveNext
Loop

 objCSVFile.Close

' close ado connections.
adoRecordset.Close
adoConnection.Close

' Active Directory User properites are exported Successfully as CSV File

Search and Filter AD users by Department and Export to CSV file in VBScript

1. Copy the below example VBScript code and paste it in notepad or a VBScript editor.
2. Change the value for 'varSearchDept' from "Admin" to your own search word.
3. Save the file with a .vbs extension, for example: SearchAndExportADUsers.vbs
4. Double-click the VBScript file (or Run this file from command window) to Export AD users into csv file.

 Click to get vbscript source code as a file Download SearchAndExportADUsers.vbs

' SearchAndExportADUsers.vbs
' Sample VBScript to Search and Filter AD users and Export into CSV file .
' Author: http://www.morgantechspace.com/
' ------------------------------------------------------' 

Option Explicit

' Initialize required variables.
Dim adoCommand, adoConnection
Dim varBaseDN, varFilter, varAttributes
Dim objRootDSE, varDNSDomain, strQuery, adoRecordset
Dim varSearchDept
Dim objFSO, objCSVFile

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")

varDNSDomain = objRootDSE.Get("defaultNamingContext")
varBaseDN = "<LDAP://" & varDNSDomain & ">"

' varBaseDN is Domain DN, you can give your own OU DN instead of getting from "defaultNamingContext"
' like varBaseDN = "<LDAP://OU=TestOU,DC=Domain,DC=com>" 

varSearchDept ="Admin"

' Filter AD user by department "Admin", you can give your own search or filter value.
varFilter = "(&(objectCategory=person)(objectClass=user)(department="& varSearchDept &"))"

' Comma delimited list of attribute values to retrieve.
varAttributes = "name,samaccountname,distinguishedname,mail"

' Construct the LDAP syntax query.
strQuery = varBaseDN & ";" & varFilter & ";" & varAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 1000
adoCommand.Properties("Timeout") = 20
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Create CSV file 
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Here, I have given CSV file as "ADUsers.csv", this will create ADUsers.csv file
' where you placed and execute this VB Script file. You can give your own file path
' like "C:\Users\Administrator\Desktop\ADUsers.csv"
 
Set objCSVFile = objFSO.CreateTextFile("ADUsers.csv", _ 
    ForWriting, True)

' Write selected AD Attributes as CSV columns(first line)
 objCSVFile.Write varAttributes 

 objCSVFile.Writeline ' New Line

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF

   ' Retrieve values and write into CSV file.

     objCSVFile.Write adoRecordset.Fields("name").Value & "," 
     objCSVFile.Write adoRecordset.Fields("samaccountname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("distinguishedname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("mail").Value & ""
     objCSVFile.Writeline  ' New Line

    ' Move to the next record in the recordset.
    adoRecordset.MoveNext
Loop

 objCSVFile.Close

' close ado connections.
adoRecordset.Close
adoConnection.Close

' Active Directory User properites are exported Successfully as CSV File


VBScript to Export Active Directory Users to CSV file by dynamic CSV file path

1. Copy the below example VBScript code and paste it in notepad or a VBScript editor.
2. Save the file with a .vbs extension, for example: ExportADUsersbyDynamicPath.vbs
3. Double-click the VBScript file (or Run this file from command window) to Export AD users.
4. Give the CSV file to save Exported AD User attributes and Click OK to proceed.

VBScript Export AD users into CSV file


Click to get vbscript code as a file Download ExportADUsersbyDynamicPath.vbs

' ExportADUsersbyDynamicPath.vbs
' Sample VBScript to Export AD users into CSV file by dynamic CSV file path .
' Author: http://www.morgantechspace.com/
' ------------------------------------------------------' 

Option Explicit

' Initialize required variables.
Dim adoCommand, adoConnection
Dim varBaseDN, varFilter, varAttributes
Dim objRootDSE, varDNSDomain, strQuery, adoRecordset
Dim csvFilePath
Dim objFSO, objCSVFile

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection

' Asks CSV File path from user to save new file.
Do
   csvFilePath = InputBox ("Please enter CSV file path.- Ex: C:\ADUsers.csv")
   If csvFilePath= "" then
      Msgbox "No file path entered"
   end if
Loop Until csvFilePath <> ""

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")

varDNSDomain = objRootDSE.Get("defaultNamingContext")
varBaseDN = "<LDAP://" & varDNSDomain & ">"

' varBaseDN is Domain DN, you can give your own OU DN instead of getting from "defaultNamingContext"

' like varBaseDN = "<LDAP://OU=TestOU,DC=Domain,DC=com>" 

' Filter for user objects.
varFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
varAttributes = "name,samaccountname,distinguishedname,mail"

' Construct the LDAP syntax query.
strQuery = varBaseDN & ";" & varFilter & ";" & varAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 1000
adoCommand.Properties("Timeout") = 20
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Create CSV file 
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objCSVFile = objFSO.CreateTextFile(csvFilePath , _ 
    ForWriting, True)

' Write selected AD Attributes as CSV columns(first line)
 objCSVFile.Write varAttributes 

 objCSVFile.Writeline ' New Line

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF

   ' Retrieve values and write into CSV file.

     objCSVFile.Write adoRecordset.Fields("name").Value & "," 
     objCSVFile.Write adoRecordset.Fields("samaccountname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("distinguishedname").Value & "," 
     objCSVFile.Write adoRecordset.Fields("mail").Value & ""
     objCSVFile.Writeline  ' New Line

    ' Move to the next record in the recordset.
    adoRecordset.MoveNext
Loop

 objCSVFile.Close

' close ado connections.
adoRecordset.Close
adoConnection.Close

' Active Directory User properites are exported Successfully as CSV File


Exported CSV File Output:

VBScript to Export AD users to CSV file


Advertisements
Advertisements

2 comments:

  1. Thanks. I found the post immensely useful.

    ReplyDelete
  2. hi, I Have a problema trying to export many attributes, For example "cn or givenName"

    can you help me please

    ReplyDelete