Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Active Directory load

I'm loading data from Active Directory.  The user select looks more or less like this:


SQL SELECT
distinguishedName,
cn,
displayName,
mail
FROM '$(vLDAP)';

Question: is it possible to load only the users who belong to a certain Group or several Groups before loading Groups themsleves?

Thanks,

Michael

23 Replies
Bill_Britt
Former Employee
Former Employee

Hi

I would think you could use something like:

FROM ''LDAP://DC= test.com'' WHERE memberOf= 'CN=[groupname]'

Bill

Bill - Principal Technical Support Engineer at Qlik
To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
Anonymous
Not applicable
Author

Thanks Bill,

This was the first thing I've tried, and it returns no records.  (I checked Group names when loading Groups separately).

I also tried to use "like":
WHERE memberOf like 'CN= ABC%',
But in this case just getting error on reload.

Any other ideas?

erikzions
Creator
Creator

This is how I do that.  Tough to be fair I am sure I got this from Rob Wunderlich.  Modified slightly to meet my needs.

ADGroups:

  LOAD

  name as GroupName,

  distinguishedName as GroupDN,

  info as GroupInfo

  ;

  SQL select

  name, distinguishedName,

  info

  FROM 'LDAP://$(RootDse)'  WHERE objectCategory='group'

  AND name = 'QV_*';

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The way Bill posted is the way I do it and it works for me. Check out Qlikview Cookbook: Load Users and Groups from Active Directory for sample code.

-Rob

http://masterssummit.com

http://robwunderlich.com

Anonymous
Not applicable
Author

Thanks Eric,

You syntax works but it doesn't answer my question - can I load users that belong to a particular group without loading the group itself?

Certainly I can load all, and remove the data I don't need, but it is rather inefffective...

Regards,

Michael

Anonymous
Not applicable
Author

Thanks Rob,

My current "AD load" app is in fact based on your older version, the one with macro.  I've downloaded your newer "no-macro" version and going to try it.  If nothing else, the absence of macro has value by itself.

Regards,

Michael

erikzions
Creator
Creator

Try this.  Again I got this from Rob, so you can refer to his code too.

CONNECT TO 'Provider=ADsDSOObject';

  ADGroups:

  LOAD

  name as GroupName,

  distinguishedName as GroupDN,

  info as GroupInfo

  ;

  SQL select

  name, distinguishedName,

  info

  FROM 'LDAP://$(RootDse)'  WHERE objectCategory='group'

  AND name = 'QV_*';

  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('GroupName') = '$(arg)';  // If the last "name" read is EQ to arg -- no more entries

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

LET arg= NoOfRows('ADGroups');

For i = 0 to NoOfRows('ADGroups') - 1

LET Group = peek('GroupDN', $(i), 'ADGroups');

  ADGroupMembers:

  LOAD DISTINCT

  distinguishedName as UserDN, '$(Group)' as GroupDN, name, userPrincipalName,

  left(userPrincipalName,(FindOneOf(userPrincipalName,'@')-1)) as zNumber;

  SELECT distinguishedName, name, userPrincipalName

  FROM 'LDAP://$(RootDse)' WHERE MemberOf='$(Group)';

NEXT i;

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The no-macro version runs a lot slower -- but it work in server reloads, which is usually critical.

-Rob

Anonymous
Not applicable
Author

I see, load the groups I need first, and get members after this.  Looks like working, after some script cleanup, but sometimes fails depending on groups.  Something to figure out...