Getting the Employeeid from Active directory

[qliksus]

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

Next

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

[bnichol]

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,

B

[qliksus]

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

;

[qliksus]

Guys any updates

[qliksus]

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

[bnichol]

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.

B

[bnichol]

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,

B

[qliksus]

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

DO

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

[qliksus]

Guys any update on this

[andy]

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