topic LDAP Access - Member Of in QlikView

LDAP Access - Member Of

Wed, 14 Apr 2010 10:11:34 GMT

Hi Folks

I have figured out how to access LDAP, however, I am having trouble getting hold of the MemberOf values, the following query

ldap_Groups:

SQL
SELECT memberOf
FROM '$(vLDAP)' WHERE objectClass='user' AND SAMAccountName='$(vUserName)';

Returns an error as shown below (always a favourite of mine when an error message states SQL Error: No Error, provides so much information!), I am suspecting that I might need to do something special with this as the contents of memberOf field is quite large.

Anybody have any clues as to what I would need to do????

Any help greatly appreciated.

LDAP Access - Member Of

TK_ — Wed, 14 Apr 2010 11:40:21 GMT

Hi Nigel

Your "problem" is that memberOf returns an array and not a string. You have to create a loop in order to read the array.

I found some information here that might be useful:

http://www.rlmueller.net/ADOSearchTips.htm

Regards

Terje Knappen

LDAP Access - Member Of

Thu, 15 Apr 2010 09:24:32 GMT

Hi Terje

Thanks for responding on that, I had a clue that this might be causing the problem, but whilst you have helped to confirm that I'm still a bit unlcear on how I overcome the problem inside the load script. Do you have any idea how to introduce a loop into the load script that will work for this (the link helped to identify some issues, but didn't indicate how to create the loop machanism).

My alternative is to change the query to bring back members of specific groups, which of course would mean creating multiple queries (one for each group I am interested in).

Thanks for your help,

LDAP Access - Member Of

TK_ — Thu, 15 Apr 2010 10:15:25 GMT

I had a situation where I needed to get the memberOf values, but (because of some other factors) I ended up with making an external vbs-script. The script read the values and created a csv-file which I then loaded into section access. I did this at my old job and I don't have access to the script anymore, otherwise I would have sent it to you. It shouldn't be to difficult to create this kind of vbs-script though, there are some nice tips on the url I posted earlier.

regards

Terje Knappen

LDAP Access - Member Of

rwunderlich — Sat, 17 Apr 2010 01:44:26 GMT

I also get the member values using an external tool. I use MSLogparser. The Qlikview Cookbook contains an sample named "Loading Group membership information from Active Directory -- requires Microsoft LogParser" that provides an example.

-Rob

LDAP Access - Member Of

rwunderlich — Sun, 18 Apr 2010 01:16:51 GMT

I updated my ActiveDirectoryLoad sample to get the group members using a macro function. Seems to work pretty well and doesn't require a preprocessor like my MsLogparser solution.

The macro function requires System Access. Change the RootDse variable on the script "Setup" tab to point to your domain.

Let me know if it works for you (or anyone who can test it) and I'll include it in the next Cookbook.

-Rob

LDAP Access - Member Of

Mon, 19 Apr 2010 08:15:09 GMT

Hello Rob

Thanks very much for getting involved in this thread, I've tried your solution and whilst it does refresh all of the data and brings back sensible (correct) user names and group names, for some reason none of the groups are connected to users (and of course vice-versa). I saved a copy with data so you can see what I mean, I've looked through the code and I can't find anything that might have caused this, wonder if you might be able to shed some light.

Cheers,

LDAP Access - Member Of

rwunderlich — Mon, 19 Apr 2010 13:55:27 GMT

Nigel,

According to the table viewer, field UserDN in table GroupMembers has 0 values. So something is going wrong with the group execution.

In the macro module, can you plug one of your UserDN values into the TestIt Sub and run that sub with the Test button?

You've granted System Access to the qvw?

-Rob

LDAP Access - Member Of

Mon, 19 Apr 2010 14:05:01 GMT

Hi Rob

You've granted System Access to the qvw?

In short, No!, but there's a good reason behind it (I wasn't just ignoring your instructions honestly).

We have system access allowed on all our QVWs so I simply assumed that this would be taken care of (we do this by adjusting the OverrideSecurityModule setting in the registry), the problem is that I have just been given a new machine by IT and they have obviously missed this part of the machine deployment process.

Sorted it now and it's working great, really appreciate your help.

Kind regards,

LDAP Access - Member Of

schivet — Wed, 15 Sep 2010 12:33:28 GMT

Hi Rob,

First of all, thank you for this very good application!

I've granted System Access to the qvw and can updated the application as expected on local but I cannot succeed from the Publisher. I don't find what to setup to get it running? Do you have any idea?

Thanks

Best regards

Stéphane Chivet

LDAP Access - Member Of

Wed, 15 Sep 2010 16:06:05 GMT

Could you plz share the registry key.

I have tried to find it in my registry but its not there so i guess you need to add it somewhere. Would be nice if you put the complete path to the key and value so i can try to do it on our server.

Thanks
/Magnus

LDAP Access - Member Of

rwunderlich — Thu, 16 Sep 2010 09:23:32 GMT


Stephane Chivet wrote:I've granted System Access to the qvw and can updated the application as expected on local but I cannot succeed from the Publisher. I don't find what to setup to get it running? Do you have any idea?

In the QVS settings, Security tab, make sure you check both

"Allow macro execution on server"
"Allow unsafe macro execution on server"

-Rob

LDAP Access - Member Of

schivet — Mon, 20 Sep 2010 12:33:27 GMT

Hello Rob,

Both:

"Allow macro execution on server"
"Allow unsafe macro execution on server"

are checked but still not working. I will investigate more.
Thanks

LDAP Access - Member Of

rwunderlich — Tue, 28 Sep 2010 07:30:45 GMT

Does the userid running Publisher have read access to AD?

Are you getting an error message or just empty / incomplete results?

-Rob

LDAP Access - Member Of

schivet — Tue, 28 Sep 2010 08:16:57 GMT

Hello Rob,

The Userid has admin right so it should not be a problem.

When I run the script from the server with QlikView dev I have this error message:

but if I clic on OK, the script continues and I get the expected result: Group + Users.

If I do the same on another LDAP I don't have any problem. I guess it's something wrong with the name but I can't find what!

If I run the application with the publisher, I got this error message:

9/28/2010 9:00:46 AM: 0035 UserTable:
9/28/2010 9:00:46 AM: 0036 LOAD
9/28/2010 9:00:46 AM: 0037 name as UserName,
9/28/2010 9:00:46 AM: 0038 distinguishedName as UserDN
9/28/2010 9:00:46 AM: 0039
..............................
9/28/2010 9:00:46 AM: 0050 SQL select
9/28/2010 9:00:46 AM: 0051 name, distinguishedName
9/28/2010 9:00:46 AM: 0052
9/28/2010 9:00:46 AM: 0053 FROM 'LDAP://EMEA.xxxx.xxxx WHERE objectCategory='person'
9/28/2010 9:00:46 AM: 0054 AND name>''
9/28/2010 9:00:47 AM: Error: Field not found - <name>
9/28/2010 9:00:47 AM: General Script Error
9/28/2010 9:00:47 AM: Execution Failed
9/28/2010 9:00:47 AM: Execution finished.

If I run the same script on the other LDAP, I don't get any message but I don't get the links between the groups and the users.

Best regards

Stéphane

LDAP Access - Member Of

rwunderlich — Wed, 29 Sep 2010 07:07:12 GMT

First I would confirm that the LDAP server in question actualy has a field called "name". You can use an LDAP browser like the one from Softerra at http://www.softerra.com/download.htm.

If you are doing multiple servers in a script, make sure you execute a DISCONNECT / CONNECT between each server.

I received a report from another user that he fixed a problem with this code by using the IP address instead of the server name.

What type of directory is EMEA.xxxx.xxxx? Is it Active Directory or something else?

-Rob

LDAP Access - Member Of

Mon, 23 May 2011 20:01:48 GMT

This application works great. But does anyone know how I can retrive only active ou:s? I have a field in in my AD thats called ou and I should not retive users that have an ou=disabled. But i only get an empty row when i tried retiveing my ou, so i changed the macro code for splitting up groups to split up my ou but that did not help.

Has anyone successfylly retrived the ou:s?

Thanks

Daniel

LDAP Access - Member Of

Tue, 09 Aug 2011 12:57:38 GMT

Hi Rob,

I'm using you'r app with the log parser solution, it is a fine app.

But when i make a load of the active directory users and groups, i only get the groups from the domain.

But i just want to get the local server groups in this app. Do you know what i have to do to get the local server data?

Thanx for your help,

Halmar Heijnen

LDAP Access - Member Of

rwunderlich — Tue, 09 Aug 2011 23:43:29 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

-Rob

http://robwunderlich.com

LDAP Access - Member Of

jbeierschmitt — Tue, 18 Oct 2011 23:35:27 GMT

Hi Rob/Stephane,

I'm having the same problem with QlikView SR7 - I changed settings in QEMC, but the macro does not execute when I use the QEMC to run the task. If I reload using the service account it runs fine and the macro works.

What other setting(s) is necessary to schedule this reload in QEMC?