Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

everest226
Contributor III

import email from AD group

Hi All,

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

Thanks

stalwar1

tamilarasu

Tags (1)
4 Replies

Re: import email from AD group

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

adamdavi3s
Honored Contributor

Re: import email from AD group

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

Adam,

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

Capture.PNG

adamdavi3s
Honored Contributor

Re: import email from AD group

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