Thought i’d share how to use Crystal Reports to Query AD.  Not only can you query AD, but you can combine it with SQL to write some nifty little reports.

Here are the steps:

  1. Create a Blank Report
  2. When prompted to create a connection, choose OLE DB (ADO)
  3. Select OLE DB Provider for Microsoft Directory Services
  4. Under Data Source, enter “LDAP://domain.com/OU=SOMEOU, check “Integrated Security”

    Domain = the domain for your company (“mycompany.com, mycompany.local, whatever)
    OU = This is the OU you want to start in, I like to use it because it makes queries faster, so usually “Users” or “Computers”, depending on what you are looking up
    Integrated Security = Why Integrated? Well, Crystal ONLY supports DB Logins, what that means is that Crystal will only prompt / save you for DB passwords, but for LDAP passwords, it wont, it actually leaves it blank, so the report will work the first time, but if you try to run it again, it wont work, and you will end up locking your AD account
  5. Under Advanced Information just leave it as-is
  6. Now the connection comes up, but there are no tables.  You have to create a custom command, so double click “Add Command”
  7. Add the query command to the window that pops up.  Almost all LDAP fields are queryable
  8. Once you hit OK, the command will appear as a table called “command”. (You can rename this to whatever you want)

 

From this point on, you just Hit OK, and all the fields that were “Selected” in the command window will show up in Crystal, and you can use them to write your report.

I hope this helps someone, please feel free to leave a comment if you like.

Facebook Twitter Email Linkedin Digg Delicious