Active Directory Timestamps

[bnichol]

I'm having a problem selecting data from an AD directory structure using the following SQL...

CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Location={IP Address};Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];
LET arg=chr(01); // Set search start name to a very low value
DO
UserTable:
LOAD
name as "Network ID",
sn as "Last Name",
givenName as "First Name",
distinguishedName as UserDN,
mail,
lastLogon,
pwdLastSet
;
SQL select
name, distinguishedName, sn, givenName, mail, department, lastLogon, pwdLastSet // Fields to select
FROM 'LDAP://dc={company name},dc=com';

I can retrieve data for all the attributes except the timestamp items (lastLogon & pwdLastSet). They return as blanks.

Does anyone have an idea of what I'm doing wrong?

Any suggestions would be appreciated,
B

[#2]

Your connection string is creating an anonymous bind, so my first guess is that these fields require an authenticated login to retrieve. (That's been the case when I've had a blank field issue.)

Try using the "OLE DB Provider for Microsoft Directory Services", mostly to create the password hash, and it should create a connection similar to this:

CONNECT TO [Provider=ADsDSOObject;User ID=Domain\user;Encrypt Password=False;Data Source=LDAP://ldap.acme.com;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648] (XPassword is ZXXXXXXXXXXXXXXXXXB);

Lemme know if that does it.

-DJ

[bnichol · Accepted solution]

Thanks for the suggestions, but it turned out being a LDAP Timestamp issue. Here's the modified load statement that I used...

LOAD
name as "Network ID",
sn as "Last Name",
givenName as "First Name",
distinguishedName as UserDN,
mail,
IF(lastLogon > 0, (date(left(lastLogon,11)/86400-109205,'YYYY-MM-DD')),'') as "Last Logon",
IF(pwdLastSet > 0, (date(left(pwdLastSet,11)/86400-109205,'YYYY-MM-DD')),'') as "Last Password Reset"
;

B

[qlikviewforum]

Hi,

The QVW which tries to load the data from LDAP loads only the partial data frequently. We are unable to find the root cause of the issue as the QVW doesn't fails but it loads only the partial data.Can someone please help us to identify the root cause of the issue. It has been happening from quite a long time but we didn't succeed in finding the root cause of the issue. Hope someone can help us for the same.

Regards,

qvforum

[#5]

Thank you helped a lot