topic Re: how to extract data from active directory. in QlikView

how to extract data from active directory.

Tue, 05 Nov 2013 10:11:10 GMT

Hi All,

Please let me know step by step qlikview script for extracting data from Active directory.

Regards

Ashish

Re: how to extract data from active directory.

suniljain — Tue, 05 Nov 2013 10:19:28 GMT

You can list local users and groups using the Net Localgroup command and direct the output to a file.

http://www.windows-commandline.com/2010/09/list-of-user-groups-command-line.html

Or you can write a VBScript or Powershell script:

http://gallery.technet.microsoft.com/scriptcenter/efd18173-21d6-4805-bf04-97f7b30d91be

Re: how to extract data from active directory.

israrkhan — Tue, 05 Nov 2013 10:59:15 GMT

Hi,

1) Create a connection, Set the Database drop-down list to OLEDB and click on Connect.

2) Select OLEDB Provider for Microsoft Directory Services and click on Next. On the

Connection tab, select Use Windows NT Integrated Security and click on OK.

3) Enter the following script after the database Connect statement (modify the LDAP

URI as appropriate for your domain):

Load *,SubField(distinguishedName, ',') As Properties;

SQL SELECT

displayName,

distinguishedName,

SAMAccountName,

mail,

userPrincipalName,

SN,

givenName,

physicalDeliveryOfficeName,

userAccountControl

FROM 'LDAP://mydomain.local'

WHERE objectCategory = 'Person' AND objectClass = 'user';

4). Reload the script.

The provider allows to make these calls against the Active Directory.

The distinguishedName field will return the full level of the name; for example:

CN=Angela Bloggs,OU=Marketing,OU=Other Users,DC=mydomain,DC=local

Hope it Helps :

Khan

Re: how to extract data from active directory.

Thu, 07 Nov 2013 11:27:04 GMT

Hi Israr,

When i am active directory quey it throw error massage and not given proper result.

Error massage- (

SQL error: No error
Script line: SELECT cn, sn, givenname, title, businessCategory FROM 'LDAP://mydomain.local'

' WHERE objectCategory = 'Person' AND objectClass = 'user'

)

Regards

Ashish

Re: how to extract data from active directory.

israrkhan — Thu, 07 Nov 2013 11:33:45 GMT

where is cn, givenname, title in the script?

Re: how to extract data from active directory.

Thu, 07 Nov 2013 13:19:26 GMT

cn and given is field in active directory table which i want to extract.

Re: how to extract data from active directory.

shraddha_g — Tue, 15 Nov 2016 09:13:43 GMT

Hi,

I tried this.

It is giving me error as QVX_UNEXPECTED_END_OF_DATA.

is there anything I can do?

Re: how to extract data from active directory.

Tue, 29 Nov 2016 15:43:14 GMT

Hi Ashish,

Thank you for the example and I tried this script with our Qlik Sense Environment. Connector works fine, at least the Connectivity Testing responded with an "OK". The script runs through the connect, but then throws an error QVX_UNEXPECTED_END_OF_DATA.

I know that there's a discussion out on the net, that LDAP in fact is not a database. But at least its' a hierarchical datastructure none the less.

Thus anyone has an idea how to tackle that error?

Best regards,

Chris

Re: how to extract data from active directory.

Tue, 29 Nov 2016 16:29:33 GMT

I tried several approaches. Here's a quick & dirty one: I found out that there is this max number of records you can select with loading from LDAP, which is exactly 1000. Thus, if you miss any users, you will need to duplicate/triplicate the whole Loop as many times until all of the LDAP data has been loaded. The variable arg needs to reach the last name of the users.

SET RootDse = 'LDAP://host.name:port';

CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Data Source=LDAP://host.name:port;Mode=Read];

LET arg=chr(01);

ADUsers:

FIRST 1000

LOAD DISTINCT

cn,

sAMAccountName as Login,

name as UserName

;

SQL select

cn,

sAMAccountName,

name

FROM '$(RootDse)'

WHERE

objectCategory='person' AND

objectClass = 'User' AND

name>'$(arg)'

order by name; // Get rows where "name" is GT the arg

EXIT DO WHEN ScriptError > 1 // Stop loop if SELECT has error

EXIT DO WHEN NoOfRows('nameTable') = 0; // Stop loop if SELECT returns nothing

EXIT DO WHEN peek('UserName') = '$(arg)'; // If the last "name" read is EQ to arg -- no more entries

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

LOOP

Yes, duplicating/triplicating - It's not nice coding, but it works 🙂

Otherwise when not setting the Limit of FIRST 1000 the script throws an error QVX_UNEXPECTED_END_OF_DATA.

Kind regards,

Chris

Re: how to extract data from active directory.

Dimes_Dias — Tue, 22 Jan 2019 20:17:43 GMT

Eu estava com dificuldades nessa parte, e o seu passo a passo me ajudou muito. Muito obrigado.

Re: how to extract data from active directory.

AlbaJPDupe — Thu, 25 May 2023 10:16:58 GMT

Dear all,

Probably you will have to limit it with first 1000 and then do a bucle.

I tried this code and it works for me:

Table:

Load *

;

First 1000

SQL Select

From table_name;

Hope it helps 🙂

Regards,

Alba