Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

how to extract data from active directory.

Hi All,

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

Regards

Ashish

1 Solution

Accepted Solutions
israrkhan
Valued Contributor II

Re: how to extract data from active directory.

where is cn, givenname,  title in the script?

8 Replies
suniljain
Honored Contributor

Re: how to extract data from active directory.

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

israrkhan
Valued Contributor II

Re: how to extract data from active directory.

Hi,

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

2013-11-05_145409.png

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

Not applicable

Re: how to extract data from active directory.

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

israrkhan
Valued Contributor II

Re: how to extract data from active directory.

where is cn, givenname,  title in the script?

Not applicable

Re: how to extract data from active directory.

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

shraddha_g
Honored Contributor III

Re: how to extract data from active directory.

Hi,

I tried this.

It is giving me error as QVX_UNEXPECTED_END_OF_DATA.

is there anything I can do?

Not applicable

Re: how to extract data from active directory.

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

Not applicable

Re: how to extract data from active directory.

Hi

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.nameSmiley Tongueort';

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

LET arg=chr(01);

DO

  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