topic Getting the Employeeid from Active directory in QlikView

Getting the Employeeid from Active directory

qliksus — Mon, 20 Jun 2011 12:46:59 GMT

Hi all,

im using the following code and macro to fetch the data from Active directory

Code:

CONNECT TO
[
Provider=ADsDSOObject;
Encrypt Password=False;
Integrated Security=SSPI;
Data Source=collercapital.com;
Mode=Read;
Bind Flags=0;
ADSI Flag=-2147483648;
];

SET RootDse = 'DC=CTS, DC=com';

CONNECT TO 'Provider=ADsDSOObject';

LET arg=chr(01);
DO
GroupTable:
LOAD
name as GroupName,
distinguishedName as GroupDN
;
SQL select
name, distinguishedName
FROM 'LDAP://$(RootDse)' WHERE objectCategory='group'
and name='QlikView Experts (Cognizant)';

EXIT DO WHEN ScriptError > 1
EXIT DO WHEN NoOfRows('GroupTable') = 0;
EXIT DO WHEN peek('GroupName') = '$(arg)';

LET arg=peek('GroupName');
LOOP

GroupMembers:
LOAD

GroupDN AS GroupKey,
GroupName AS UserGroup,
subfield(GroupMembers(GroupDN), ';') as GroupUser

RESIDENT GroupTable;

/*
Clean up
*/
SET arg=;
SET vUserType=;

Now the macro code is

Function GroupMembers(DN)

Set objItem = GetObject("LDAP://"&DN)

strmember = objItem.GetEx("sAMAccountName")

ret = ""

For Each Item in strmember

ret = ret & Item & ";"

GroupMembers = Left(ret, Len(ret)-1) 'Drop trailing semicolon

End Function

Everthing is working fine but i could not get the employee id for a particuolar group i.e the column GroupUser which is returned

through the macro is not having the employee id SO please let me know how can take the employee id column

Getting the Employeeid from Active directory

bnichol — Mon, 20 Jun 2011 13:16:51 GMT

I use a similar macro, but with

strmember = objItem.GetEx("member")

instead of

strmember = objItem.GetEx("sAMAccountName")

Then the list of member DNs is linked to user information with the following script..

Users:
LOAD
name as "Network ID",
sn as "Last Name",
givenName as "First Name",

distinguishedName as GroupUser
;
SQL select
name, distinguishedName, sn, givenName
FROM 'LDAP://ou=Users,dc=company,dc=com';

Good luck,

Re: Getting the Employeeid from Active directory

qliksus — Tue, 21 Jun 2011 05:37:50 GMT

HI thanks for the reply im getting a Script error when i reload the table u have said

Users:
LOAD
name as "Network ID",
sn as "Last Name",
givenName as "First Name",

distinguishedName as GroupUser
;
SQL select
name, distinguishedName, sn, givenName
FROM 'LDAP://ou=Users,dc=company,dc=com'

But when i remove that ou=Users then there is no error .Why is that happening

;

Re: Getting the Employeeid from Active directory

qliksus — Tue, 21 Jun 2011 11:21:29 GMT

Guys any updates

Getting the Employeeid from Active directory

qliksus — Wed, 22 Jun 2011 05:25:30 GMT

Guys i have one more problem the load statement of the Employee table is just fetching the same records again

and again its showing the same 20,000 records fetched intially its not going to the next record even though i am

using the loop .Please suggest what i am missing

Re: Getting the Employeeid from Active directory

bnichol — Wed, 22 Jun 2011 12:49:15 GMT

But when i remove that ou=Users then there is no error .Why is that happening

I'm not sure what the organization of your AD structure is, but you probably don't have users in a separate folder. My company has put all the users under the branch ou=Users, that's why the script works for me.

Getting the Employeeid from Active directory

bnichol — Wed, 22 Jun 2011 13:00:46 GMT

I'm not sure what you are refering to as the "Employees" table, based on the original load script you provided.

If it is the logic for the "Users" table, then the script should not be in the loop. This will return all the users in your AD structure and will be associated to the GroupMembers table by the "GroupUser" field, that exists in both.

Good luck,

Getting the Employeeid from Active directory

qliksus — Thu, 23 Jun 2011 05:55:10 GMT

Hi bnichol ,

thanks for the reply i have missied one condition name>'$(arg)' in the loop of the original script so it was fetching all the records and joining was working fine But now i have one more problem some of the employees are not loaded in my script .Here is the final and correct script i am using please refer this for ur further reply

LET arg=chr(01); // Set search start name to a very low value

UserTable:

LOAD

name as UserName,

sAMAccountName as LoginName,

mid(SubField(distinguishedName,',OU',1),4) as GroupUser,

;

SQL select

name, sAMAccountName, distinguishedName // Fields to select

FROM 'LDAP://$(RootDse)'

WHERE objectCategory='person'

AND name>'$(arg)'

; // Get rows where "name" is GT the arg

EXIT DO WHEN ScriptError > 1; // Stop loop if SELECT has error

EXIT DO WHEN NoOfRows('nameTable') = 0; // Stop loop if SELECT returns nothing

EXIT DO WHEN peek('UserName') = '$(arg)'; // If the last "name" read is EQ to arg -- no more entries

LET arg = peek('UserName'); // Set the arg to the last "name" read

LOOP

Now in the above script some employees are missed but when i load this table with their employee id the employees are loaded

can u guess any reason behind that

Getting the Employeeid from Active directory

qliksus — Fri, 24 Jun 2011 11:45:45 GMT

Guys any update on this

Getting the Employeeid from Active directory

andy — Thu, 03 Nov 2011 15:31:26 GMT

Did you solve this?

I'm having the same problem some users doesn't being fetched but if I set name=their name then they are fetched. I also have the annoying "No error" error. Guess it has something to do with that

/Andy

Getting the Employeeid from Active directory

andy — Fri, 04 Nov 2011 11:01:46 GMT

I think my problem of some users doesn't being fetched may have come from some 1500 limit when reading the AD. I turned it the other way around for my specific problem by first fetching the groups and then retrieving their members.

Found some macro from Rob Wunderlich to grab the members it works when run on desktop but not on publisher even though I have checked system access in the file and allow macro and allow unsafe macros on QV 9 server. The user running the publisher is able to reload the document when in desktop.

Now I'm going for the logparser solution....

/Andy

Re: Getting the Employeeid from Active directory

qlikviewforum — Mon, 22 Jul 2013 16:39:25 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