Reporting with Active Directory

[Former Member]

I have to create Crystal report on Active Directory database. The fields I want to use have some date Field for Example lastLogon and pwdLastSet.Active Directory stores the value of these fields in nano seconds since January 01, 1601 and the data type of the field is Large Integer/Interval. Here is an example of what is showing up in Active Directory for this field:

pwdLastSet: 127298886538437500

I am using the LDAP Query in Crystal Report Designer to fetch the data. Below is the example of LDAP Query I am using.

SELECT name, adsPath, pwdlastset

FROM ''LDAP://OU=comp,DC=AS,DC=local''

WHERE objectCategory = ''Person'' AND objectClass= ''user'''

Query runs successfully and I get these objects in Crystal report. Now when I drag these objects in CR I can see the value of name and adsPath but pwdlastset is blank even though the data is there for this field in Active Directory.

Crystal Reports shows the fields type of pwdlastset object as a String [65534] even though the field only contains numbers. Crystal might consider it to be a string since the number is so large. Does anyone know how I can get this data to show up in a report?

[Former Member]

Hi Amrita

The reason of this behaviour should be related to the type of information; in fact, the particular field that shows blank in CR, is ideally a multi-valued attribute.

On the other hand, this is not seem actually Crystal related; Microsoft has acknowledged this limitation in the article ID 299410 at

http://support.microsoft.com/kb/299410

The workaround is to report off a text file:

- From Active Directory Users and Computers or from ADSI Edit (where you would get more information), locate the level where you want the information retrieved from, for instance OU= (top level);

- Right-click on the folder and select u2018Export listu2019;

- Save the file in TXT format.

- From Crystal Reports, create new report;

- From Database Expert, click on u2018Create a new connectionu2019 folder;

- Double-click on u2018Access/Excel(DAO)u2019 folder;

- Change the u2018Database typeu2019 to u2018Textu2019 and select the u2018Database Nameu2019, which is the TXT file;

- Select u2018Finishu2019;

Should you have any questions, please do not hesitate to let us know.

Hope this helps!!!

Regards

Sourashree

[Former Member]

Hi Sourashree,

Thanks for your Prompt reply.

I saw the link you pasted. This link tells the limitation of multivalued attributes in AD however the attributes I am talking about are not multivalued instead these attribute contains a very large number.

I feel that crystal report is not able to understand and cast this number.

I have already gone through the provided workaround but the limitation with this workaround is "we wont be able to get the latest datau201D. Yes we can write a script to get the latest data every night and then use this data but this is not feasible in the application we are running here.

Do you know any way by which we can convert this value in the LDAP query itself we are sending from crystal report? For example To_Timestamp(pwdLastSet) NOTE: This is not the Actual LDAP Query syntax. its just an example.

Thanks & Regards,

Amrita

[Former Member]

Hi Amrita

That was the only workaround in my knowledge.

I would definitely post if i come to know anymore information regarding this.

I would also request you to share if you get any further information on this.

Regards

Sourahsree