how to extract data from active directory.

[#1]

Hi All,

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

Regards

Ashish

[suniljain]

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]

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

[#4]

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 · Accepted solution]

where is cn, givenname,  title in the script?

[#6]

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

[shraddha_g]

Hi,

I tried this.

It is giving me error as QVX_UNEXPECTED_END_OF_DATA.

is there anything I can do?

[#8]

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

[#9]

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

[Dimes_Dias]

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