Discussion Board for collaboration related to QlikView App Development.
You can list local users and groups using the Net Localgroup command and direct the output to a file.
Or you can write a VBScript or Powershell script:
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;
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 :
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'
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?
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];
sAMAccountName as Login,
name as UserName
objectClass = 'User' AND
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
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.