Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
I would think you could use something like:
FROM ''LDAP://DC= test.com'' WHERE memberOf= 'CN=[groupname]'
Bill
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?
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_*';
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
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
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
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;
The no-macro version runs a lot slower -- but it work in server reloads, which is usually critical.
-Rob
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...