Querying LDAP to pull back people in AD groups

So you’re using AD to control access to your dashboard folders, but what if you want to show this in Qlik? For example to load it into the Governance Dashboard, you can see my post on this on the Qlik community here: https://community.qlik.com/docs/DOC-17259


The code is pretty straight forward when you think about it. Obviously the first thing to do is to add a linked server if you haven’t already. This doesn’t require any changes to run

exec sp_Addlinkedserver @server= N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource' 


Then you can set up a really quite straight forward cursor to load the data into a table. The reason we need the cursor is we can only query one AD group at a time.


--create procedure test.ad_qlik_groups_Test as

IF OBJECT_ID('tempdb.dbo.#tblqlikgroups', 'U') IS NOT NULL DROP TABLE #tblqlikgroups; 
IF OBJECT_ID('tempdb.dbo.#test', 'U') IS NOT NULL DROP TABLE #test; 
IF OBJECT_ID('dbo.active_directory_qlikview_users_and_groups', 'U') IS NOT NULL truncate TABLE active_directory_qlikview_users_and_groups;

--get list of AD groups for Qlik

replace(ADsPath,'LDAP://','') as groupPath ,
LEFT(replace(ADsPath,'LDAP://CN=',''), charindex(',', replace(ADsPath,'LDAP://CN=','')) - 1) as groupname
into #tblqlikgroups
FROM OpenQuery ( 
 'SELECT displayname,
 whenCreated ,
 FROM ''LDAP://DC=yourdomain,DC=co,dc=uk'' 
 WHERE objectClass = ''Group'' 
 ') AS tblADSI
WHERE samaccountname like'%Qlik%' --this is where we tell the system what groups we want to call back

-- declare the variable to pass to the cursor

Declare @grouppath varchar(max), @groupname varchar (max) 

 -- declare the cursor and tell it what we want to use for our loop
 Declare active_directory_users_and_groups cursor for
 select grouppath, groupname 
 from #tblqlikgroups

--open the cursor and find the first row
 open active_directory_users_and_groups 
 fetch next from active_directory_users_and_groups into @grouppath, @groupname

--now just loop through until there are no more items to fetch from the table
 while @@FETCH_STATUS = 0
 declare @q varchar(max)
 set @q='INSERT into active_directory_qlikview_users_and_groups 
 SELECT DISTINCT '''+@groupname+''' as groupName, displayname, samAccountName,lastLogon,mail

 FROM OpenQuery ( 
 ''SELECT displayName,
 lastLogon ,
 FROM ''''LDAP://DC=yourdomain,DC=co,dc=uk'''' 
 WHERE objectClass = ''''Person'''' and memberOf='''''+@grouppath+'''''
 '') AS tblADSI '
--print (@q) --use this to test the code, comment out when executing

fetch next from active_directory_users_and_groups into @grouppath, @groupname


--close and deallocate the cursor
close active_directory_users_and_groups
deallocate active_directory_users_and_groups



Leave a Reply