5 Replies Latest reply on Nov 5, 2013 9:39 AM by Maag

    Report Scope on Custom Report

    Rookie

      Hi

       

      I have made the following custom report:

       

      Select: a.MacAddress, b.PropertyName, b.PropertyValue, c.PropertyName, c.PropertyValue, d.PropertyName, d.PropertyValue

      From: MobileDevice as a, DeviceProperty as b, DeviceProperty as c, DeviceProperty as d

      Where: b.PropertyName='SerialNumber' and c.PropertyName='ModelName' and d.PropertyName='DeviceID'

       

       

      How can I limit it to a device group, the same way as on a built in report.?

      or even better how can I add to the report, which device groups the device is member of?

        • 1. Re: Report Scope on Custom Report
          SupportEmployee

          Can you please tell me what version of Avalanche you are using?

          • 2. Re: Report Scope on Custom Report
            Rookie

            I am using version 5.3.1.50

            • 3. Re: Report Scope on Custom Report
              SupportEmployee

              I am not sure how to do this through the UI or even if it is currently possible. You could open a case to see if someone can help you but below is an example of something I did that was similar. Keep in mind that this was ran from inside Management Studio and NOT the WL reporting utility and it was for a slightly older version so the schema will be a little different, but maybe it will point you in the right direction.

               

              select name, selection_criteria,

              (Select count(*) from device

              join egroup egroup2 ON substring(device.ip_address,0,7) =

              substring(egroup2.selection_criteria, 6,6)

              where status = 'Updated' AND egroup.name = egroup2.name ) as "Total Updated",

              (Select count(*) from device

              join egroup egroup2 ON substring(device.ip_address,0,7) =

              substring(egroup2.selection_criteria, 6,6)

              where status like '%Pending%' AND egroup.name = egroup2.name ) as "Total Pending",

              (Select count(*) from device

              join egroup egroup2 ON substring(device.ip_address,0,7) =

              substring(egroup2.selection_criteria, 6,6)

              where egroup.name = egroup2.name ) as "YOUR_GROUP_NAME",

              Stuff ( (Select ', ' + device.ip_address from device

              join egroup egroup2 ON substring(device.assigned_ip_address,0,7) =

              substring(egroup2.selection_criteria, 6,6)

              where status = 'Updated' AND egroup.name = egroup2.name FOR XML PATH ('') ),1 ,2 , '' ) as "Updated List",

              Stuff ( (Select ', ' + device.ip_address from device

              join egroup egroup2 ON substring(device.assigned_ip_address,0,7) =

              substring(egroup2.selection_criteria, 6,6)

              where status like '%Pending%' AND egroup.name = egroup2.name FOR XML PATH ('') ),1 ,2 , '' ) as "Pending List"

              from egroup

              where egroup.type = 'SUBSITE' AND egroup.name = ‘YOUR_GROUP_NAME’

               


              • 4. Re: Report Scope on Custom Report
                Rookie

                Thanks for the answer.

                 

                But if I run the script, I get the following error:

                 

                ERROR:  syntax error at or near "XML"

                LINE 33: ...us = 'Updated' AND egroup.name = egroup2.name FOR XML PATH (...

                                                                                                                  ^

                  ********** Error **********

                 

                ERROR: syntax error at or near "XML"

                SQL state: 42601

                 

                I can see that you did run it from "Management Studio" so I guess that's MS SQL, I am running on PostgreSQL.

                And if I look on the table egroup there is no colume called selection_criteria and there is no table called egroup2, so I guess there is a lot that need to be changed and I am not that good at SQL.

                • 5. Re: Report Scope on Custom Report
                  SupportEmployee

                  I think the biggest difference is the db schema. This needs to be modified to work with the Avalanche53 schema:)