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.

adprop

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

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.

adprop2

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

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

Advertisements

One thought on “Export data from Active Directory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s