import email from AD group

everest226 — Mon, 13 Mar 2017 22:40:44 GMT

Hi All,

How do i import email, name from AD group, what kind of connection do i need.

Thanks

stalwar1‌

tamilarasu‌

Re: import email from AD group

tamilarasu — Tue, 14 Mar 2017 12:32:10 GMT

Hi Ujjwal,

Sorry for the delay in response. I haven't tried this personally, but the below link might be help you.

Connecting to and Querying Active Directory for Users

Re: import email from AD group

adamdavi3s — Tue, 14 Mar 2017 12:37:02 GMT

If you need to pull from multiple groups I wrote a little bit of code here, which you could easily adapt to Qlik using a loop.

We just happen to pull into SQL first (As we use it in other places)

Querying LDAP to pull back people in AD groups – Qlik and Dirty

Re: import email from AD group

tamilarasu — Tue, 14 Mar 2017 12:47:39 GMT

Adam,

Seems like your website is restricted due to the name . Anyway I'll go home and check in my laptop.

Re: import email from AD group

adamdavi3s — Tue, 14 Mar 2017 12:48:45 GMT

hahahaha that is amusing, I did choose a rather silly name!

Here is the content for you:

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

topic Re: import email from AD group in QlikView

import email from AD group

Re: import email from AD group

Re: import email from AD group

Re: import email from AD group

Re: import email from AD group