Fetch users from Active Directory in Sense script.

[Florentin74]

Hi there,

I have not been able to fetch users from Active Directory in a Qlik Sense script via the OLE DB provider for Microsoft Directory Services. I am able to do it in Qlikview with the following script:

OLEDB CONNECT32 TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

LET vLDAP_UNC = 'LDAP://*****.COM';
LET vUsername=chr(01); // Set search start name to a very low value

DO

ADUsers:
FIRST 901  // Workaround for undefined SQL error. Uncomment this line if you are getting the error
LOAD
  name     as userName,
  distinguishedName  as userDN,
  sAMAccountName  as userID,
  company,
  department,
  OU     as orgUnit,
  c     as country,
  '$(vUsername)'   as vUsername
;
SQL SELECT
  name,
  distinguishedName,
  sAMAccountName,
  company,
  department,
  OU,
  c
FROM
  '$(vLDAP_UNC)'
WHERE
  SAMAccountName >'$(vUsername)'
;

EXIT DO WHEN ScriptError > 1; // Stop loop if SELECT has error
EXIT DO WHEN NoOfRows('ADUsers') = 0;  // Stop loop if SELECT returns nothing
EXIT DO WHEN peek('userID') = '$(vUsername)';  // If the last "name" read is EQ to arg -- no more entries

LET vUsername = peek('userID'); // Set the arg to the last "name" read
LOOP

In Sense I have to create a connection... All I type as source is LDAP://<domain>.com ...

How do I go on from here? Does anyone know?

Please notice that I cannot switch to legacy mode, because this will affect all apps on the server.

[lucienorrin]

Yes Sir, we are using Sense.

I can create the data connection to AD using the built in "OLE DB provider for Microsoft Directory Services" and testing succeeds but I cant figure out the correct syntax to query successfully using this connection.

[Tyler_Waterfall]

I'm sorry but I have not attempted this in Sense. The syntax from Rob's app wasn't working?

[lucienorrin]

I can't get it working, but I am not very good with Sense yet either.

I update the RootDse to my domain

and replace this:

CONNECT TO 'Provider=ADsDSOObject';

with this:

CONNECT TO 'lib://domain.com';  (using my domain name)

Then I get the above error about CONNECTs other than LIB CONNECT are not available in this script mode...

[Tyler_Waterfall]

You should be setting the data connection parameters in the actual data connection and then in the load script you reference the data connection name.

So, if you are able to create the data connection and you name it "my_active_directory", then in the load script you should be able to click on the "select data" icon below the "my_active_directory" data connection in the right-hand panel and select your data there or click on the icon "insert connection string".

The result would be a simple script line generated:

LIB CONNECT TO 'my_active_directory';

followed by your load script statements.

[lucienorrin]

Ah, you are right, I should have caught that. Thanks twa for pointing me in the right direction.

Once the connect statement is correct I can query for a small number of objects and get results. But when I broaden my search I get the unexpected end of data error mentioned here. Connecting to and Querying Active Directory for Users.

So far none of the suggestions are working for me.

I'm not sure if I am missing another simple error or something bigger.

[ArnadoSandoval]

I am sharing my finding to this thread hoping it could help others.

• The Data Connection required to connect to the Microsoft Active Directory Services (32 bits)
• Name: LDAP--- XXX.com.au (no username and no password)

• Load Script:

LIB CONNECT TO 'LDAP---XXX.com.au';

AD_Data:
Load title,
    telephoneNumber,
    cn,
    sn,
    name,
    mail,
    memberof;

SQL SELECT title,
          telephoneNumber,
          cn,
          sn,
          name,
          mail,
          memberof
    From 'LDAP://DC=XXX, DC=COM, DC=AU' // you need all 3 DCs
    WHERE 
          objectClass='user' AND objectCategory='Person';

• More AD Columns names:

I also found this links with more AD Columns names: https://support.symantec.com/en_US/article.HOWTO9869.html

[lucienorrin]

tinaco110 that is very similar to what I came up with.

It works fine if it returns a small number of objects (<1000 I think).

However I expect to get closer to 25k results and I end up with the QVX_UNEXPECTED_END_OF_DATA error.

[ArnadoSandoval]

lucienorrinOur AD is pretty small, it returned 538 records, but I was trying to write a 100% Qlik Sense script, something missing from all contributors to this thread.

I got the error (QVX_UNEXPECTED_END_OF_DATA) after commenting out the " AND objectCategory = 'Person' " in my query, just asking for all records in the 'user' class.

This is when I realized my Active Directory Knowledge is limited, the "Person Category" returns all its record, but a different category with a name to be identified triggers the Unexpected End of Data.

It will be good to find good Active Directory documentation listing all its Categories and Classes, if that documentation includes columns name will be even better.

[tomovangel]

Hello guys, long time no posts in this discussion. 

Has anyone successfully loaded users, and groups .

What I need is to get specific group and the users inside this group, so I can use it as Section Access in Qlik Sense. 

I am familiar with Rob's Qlikview Cookbook, but I can't use the module to connect groups and users like he does in QV. ( qlik sense doesn't have Modules)

Any help will be highly appreciated

Best Regards,

Angel Tomov

[Florentin74]

I haven't ...