none
Query AD, export user info to excel

    Question

  • I would like to 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
    Tuesday, September 06, 2011 8:39 AM

Answers

All replies

  •  

    Hi, 

    Try this

    For all attibutes

    C:\>Dsquery * -limit 0 -filter "&(objectClass=User)(objectCategory=Person)" -attr * >>output123.txt

     

    For specific attributes.

     

    C:\>Dsquery * -limit 0 -filter "&(objectClass=User)(objectCategory=Person)" -attr samaccountname displayname sn givenname >> c:\allusers.txt

     

     

     


     

    Best regards Biswajit Biswas Disclaimer: This posting is provided "AS IS" with no warranties or guarantees , and confers no rights. MCP 2003,MCSA 2003, MCSA:M 2003, CCNA, MCTS, Enterprise Admin

     



    Tuesday, September 06, 2011 8:58 AM
  • Hello, A.W.

    You can use CSVDE to this. Here you have some information: http://www.petri.co.il/using-csvde-ldifde-export-active-directory-snapshots-windows-server-2008.htm

    Regards.


    Oscar Abad -- MCITP Enterprise Administrator // MCITP Server Administrator
    http;//www.aprendeinformaticaconmigo.com
    http://www.serverswin.com
    Tuesday, September 06, 2011 9:01 AM
  • Hi,

    you can also use adfind.

    Example: adfind -default -f "(objectcategory=person)" -nodn samaccountname sn givenname displayname employeeid -csv >filename.csv

    More info about adfind http://www.joeware.net/freetools/tools/adfind/index.htm

    Regards,

    Martin


    Martin Forch
    Tuesday, September 06, 2011 9:14 AM
  • Tuesday, September 06, 2011 9:22 AM
  • I have example VBScript and PowerShell scripts to document users in a comma delimited format linked here:

    http://www.rlmueller.net/DocumentUsers.htm

    You would run the scripts at a command prompt (or PowerShell prompt) and redirect the output to a text file. The resulting file can be read into Excel. The scripts as written document all users in the domain. To restrict the output to users in one OU, modify the base of the query to be the Distinguished Name of the OU. For example, in my example DocumentUsers.vbs:

    strBase = "<LDAP://ou=West,dc=MyDomain,dc=com>"

    Run the vbscript program at a command prompt using the cscript host program, and the optional //nologo parameter, so the output can be redirected. For example:

    cscript //nologo DocumentUsers.vbs > Users.csv

    My examples document givenName, sn, distinguishedName, sAMAccountName, and several other attributes you don't need. You should be able to add displayName and employeeID (both single valued attributes). Add these attributes to the comma delimited list (strAttributes), then in the loop where the recordset is enumerated retrieve the values and add to the output. The only function in the VBScript program you need is Function CSVLine.

     


    Richard Mueller - MVP Directory Services
    Tuesday, September 06, 2011 1:40 PM
  • It just occurred to me that my example linked on this page might be easier for you:

    http://www.rlmueller.net/GenericADO.htm

    Again, I have a VBScript and an equivalent PowerShell script example. In both cases you are prompted for three values. First you are prompted for the base of the query. Provide the Distinguished Name of the OU. Then you are prompted for the LDAP syntax filter. For users, use:

    (&(objectCategory=person)(objectClass=user))

    Then you are prompted for the comma delimited list of attribute values to retrieve. In your case:

    sAMAccountName,givenName,sn,displayName,employeeID

    When you run the script, use the optional parameter /csv to have the output formated comma delimited, so it can be read into Excel. Also, redirect the output to a text file. For example:

    cscript //nologo GenericADO.vbs /csv > Users.csv


    Richard Mueller - MVP Directory Services
    Tuesday, September 06, 2011 1:52 PM
  • Hi,

     

    If you encounter any difficulties when customizing the scripts, you may submit a new question in The Official Scripting Guys Forum! which is a best resource for scripting related issues.

      

    The Official Scripting Guys Forum!

    http://social.technet.microsoft.com/Forums/en/ITCG/threads

     

    Regards,

     

    Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, September 07, 2011 12:30 PM
  • Hi,

     

    I would like to confirm what is the current situation? If there is anything that I can do for you, please do not hesitate to let me know, and I will be happy to help.

     

    Regards,

     

    Arthur Li

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact
    tnmff@microsoft.com.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, September 12, 2011 2:02 AM