Creating a Crystal Report from Windows Active Directory 2

[paulmarr]

Hi Crystal Gurus

I have a business requirement to create a report from Windows Active Directory that will list members of specified Win AD Security Groups.

Is this possible? How do I connect to it - what driver would I use? Anyone that has accomplished this before?

Any help will be appreciated!

Cheers
paulmarr

 

[paulmarr]

I know this isn't an easy question like grouping or sorting - surely one of the tipsters here that eagerly answer the easy ones would be able to tackle this one????

Yes it's a challenge! You know who you are!

 

[MJRBIM]

I have never been able to this - but only looked at it briefly...

This article seems to show a way to link to WinAD info from MS-SQL server, you might be able to connect Crystal to an ODBC on MS-SQL that in turn connects to WinAD info (groups, users, roles, etc).

http://articles.techrepublic.com.com/5100-6345_11-5259887.html

If there's a direct Crystal driver for WinAD, I am unaware of it.

 

[Turkbear]

Hi,
Just a note paulmarr:
The folks here are all volunteers and answer many very complex questions, when they have the time and the knowledge..taking cheap shots will not get you much help..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

There is a way to do a report on directories/files on your machine/network, if you open a new blank report->create new connection->file system data. Then you can browse for the starting directory/file, but I didn't think this was really what you were looking for.

-LB

 

[paulmarr]

Thanks to all for your answers - I wasn't taking a cheap shot - and appreciate the response - even if it was an "I don't know".

I realise this is a volunteer web site and if you look at any of the Crystal boards you will see I am a regular contributor too.

 

[IdoMillet]

Here's a relevant KnowledgeBase Article:
_________________________________________

KBase Article ID:c2016008

How can you report off Active Directory when using Crystal Reports (CR) 8.5, 9, or 10?

Resolution

This article contains three methods for reporting off data contained in Active Directory.

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 and 10:

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

Crystal Reports 9 and Crystal Reports 10 use a 'Command Object' to query for Active Directory data.

1. For Crystal Reports 10 apply the following hot fix:

ftp://ftp.crystaldecisions.com/outgoing/ehf/dbex10win_en.zip

For Crystal Reports 9 apply the following hot fix:

ftp://ftp.crystaldecisions.com/outgoing/EHF/cr90dbexwin_EN.zip

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

3. Create a new blank report.

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

5. 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 to step 6.

6. 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'

7. Save the command object.

8. Add the command object to your report.

9. 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.

MORE INFORMATION:

For information about reporting off CSV files, search for article c2007719 on our support site at:

http://support.businessobjects.com/search

More information on exporting Active Directory information to a CSV file can be found at:

http://www.computerperformance.co.uk/Logon/CSVDE_LDIFDE.htm

____________________________________________

hth,
- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[monkeyboy76]

Thanks alot thats spot on, shall go away and see if i can break things now :O)


cheers

t