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
 
SELECT 
REPLACE(ADsPath,'LDAP://','') AS groupPath ,
LEFT(REPLACE(ADsPath,'LDAP://CN=',''), charindex(',', REPLACE(ADsPath,'LDAP://CN=','')) - 1) AS groupname
INTO #tblqlikgroups
FROM OpenQuery ( 
 ADSI, 
 'SELECT displayname,
 samAccountName, 
 whenCreated ,
 ADsPath 
 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
 BEGIN 
 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 ( 
 ADSI, 
 ''SELECT displayName,
 samAccountName,
 lastLogon ,
 whenCreated,
 mail
 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
EXEC(@q)
 
fetch NEXT FROM active_directory_users_and_groups INTO @grouppath, @groupname
 
END
 
--close and deallocate the cursor
close active_directory_users_and_groups
deallocate active_directory_users_and_groups

 

 

Leave a Reply