Export data from Active Directory

Hi Readers,

There are different ways & methods to achieve it.

I have learned these methods from my experience & offcourse found some stuff by googling & bing.

I am sharing thre most common used methods :-

1. CSVDE for exporting data from AD, this is very very fast if you have millions of objects in AD.

Example:- CSVDE -f C:\Scripts\exportad\onlyusers.csv -r “(&(objectClass=user)(objectCategory=person))” -l “samaccountname, givenName, sn, extensionattribute1, mail,physicalDeliveryOfficeName, Department,Title,company,st,co,userAccountControl”

2. Thru excel if users are hundred in numbers (founded this method on internet , it is very useful in day to day activities)

Insert below two functions is excel :-

————————————————————————————

Function GetAdsProp(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String) As String ' Get the domain string ("dc=domain, dc=local") Dim strDomain As String strDomain = GetObject("LDAP://rootDSE").Get("defaultNamingContext")

'MsgBox strDomain

' ADODB Connection to AD Dim objConnection As ADODB.Connection Set objConnection = CreateObject("ADODB.Connection") objConnection.Open "Provider=ADsDSOObject;"

' Connection Dim objCommand As ADODB.Command Set objCommand = CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection

' Search the AD recursively, starting at root of the domain objCommand.CommandText = _ "<LDAP://" & strDomain & ">;(&(objectCategory=User)" & _ "(" & SearchField & "=" & SearchString & "));" & SearchField & "," & ReturnField & ";subtree"

'MsgBox objCommand.CommandText

' RecordSet Dim objRecordSet As ADODB.Recordset Set objRecordSet = objCommand.Execute

If objRecordSet.RecordCount = 0 Then GetAdsProp = "not found" ' no records returned Else GetAdsProp = objRecordSet.Fields(ReturnField) ' return value End If

' Close connection objConnection.Close

' Cleanup Set objRecordSet = Nothing Set objCommand = Nothing Set objConnection = Nothing End Function

—————————————————————————————————————————————————-
Function GetAdsProp2(ByVal SearchField As String, ByVal SearchString As String, ByVal SearchField2 As String, _ ByVal SearchString2 As String, ByVal ReturnField As String) As String

'Get the domain string ("dc=domain, dc=local") Dim strDomain As String strDomain = GetObject("LDAP://rootDSE").Get("defaultNamingContext") 'MsgBox strDomain 'ADODB Connection to AD Dim objConnection 'As ADODB.Connection Set objConnection = CreateObject("ADODB.Connection") objConnection.Open "Provider=ADsDSOObject;"""

‘Connection
Dim objCommand ‘As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection

‘Corrected code from Jessica to include ldap
objCommand.CommandText = _
“<LDAP://” & strDomain & “>;(&(objectCategory=User)” & _
“(” & SearchField2 & “=” & SearchString2 & “)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField2 & “,” & SearchField & “,” & ReturnField & “;subtree”
‘MsgBox objCommand.CommandText
‘Recordset
Dim objRecordSet ‘As ADODB.Recordset
Set objRecordSet = objCommand.Execute

If objRecordSet.RecordCount = 0 Then
GetAdsProp2 = “not found” ‘no records returned”
Else
GetAdsProp2 = objRecordSet.Fields(ReturnField) ‘ return value
End If

‘Close Connection
objConnection.Close

‘Cleanup
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function

——————————————————————————————————————————————————————–

Now you can do wonders from excel see the below screenshot(you can get any field from ad), just drag if you have hundreds of users.

————————————————————————————————————————————————–

Second function does the same thing but you can search on two fields, example if you have first name & last name, you want to extract user id.

———————————————————————————————————————————————

3. Third Method is Quest powershell script along with excel:- (no formatting is needed, it will do everything for you)
###################################################################################### # Author: Vikas Sukhija # Date:- 01/21/2012 #Description:- This script will use quest shell & grab the user attributes from AD #Prerequisites :- Excel & Quest Shell ###################################################################################### #Start-Transcript

# call excel for writing the results
$objExcel = new-object -comobject excel.application $workbook = $objExcel.Workbooks.Add() $worksheet=$workbook.ActiveSheet $objExcel.Visible = $False $cells=$worksheet.Cells

# define top level cell

$cells.item(1,1)=”UserId”
$cells.item(1,2)=”FirstName”
$cells.item(1,3)=”LastName”
$cells.item(1,4)=”Employeeid”
$cells.item(1,5)=”email”
$cells.item(1,6)=”Office”
$cells.item(1,7)=”Department”
$cells.item(1,8)=”Title”
$cells.item(1,9)=”Company”
$cells.item(1,10)=”City”
$cells.item(1,11)=”State”
$cells.item(1,12)=”Country”
$cells.item(1,13)=”AccountIsDisabled”

#intitialize row out of the loop

$row = 2

#import quest management Shell

if ( (Get-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin Quest.ActiveRoles.ADManagement
}
$data = get-qaduser -IncludedProperties “CO”, “extensionattribute1”

#loop thru users

foreach ($i in $data)

{

#initialize column within the loop so that it always loop back to column 1
$col = 1
$userid=$i.Name
$FisrtName=$i.givenName
$LastName=$i.sn
$Employeeid=$i.extensionattribute1
$email=$i.PrimarySMTPaddress
$office=$i.Office
$Department=$i.Department
$Title=$i.Title
$Company=$i.Company
$City=$i.l
$state=$i.st
$Country=$i.CO
$AccountIsDisabled=$i.AccountIsDisabled

Write-host “Processing……………………………$userid”
$cells.item($row,$col) = $userid
$col++
$cells.item($row,$col) = $FisrtName
$col++
$cells.item($row,$col) = $LastName
$col++
$cells.item($row,$col) = $Employeeid
$col++
$cells.item($row,$col) = $email
$col++
$cells.item($row,$col) = $office
$col++
$cells.item($row,$col) = $Department
$col++
$cells.item($row,$col) = $Title
$col++
$cells.item($row,$col) = $Company
$col++
$cells.item($row,$col) = $City
$col++
$cells.item($row,$col) = $state
$col++
$cells.item($row,$col) = $Country
$col++
$cells.item($row,$col) = $AccountIsDisabled
$col++
$row++

}

#formatting excel

$range = $objExcel.Range(“A2”).CurrentRegion
$range.ColumnWidth = 30
$range.Borders.Color = 0
$range.Borders.Weight = 2
$range.Interior.ColorIndex = 0
$range.Font.Bold = $false
$range.HorizontalAlignment = 3

# Headings in Bold

$cells.item(1,1).font.bold=$True
$cells.item(1,2).font.bold=$True
$cells.item(1,3).font.bold=$True
$cells.item(1,4).font.bold=$True
$cells.item(1,5).font.bold=$True
$cells.item(1,6).font.bold=$True
$cells.item(1,7).font.bold=$True
$cells.item(1,8).font.bold=$True
$cells.item(1,9).font.bold=$True
$cells.item(1,10).font.bold=$True
$cells.item(1,11).font.bold=$True
$cells.item(1,12).font.bold=$True
$cells.item(1,13).font.bold=$True

#save the excel file

$filepath = “c:\scripts\exportad\exportAD.xlsx”
$workbook.saveas($filepath)
$workbook.close()
$objExcel.Quit()

#Stop-Transcript
##############################################################################################

Regards

Sukhija Vikas