Field information not showing up from Active Directory Data Source

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
blouckswwuAsked:
Who is Participating?
 
frodomanConnect With a Mentor Commented:
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
 
blouckswwuAuthor Commented:
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
 
mlmccCommented:
How do you want it displayed?

mlmcc
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
blouckswwuAuthor Commented:
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
 
frodomanCommented:
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
 
frodomanCommented:
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
 
blouckswwuAuthor Commented:
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
 
frodomanCommented:
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
 
blouckswwuAuthor Commented:
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
 
blouckswwuAuthor Commented:
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
 
blouckswwuAuthor Commented:
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
 
frodomanCommented:
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
 
blouckswwuAuthor Commented:
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
 
blouckswwuAuthor Commented:
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
 
blouckswwuAuthor Commented:
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
 
frodomanCommented:
Bill,

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

Cheers,

frodoman
0
 
blouckswwuAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.