Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist II
Specialist II

where is cn, givenname,  title in the script?

View solution in original post

9 Replies
Highlighted
Master
Master

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

Highlighted
Specialist II
Specialist II

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

Highlighted
Not applicable

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

Highlighted
Specialist II
Specialist II

where is cn, givenname,  title in the script?

View solution in original post

Highlighted
Not applicable

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

Highlighted
Partner
Partner

Hi,

I tried this.

It is giving me error as QVX_UNEXPECTED_END_OF_DATA.

is there anything I can do?

Highlighted
Not applicable

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

Highlighted
Not applicable

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.name:port';

CONNECT TO [Provider=ADsDSOObject;Encrypt Password=False;Data Source=LDAP://host.name:port;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

Highlighted
Contributor
Contributor

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