Extracting Data from Active Directory

[Former Member]

Hi all,

I thought i saw a connector for Microsoft Active Directory.

I would like to create a report (without having to create another database) that would extract certain attributes out of active directory.

eg: Log on time and log off time; username

Is this possible?

Thanks

Roger

[Former Member · Accepted solution]

Hi Roger / Mark,

Please go through the following steps to report off Active Directory in Crystal Reports,

To report off Active Directory data using CR 8.5:

====================================

1. Create a new blank report.

2. Choose 'More Data Sources' > 'Active Data (ADO)' > 'Make New Connection'.

3. In the 'Select Data Source' dialog box select the 'ADO and OLE DB' option and then click 'Build'.

4. In the 'Data Link Properties' dialog box choose 'OLE DB Provider for Microsoft Directory Services' and then click 'Next'.

5. In the 'Data Source' box enter the full computer name of the server where the connection is being made.

6. In the 'Location' text box enter the UNC path to the directory where the Active Directory ntds.dit file is located.

7. When prompted to enter information to log on to the server, select the option 'Use Windows NT Integrated security' and click 'OK'.

8. Click 'OK' to revert to the 'Data Explorer' dialog box.

9. The new data connection will appear under the 'Active Data (ADO)' folder in the 'Data Explorer' dialog box.

10. Select the newly created data object and click 'Add' to add it to your report.

11. Click 'SQL' in the 'Select Recordset' dialog box.

12. In the blank text area of the 'Select Recordset' dialog box, enter a query.

Example (where the domain is sports.xtremebikes.com):

SELECT ADsPath, cn FROM 'LDAP://OU=ou,DC=sports,DC=xtremebikes,DC=com'

13. This query now appears as a data object under the 'ADO' folder.

14. Expand the data object to show the Active Directory fields available for your report.

To report off Active Directory data using CR 9, 10, XI and 2008:

========================================

You will use a 'Command Object' to query for Active Directory data.

1. Ensure that your computer is joined to the domain being queried and that you are logged in under your domain account.

2. Create a new blank report.

3. Choose 'Create New Connection' > 'OLE DB'.

4. In the 'OLE DB (ADO)' dialog box, create a new connection using the "OLE DB Provider for Microsoft Directory Services". Clear all fields in the 'Connection Information' dialog box and click 'Finish'. If generated, ignore the error "Current provider does not support schema record sets" and proceed.

5. Double-click the "Add Command" object. Inside the Add Command dialog, enter the case sensitive LDAP query.

LDAP query examples are shown below:

Query Example 1

-

---

This query will return group information from Active Directory where the domain equals 'sports.xtremebikes.com'.

'LDAP://DC=sports,DC=xtremebikes, DC=COM' WHERE objectCategory='group'

Query Example 2

-

---

This query will return group information from an organization unit.

'LDAP://OU=ou,DC=sports,DC=xtremebikes,DC=com'

Query Example 3

-

---

This query will return data from Active Directory where the domain equals 'xtremebikes.com'.

'LDAP://DC=xtremebikes, DC=COM'

6. Save the command object.

7. Add the command object to your report.

8. Design and run your report based on the fields available in the command object.

To report Active Directory data from a CSV file:

===================================

It is possible to export Active Directory data to a comma-separated values (CSV) file by running a script from a batch file or at the command prompt. Re-run the script to update the data in the CSV. The fields available in the CSV contain more detail than Active Directory data obtained by other methods.

To create a CSV file choose one of the options below:

" Using the command Prompt

A CSV can be created by entering the following script at the command prompt or in DOS on your domain controller.

CSVDE -f c:\temp\csv_file_name.csv -r "(&(objectClass=user))"

" Using a Batch File

A CSV can be created by entering the following script in a text editor.

CSVDE -f c:\temp\csv_file_name.csv -r "(&(objectClass=user))"

Save the file as "filename.bat". Run the batch file by double-clicking it.

Crystal Reports can report off this CSV file.

Hope this helps you.

Regards,

Abhijeet T.

[Former Member · Accepted solution]

Hi Abhijeet,

I am using CR 2008 and I want to create a Crystal Report using AD. I looked at KB article that you posted.

During report creation, I can log in with my domain account and create report. But what happens after I publish the report to the Business Objects Enterprise.

How does the connection happen on the Business Objects Server?

Will the report use my connection information to generate the report?

What do we need to configure at the Server for the report to work?

Thanks.

[Former Member · Accepted solution]

Hello,

I am facing issues while creating ldap connection and then while retrieving data from Active Directory.

I am using Crystal Reports 2008 and I followed the steps mentioned in the post. While creating new OLE DB (ADO) Active Directory Service Interfaces connection, I followed below steps:

1. I choose "OLE DB Provider for Microsoft Directory Services" as Provider

2. On "Connection Information" screen, I cleared all fields as suggested in the post

3. On "Advanced Information" screen, I clicked on Finish button

At this point I got "Logon Failed." error message dialog box. When I clicked OK on error message dialog box, the "Connection Information" screen re-appears. Hence I am not able to Finish connection process.

Then I thought of providing username and password information on "Connection Information" screen. I provided Domain User credentials (using which I logged on to the computer) and finished connection. After that, I double clicked "Add Command" to enter ldap SQL query. I tried with all ldap queries mentioned in this post, however, I got following error message dialog box:

Failed to retrieve data from the database.

Details: ADO Error Code: 0x

Source: Provider

Description: One or more errors occurred during processing of command.

Native Error: [Database Vendor Code: -2147217900]

Please let me know if anyone can help me.

Thank you!

[former_member183781]

Bump (similar question).

Does anyone know how to do this in CR-XI or CR-2008...?

We are looking to create a report that shows a grid of WinAD Groups and the Users within those groups.

What is the data-connection method that you would use...?

Thanks in advance!