Query AD, export user info to excel

Hi Experts,

I would liketo to be able to run a script from excel that will query an OU within my Active Directory, and report back for each user their:

Username
Firstname
Surname
Display name
Employee ID

Many thanks
wisematAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PberSolutions ArchitectCommented:
Issue the following command using CSVDE:

csvde -r "(objectclass=user)" -l samaccountname,givenname,SN,displayname,employeeid -f user.csv  
It will output the information to a file called user.csv
0
wisematAuthor Commented:
Thanks,
I have just realised though, that I do not have the csvde command in the sys32, crippled office build of XP.

Is there another way?
0
PberSolutions ArchitectCommented:
that filter included computers.  Here a better filter
csvde -r
"(&(objectcategory=person)(objectclass=user))"  -l samaccountname,givenname,SN,displayname,employeeid -f user.csv  .
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

PberSolutions ArchitectCommented:
If you have access to a server you can copy csvde.exe from the c:\windows\system32 directory.  Otherwise you have to go to a script
0
PberSolutions ArchitectCommented:
Save this as a script and call it like this:
 
cscript yourscript.vbs>users.csv

strLDAP = "(&(objectcategory=person)(objectclass=user))"
 
set oRootDSE = GetObject("LDAP://RootDSE")
strDomainNC = oRootDSE.Get("defaultNamingContext")
set oRootDSE = Nothing
 
Set oConnection   = CreateObject("ADODB.Connection")
oConnection.Provider   = "ADsDSOObject"
oConnection.Open "Active Directory Provider"
 
Set oCommand = CreateObject("ADODB.Command")
Set oCommand.ActiveConnection = oConnection
 
strQuery = "<LDAP://"&strDomainNC&">;" & strLDAP & ";AdsPath;subTree"
    
oCommand.CommandText = strQuery   
oCommand.Properties("Page Size") = 1000
Set oRecordSet = oCommand.Execute
 
if not oRecordSet.Eof Then
 
	While Not oRecordSet.Eof
		Set x = GetObject(oRecordSet.Fields("AdsPath").Value)
		
		WScript.Echo x.samaccountname& "," & _
		             x.givenname & "," & _
		             x.sn & "," & _
		             x.displayname & "," & _
		             x.employeeid
		oRecordSet.MoveNext
	Wend
end if

Open in new window

0
wisematAuthor Commented:
I'm just a plain user, trying to pull an audit report together to do my job, no server access I'm afraid.  
Can you help with a script?
0
wisematAuthor Commented:
Just running the script now, how do I tell it I want the 'Europe' container?

I think we are pretty much there :)
0
Mike KlineCommented:
ok you can use adfind for this.  Great tool by MVP Joe Richards
http://www.joeware.net/freetools/tools/adfind/index.htm 
adfind -b "DN of OU" -f  "&(objectcategory=person)(objectclass=user)"  samaccountname givenname sn displayname employeeid -csv -nodn > c:\exportUsers.csv
so you may be asking "how do I get the DN of my OU"
adfind again
adfind -default -f ou="name of your OU" DN
Thanks
Mike

 
0
wisematAuthor Commented:
Mike
Using adfind -default -f ou="name of your OU" DN,
I get the response:
Using server: londondc1.eu.acme.com:389
Directory: Windows Server 2003
Base DN: DC=eu,DC=acme,DC=com

I'm not sure what to enter to make your main command work, sorry

0
wisematAuthor Commented:
sorry, cut and paste error that should read "europe" not "name of your ou"
0
Mike KlineCommented:
What is the name of your OU, looks like it may be Europe (from your previous response).  If it is Europe use
adfind -b "OU=Europe, DC=EU, DC=acme, DC=com" -f  "&(objectcategory=person)(objectclass=user)"  samaccountname givenname sn displayname employeeid -csv -nodn > c:\exportUsers.csv
See how that works for you. (may need to modify your OU if it is not Europe)
Thanks
Mike
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PberSolutions ArchitectCommented:
Sorry I was at a meeting
Change this one line:
strQuery = "<LDAP://OU=Europe" & strDomainNC&">;" & strLDAP & ";AdsPath;subTree"
 
0
PberSolutions ArchitectCommented:
woops, missed a comma:

strQuery = "<LDAP://OU=Europe," & strDomainNC&">;" & strLDAP & ";AdsPath;subTree"
 
0
PberSolutions ArchitectCommented:
You could make the script for flexible in the future by adding a variable to specify the OU
'change this line to the OU you wish
strOU = "" 'Add trailing comma if not blank.  i.e.  strOU = "OU=Europe,"
 
strLDAP = "(&(objectcategory=person)(objectclass=user))"
 
set oRootDSE = GetObject("LDAP://RootDSE")
strDomainNC = oRootDSE.Get("defaultNamingContext")
set oRootDSE = Nothing
 
Set oConnection   = CreateObject("ADODB.Connection")
oConnection.Provider   = "ADsDSOObject"
oConnection.Open "Active Directory Provider"
 
Set oCommand = CreateObject("ADODB.Command")
Set oCommand.ActiveConnection = oConnection
 
strQuery = "<LDAP://"& strOU & strDomainNC & ">;" & strLDAP & ";AdsPath;subTree"
    
oCommand.CommandText = strQuery   
oCommand.Properties("Page Size") = 1000
Set oRecordSet = oCommand.Execute
 
if not oRecordSet.Eof Then
 
	While Not oRecordSet.Eof
		Set x = GetObject(oRecordSet.Fields("AdsPath").Value)
		
		WScript.Echo x.samaccountname& "," & _
		             x.givenname & "," & _
		             x.sn & "," & _
		             x.displayname & "," & _
		             x.employeeid
		oRecordSet.MoveNext
	Wend
end if

Open in new window

0
PberSolutions ArchitectCommented:
You can also limit your search scope as well.  For instance, if you wanted to look only at OU=Europe and not in a sub OU of Europe, you can do that by changing the strQuery line.  Specifically change the word: subTree to oneLevel
e.g.:
strQuery = "<LDAP://"& strOU & strDomainNC & ">;" & strLDAP & ";AdsPath;oneLevel"
   

0
PberSolutions ArchitectCommented:
With some slight re-tooling, you can run the script from within Excel.  Just create a macro that calls the sub EnumUsersInOu


Sub testMacro()
    EnumUsersInOU ("OU=Museum,")
End Sub
Sub EnumUsersInOU(strOU)
    'strOU = "" 'Add trailing comma if not blank.  i.e.  strOU = "OU=Europe,"
 
    strLDAP = "(&(objectcategory=person)(objectclass=user))"
 
    Set oRootDSE = GetObject("LDAP://RootDSE")
    strDomainNC = oRootDSE.Get("defaultNamingContext")
    Set oRootDSE = Nothing
 
    Set oConnection = CreateObject("ADODB.Connection")
    oConnection.Provider = "ADsDSOObject"
    oConnection.Open "Active Directory Provider"
 
    Set oCommand = CreateObject("ADODB.Command")
    Set oCommand.ActiveConnection = oConnection
 
    strQuery = "<LDAP://" & strOU & strDomainNC & ">;" & strLDAP & ";AdsPath;subTree"
    
    oCommand.CommandText = strQuery
    oCommand.Properties("Page Size") = 1000
    Set oRecordSet = oCommand.Execute
 
    'Clear Worksheet - optional
    Cells.Select
    Selection.ClearContents
 
    'Add titles
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "UserID"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "First Name"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Last Name"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Display Name"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "EmployeeID"
    Range("A1:E1").Select
    Selection.Font.Bold = True
    Selection.Font.Size = 12
    
    y = 2
    If Not oRecordSet.EOF Then
 
        While Not oRecordSet.EOF
            Set x = GetObject(oRecordSet.Fields("AdsPath").Value)
                ActiveSheet.Cells(y, 1).Value = x.samaccountname
                ActiveSheet.Cells(y, 2).Value = x.givenName
                ActiveSheet.Cells(y, 3).Value = x.sn
                ActiveSheet.Cells(y, 4).Value = x.displayname
                ActiveSheet.Cells(y, 5).Value = x.employeeid
            oRecordSet.MoveNext
            y = y + 1
        Wend
    End If
    
    Cells.Select
    Selection.Columns.AutoFit
End Sub

Open in new window

0
wisematAuthor Commented:
Thank you guys, when I came back towork this morning I tried both solutions and both gave perfect results that I could use in excel as requested.  Thank you both, I hope you dont mind the split, but it seems fair to me
0
PberSolutions ArchitectCommented:
Glad to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.