Wednesday, January 23, 2013

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 = _
        ";(&(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 = _
";(&(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

1 comment: