topic Active Directory Timestamps in QlikView

Active Directory Timestamps

bnichol — Wed, 23 Mar 2011 14:33:10 GMT

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

Active Directory Timestamps

Wed, 06 Apr 2011 17:21:06 GMT

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

Active Directory Timestamps

bnichol — Wed, 06 Apr 2011 20:35:13 GMT

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"
;

Re: Active Directory Timestamps

qlikviewforum — Mon, 22 Jul 2013 16:43:12 GMT

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

Re: Active Directory Timestamps

Thu, 12 Feb 2015 11:27:17 GMT

Thank you helped a lot