4 Replies Latest reply on Aug 11, 2013 11:40 PM by jcheang

    New report generated from existing query with qualifier not working

    Rookie

      Hi LANdesk experts,

       

      I want to create a LANDesk report based on a query that gives me list of all Windows 7 PCs and shows me how many of them belongs to a particular LDAP group (which I use "Qualifier" in Query's). It seems like LANdesk reporting does not able to recognize my query's qualifier, it gives a lot of duplicated items when I import this query into LANdesk reporting tool.

       

      I created a new query, with these conditions:

      Computer - OS - Name = Windows 7 Professional 64 AND

      Computer - OS - NT Info - Service Pack = 1

       

      In the "Select Columns:" windows, I select:

       

      Computer.Display name

      Computer.LDAP Group.Primary Owner.Name, Qualifier as "Computer.LDAP Group.Primary Owner.Name = test_group" (test_group is a valid domain distribution list)

      Computer.Last Software Scan Date

      Computer.Login Name

       

      I run this query and the returned result is ok, it recognizes the qualifier set.

      However, if I right click on the query > select "New report" and run the report. From the report, seems that it does not recognize the qualifier, it gives me multiple lines of the same device name but different LDAP groups which it belongs to.

       

      Strange thing is that, right clicking on the same query and select "View as report", it works well as running the query itself. It does recognize the qualifier.

       

      How do I make a scheduled report that reads the qualifier from a query?

      I am using LDMS 9.5.

       

      Please advise.

      Thanks.

        • 1. Re: New report generated from existing query with qualifier not working
          Simon Ma Employee

          Hi, I've already face this issue.

           

          The problem is in the SQL query used to generate the report : it doesn't take the qualifier in count.

           

          So to remediate, you need to modify manually the SQL query in the report designer (Dataset > Query).

           

          Your SQL query should be like :

           

          SELECT DISTINCT A0.DISPLAYNAME AS ...

                                           Ax.Name AS ...

          [...]

          FROM Computer A0 (nolock)

          LEFT OUTER JOIN

          [...]

          LEFT OUTER JOIN LDAPUserGroups Ax (nolock) ON A0.Computer_Idn = Ax.Computer_Idn AND Ax.Name = 'your test_group'

          WHERE [...]

          ORDER BY [...]

           

           

          Simon

          • 2. Re: New report generated from existing query with qualifier not working
            Rookie

            Hi Simon,

             

            Appreciate your comments. I tested, it works for me if I set one qualifier = test_group.

             

            Can we include multiple qualifier in the reports?

             

            Example, in my query, I set 3 qualifiers,

            Computer.LDAP Group.Primary Owner.Name, Qualifier as "Computer.LDAP Group.Primary Owner.Name = test_group1"

            Computer.LDAP Group.Primary Owner.Name, Qualifier as "Computer.LDAP Group.Primary Owner.Name = test_group2"

            Computer.LDAP Group.Primary Owner.Name, Qualifier as "Computer.LDAP Group.Primary Owner.Name = test_group3"

             

            How should the dataset be defined in reports?

             

            I am not a database person, I can't seem to make it work.

             

            Could you shred some lights please?

             

            Thanks.

            • 3. Re: New report generated from existing query with qualifier not working
              Simon Ma Employee

              Hello,

               

              Yes you can include as many qualifier you want.

               

              The query will look like :

               

              SELECT DISTINCT A0.DISPLAYNAME AS ...

                                               Axx.Name AS ...

                                               Ayy.Name AS ...

                                               Azz.Name AS ...

              [...]

              FROM Computer A0 (nolock)

              LEFT OUTER JOIN

              [...]

              LEFT OUTER JOIN LDAPUserGroups Axx (nolock) ON A0.Computer_Idn = Axx.Computer_Idn AND Axx.Name = 'test_group1'

              LEFT OUTER JOIN LDAPUserGroups Ayy (nolock) ON A0.Computer_Idn = Ayy.Computer_Idn AND Ayy.Name = 'test_group2'

              LEFT OUTER JOIN LDAPUserGroups Azz (nolock) ON A0.Computer_Idn = Azz.Computer_Idn AND Azz.Name = 'test_group3'

              WHERE [...]

              ORDER BY [...]

               

              Simon

              • 4. Re: New report generated from existing query with qualifier not working
                Rookie

                Hi Simon,

                 

                Thank you very much for your help! Now I can make my qualifiers work

                 

                I wonder if we can change the title column name in the report from the default DISPLAYNAME and DISPLAYNAME0 to something else which are more make sense to the reader. Example, "<Test_Group_Name1>" and "<Test_Group_Name2>"

                 

                This is my edited report query with working qualifiers:

                 

                SELECT DISTINCT A0.DISPLAYNAME AS "Device Name", A0.LOGINNAME AS "Login Name", A3.DISPLAYNAME AS "Display Name", A4.DISPLAYNAME AS "Display Name0

                 

                FROM Computer A0 (nolock) LEFT OUTER JOIN Operating_System A1 (nolock) ON A0.Computer_Idn = A1.Computer_Idn LEFT OUTER JOIN OSNT A2 (nolock) ON A0.Computer_Idn = A2.Computer_Idn LEFT OUTER JOIN LDAPUserGroups A3 (nolock) ON A0.Computer_Idn = A3.Computer_Idn AND A3.DISPLAYNAME='test_group1' LEFT OUTER JOIN LDAPUserGroups A4 (nolock) ON A0.Computer_Idn = A4.Computer_Idn AND A4.DISPLAYNAME='test_group2'

                 

                WHERE ((A1.OSTYPE = N'Microsoft Windows 7 Professional Edition, 64-bit' OR A1.OSTYPE = N'Windows 7 Professional 32' OR A1.OSTYPE = N'Windows 7 Professional 64') AND A2.SERVICEPACK = N'1')  

                 

                ORDER BY  A0.DISPLAYNAME

                 

                This is how my report looks like now, I would like to change the third and forth column title.

                 

                title.JPG

                 

                Is that possible?

                 

                Looking forward to hear from you soon.

                Thank you.