Solved

Field information not showing up from Active Directory Data Source

Posted on 2004-09-16
17
1,648 Views
Last Modified: 2010-05-18
I have created a report that connects to our Active Directory database.  One of the fields that I want to report on (PwdLastSet) is not showing up on the report even though I know there is data.   Here is an example of what is showing up in Active Directory for this field:

pwdLastSet: 127298886538437500

Crystal Reports shows the fields type 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?

Thanks
0
Comment
Question by:blouckswwu
  • 10
  • 6
17 Comments
 

Author Comment

by:blouckswwu
ID: 12077534
I just found out that the format that this field is set as: FILETIME, a count of 100 nanosecond intervals since January 01, 1601.  Is there a way to get Crystal to handle this type of format?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12077782
How do you want it displayed?

mlmcc
0
 

Author Comment

by:blouckswwu
ID: 12077840
There is a formula that I can use to convert this into a acutal date.  The problem is that I can't get the data into Crystal to convert it.
0
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 42

Expert Comment

by:frodoman
ID: 12077942
It's ugly but I think it'll work:

DateAdd('d',DateDiff('d','01/01/1601','12/30/1899'),DateValue(int({field})));


The tricky part is the DateValue function assumes a number is the # of days since 12/30/1899 so after this function converts it to a date you then need to add the number of days between the assumed 12/30/1899 start date and the actual 1/1/1601 start date.

HTH

frodoman
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12077957
Oops - had the order of the dates reversed and you need to convert them to date formats also.  Try this instead:

DateAdd('d',DateDiff('d',cdate('12/30/1899'),cdate('01/01/1601')),DateValue(int({field})));
0
 

Author Comment

by:blouckswwu
ID: 12078202
Thanks for the input, but I am unable to use this formula since the data is not loading into Crystal Reports.  I am using a SQL command to bring in the data using OLE for Microsoft Directory Services.  I need a way to tell Crystal that I need this particular field to be a long integer format.  Right not its coming in as a string and the field in Active Directory is a long int.  Is there a way to to force a field format in the command statement?

Thanks for your input and help!
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12078342
Have you tried using the sql CAST statement?  

   SELECT ...., CAST (pwdLastSet AS LONG), ....

I can't hit an AD server at the moment to test but it seems like it would work...

The alternative would be to let it come in as a string and then use a formula to convert it once it's in Crystal.

frodoman
0
 

Author Comment

by:blouckswwu
ID: 12078449
Right now I can't get any data to come into Crystal to be able to convert it.  I'm just guessing that the reason the data is not there is because of a coversion problem with that long number.  Here is an example of what the field actually has in Active Directory:

pwdLastSet = 127298886538437500

I tried the cast statement and got the following error:

Failed to open rowset
Details: ADO Error Code: 0x80040e14
Source: Provider
Description: One or more errors occured during processing of command.
Native Error: -2147217900

Gotta love those descriptive error messages "One or more errors occurred during processing of command".

Here is my SQL statement (with the cast statement):

SELECT
  cn,
  lastlogon,
  logoncount,lastlogoff,
  Cast(PwdLastSet as Long),
  whenCreated

FROM 'LDAP://OU=WWU_Staff,DC=williamwoods,DC=local'  
WHERE objectClass='user'

It pulls data in when I take the cast out, but then the pwdLastSet comes in as blanks or nulls.

Thanks for your help!
0
 

Author Comment

by:blouckswwu
ID: 12084130
I finally am able to query using Query Analyzer and ran the following query:

SELECT *
FROM OpenQuery( ADSI2, 'SELECT name, adsPath, pwdlastset
                   FROM ''LDAP://OU=WWU_Staff,DC=williamwoods,DC=local''
                   WHERE objectCategory = ''Person'' AND objectClass= ''user''')


Could not get the data of the row from the OLE DB provider 'ADSDSOObject'. Could not convert the data value due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x40eda:  Data status returned from the provider: [COLUMN_NAME=name STATUS=DBSTATUS_S_OK], [COLUMN_NAME=adsPath STATUS=DBSTATUS_S_OK], [COLUMN_NAME=pwdlastset STATUS=DBSTATUS_E_CANTCONVERTVALUE]].

This confirms what I thought.  My query cannot handle pwdlastset field in active directory.  There has to be a way to get this coverted in order for it to show up.

Thanks
0
 

Author Comment

by:blouckswwu
ID: 12101972
I exported this these fields (name, adsPath, pwdlastset) to a text file using the CSVDE utility.  The pwdlastset field did export correctly.  Now when I try to import the data into SQL server, Excel or Access they do not display correctly.  Access just refuses to import, Excel imports the field, but displays it incorrectly and SQL server gives me an error.  The number is just so large I think the software is freaking out on it.  Anyone have any ideas?
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12102007
Have you tried or considered just selecting a smaller number:  "Select floor(field / 1000000) as fieldname" for example?  Unless you really need the info down to the nanosecond you can easily chop off the end of the number and then convert from there to a date...

frodoman
0
 

Author Comment

by:blouckswwu
ID: 12102048
It seems the query does not let me put any formating into the select statement.  It gives an error when I try to do that:

Could not get the data of the row from the OLE DB provider 'ADSDSOObject'. Could not convert the data value due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x40eda:  Data status returned from the provider: [COLUMN_NAME=name STATUS=DBSTATUS_S_OK], [COLUMN_NAME=adsPath STATUS=DBSTATUS_S_OK], [COLUMN_NAME=pwdlastset STATUS=DBSTATUS_E_CANTCONVERTVALUE]].

I am toying with Excel right now to see if I can get it to come up correctly.
0
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 12102135
Can you cast it as a string and then manipulate it after it's been selected - maybe take a substr to get the leftmost x characters and then convert that to a number?  

This is hideous - I'm glad I've never cared about this field!

frodoman
0
 

Author Comment

by:blouckswwu
ID: 12102291
LOL...Its frustrating how you can make gains on getting the information out and then cannot do anything with it because of the way it stores it in AD.  I'm still looking at a few things with Excel.  I will keep you posted.

Thanks for your input!

Bill
0
 

Author Comment

by:blouckswwu
ID: 12105123
Ok..found a way around this problem.  Here are the steps that I performed to get what I need:

1.) Create a batch file to run CSVDE.exe with the following parameters:
csvde -f aduserlist.csv -r "(objectclass=user)" -l "DN,cn,objectclass,pwdlastset,createtimestamp"  -- You pick the attributes that you need

2.) Create/schedule a scheduled task so the batch file will run everynight (or twice a day..I'm undecided on this).

**** I would of like to be able to just use a DTS package to do this, but I am limited to 1,000 records when I query directly off of Active Directory.  If you use CSVDE, there is no limit.

3.) Create/schedule a DTS package in SQL server to run after the batch file is run (everynight or twice a day) to import the text file aduserlist.csv.  Make sure you check the drop and recreate table.

4.) I edited the table to include a computed field that pulls out the last 7 digits.  This shows me the lastPwdSet in seconds.  This is called pwdtest in my table.  

5.) Now you will be ready to report this information using Crystal Report or any other reporting software.

6.) To convert integer8 (or 64 bit) datetime that Active Directory uses you will use this formula to get a correct date format:

(DateAdd(hh,-5,(DateAdd(ss,(pwdtest - 11644473600),'1/1/1970'))))

The date that active directory uses for lastPwdSet is the number of 100 nanoseconds has passed since 1/1/1601 in UTC time.  SQL server does not handle dates ranges this far back (at least I got an overflow error...my guess the number was too long).  So I found out that there are 11,644,473,600 seconds between 1/1/1601 and 1/1/1970.

The inner DateAdd command takes the pwdtest date - 1/1/1970 date and converts advances this amount from 1/1/1970.
Example:
pwdLastSet (Actual value in Active Directory) = 127204023240468000
pwdtest (computed field (left([pwdLastSet],11)) in MS SQL that strips the last 7 digits of pwdLastSet field = 12720402324
11644473600 = The number of seconds that have elapsed between 1/1/1601 - 1/1/1970

DateAdd(ss,1075928724, '1/1/1970') = 2004-02-04  21:05:24.000

The outer DateAdd convert the UTC time to your local timezone.  Since I'm 5 hours behind UMT you have to subtract 5 hours off the 2004-02-04  21:05:24.000 value.  The result is: 2004-02-04  16:05:24.000

***  You might be able to put this formula in the computed fieldin MS SQL, but I couldn't figure it out.  Please respond if you know this is done.

There are some date fields in Active Directory that reports an actual date value, but there are some that use this integer8 value.  You will have to use a formula to convert it to a usable date/time format.

Just let me know if anyone has any questions.

Thanks to all that helped in their own way!

Bill



0
 
LVL 42

Expert Comment

by:frodoman
ID: 12105221
Bill,

Nice work.  You didn't have to award points though as I'm not sure I helped much...

Cheers,

frodoman
0
 

Author Comment

by:blouckswwu
ID: 12105233
Actually I'll give ya whatever just by giving any input.  Your date statement actually gave me the idea to do this.

Thanks again bud and have a good day!

Bill
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question