51 Replies Latest reply: Mar 27, 2014 4:30 AM by Joseph Ibrahim RSS

LDAP Access - Member Of

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
    Terje Knappen

    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

      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
        Terje Knappen

         

        Hi

        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
          Rob Wunderlich

          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
    Rob Wunderlich

    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

      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
        Rob Wunderlich

        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

          Hi Rob

          [Quote]You've granted System Access to the qvw?[/Quote]

          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
            SebulbaSe

            Hi

            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
          Stephane Chivet

          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
            Rob Wunderlich

             


            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
              Stephane Chivet

              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
                Rob Wunderlich

                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
                  Stephane Chivet

                  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
                    Rob Wunderlich

                    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
                      Daniel Viklinder

                      Hi

                      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
                    Jim Beierschmitt

                    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?

                    • Re: LDAP Access - Member Of
                      Jerry Somsen

                      Has anyone solved the issue of running this in Publisher?  I run this AD app on the publisher server, signed in as the QV Server Account, and run it manually and it runs fine.   I run it through the QEMC and It runs but the AD users and groups are blank.  I have verified all the settings in the previous threads of this forum.  

                       

                      Any help would be great. 

        • Re: LDAP Access - Member Of
          sherbahadur

          Hi Rob Wunderlich,

           

          I need your help , Please help me.

           

          On server when I login in access from IE(plugins) Point macro is working well.

          but when I am logging externally(local machine) on access point the macro is not working.

          Please help me.

           

          Thanks in Advance

          • Re: LDAP Access - Member Of
            Rob Wunderlich

            What is the content of your macro that is not working?

             

            -Rob

            • Re: LDAP Access - Member Of
              sherbahadur

              Thanks for reply Rob

               

              Content of macro is

              I am creating a automatic ms word document report , in that i am taking a template named as "standard template.docx" and after that I am inserting the Bitmaps and charts into it.

              I am able to generate automatic word doc report on server

              but when I am logging in access point  and firing the macro  the report is not generated in ms word document

              now I fixed the issue of macro and macro is working but it is not generating the word report.

              The error is coming

               

              QlikOcx

              macro parsed failed Functionality was lost

              Export_To_Microsoft_word

              Error " the file could not be found

              ("C:\....\standard template.docx")

               

               

              Please help me

               

              Thanks

    • LDAP Access - Member Of
      Halmar Heijnen

      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
    Qliker Max

    Hi Rob,

     

    Hope you can help me, I have successfully downloaded my users and groups from our AD.

    I also need the Description of Groups. I tried adding description in the load script but seems not a valid field.

     

    Thanks.

     

    ~skip~

  • LDAP Access - Member Of
    Qliker Max

    Hi Rob,

     

    Sorry for the confusion, I just realized I only added the "description" in the load and not in sql statement.

    I was able to get the field properly. 

     

    This is very helpful.   Thank you and have a Happy New Year.

     

    ~skip~

    • LDAP Access - Member Of
      Qliker Max

      Hi Rob,

       

      Skip again, I tried the adding the same field ("description") in the UsersTable but nothing is being pulled out from AD.

      Am I using the right field for Users Description?

       

      Do you happen to have list of available fields I can select from the AD? I tried * just to get the available fields, but seems not working.

       

      Thank you again for any help you can extend to me.

       

      Happy Holidays.

       

      ~skip~