Solved

Field information not showing up from Active Directory Data Source

Posted on 2004-09-16
17
1,628 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
Comment Utility
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
Comment Utility
How do you want it displayed?

mlmcc
0
 

Author Comment

by:blouckswwu
Comment Utility
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
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:blouckswwu
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now