6 Replies Latest reply on Sep 22, 2017 12:58 PM by mesverrum

    Report to query multiple custom poller values (Cisco WLC)

    jaywi11@hotmail.com

      I am asking the community to see if someone knows how to get this to work.

       

      I have a SQL query that was put together to allow 4 values from Universal Device Pollers (UDPs) to be put together in one table / report.

      I try to put together 4 queries into one report and the preview of the data table looks fine.

       

      When I get to "ADD TO LAYOUT" , I get the query is not valid

       

      So a little bit of background. My 4 UDPs query the OIDs listed below

      DescriptionOIDMIB NameCustom Device Poller Table Assignment ID
      WAP Hostname1.3.6.1.4.1.14179.2.2.1.1.3AIRESPACE-WIRELESS-MIB:bsnAPName90773187-9817-4D32-A9C6-756E0DC5C4CC
      WAP Location1.3.6.1.4.1.14179.2.2.1.1.4AIRESPACE-WIRELESS-MIB:bsnAPLocation98A54BA6-F768-4483-9DE5-C304C12294FB
      WAP Model1.3.6.1.4.1.14179.2.2.1.1.16AIRESPACE-WIRELESS-MIB:bsnAPModel0DE4634A-60F3-41EB-9285-DFCABB5E670B
      WAP Serial Number1.3.6.1.4.1.14179.2.2.1.1.17AIRESPACE-WIRELESS-MIB:bsnAPSerialNumberA3A158F4-2896-4C2C-A807-5952E0582695

      . The "Custom Device Poller Table Assignment ID" is random IDs given to my UDP data in the custom poller status table. NB for each UDP these no doubt will be different from my data to anyone else that tries to work this out.

      The SQL query.

      I can get the report in a round about way by adding each table separately, but its pretty ugly and I should be able to combine all the values into one table for the basis of this report. The match for each row is the field ROWID.

       

       

      =====================================================================================

      SELECT top 1000 [wap_serial]

      FROM   (

                    select CustomPollerStatus.Status AS wap_serial

                    ,      *

                    FROM [Solarwinds].[dbo].[CustomPollerStatus]

                           ) Subqueryserial

      WHERE CustomPollerAssignmentID = 'A3A158F4-2896-4C2C-A807-5952E0582695'

      order by RowID

      '90773187-9817-4D32-A9C6-756E0DC5C4CC'

      =====================================================================================

      SELECT top 1000 [wap_hostname]

      FROM (

      select CustomPollerStatus.Status AS wap_hostname

      , *

      FROM [Solarwinds].[dbo].[CustomPollerStatus]

      ) SubqueryHostname

      WHERE CustomPollerAssignmentID = '90773187-9817-4D32-A9C6-756E0DC5C4CC'

      =====================================================================================

      SELECT top 1000 [wap_Location]

      FROM (

      select CustomPollerStatus.Status AS wap_Location

      , *

      FROM [Solarwinds].[dbo].[CustomPollerStatus]

      ) SubqueryLocation

      WHERE CustomPollerAssignmentID = '98A54BA6-F768-4483-9DE5-C304C12294FB'

      =====================================================================================

      SELECT top 1000 [wap_model]

      FROM (

      select CustomPollerStatus.Status AS wap_model

      , *

      FROM [Solarwinds].[dbo].[CustomPollerStatus]

      ) SubqueryModel

      WHERE CustomPollerAssignmentID = '0DE4634A-60F3-41EB-9285-DFCABB5E670B'

      =====================================================================================

       

       

      Any help would be greatly appreciated and will probably help out others in the thwack community.

        • Re: Report to query multiple custom poller values (Cisco WLC)
          tony.johnson

          Hi,

          I'm not entirely sure i understand what you are trying to achieve but I'm guessing you have multiple devices with 4  UNDPS assigned to each and you want to create a single report with 1 line for each device?

           

          A possible solution could be to create a new view within the database, and use SELECT * from your_view_name as the report code. Caution when creating the name of the view!

            • Re: Report to query multiple custom poller values (Cisco WLC)
              jaywi11@hotmail.com

              Polling a single device, with 4 unique UDPs.

              The end result should be a table with 4 columns, one for each of the UDPs which will give a Wireless AP asset report.

              should have looked womthing like this:-

              wap_report.jpg

              WAP name was picking up the hostname of the WAP, wap location(self explanatory)), WAP serial number as the serial number of the wireless ap and wap model the model of wireless access point. I could get some of this information from the NCM (serial and model type) but it was missing hostname and location. and as these devices are quite geographically spread, it would be handy to know where they all are. As you can see we have quite afew EOL AIR-LAP1131AG-N-K9's kicking about and they really need to be replaced.

               

              The big problem is all 4 values come from the custom_poller_status field. So if I have say 240 wireless APs, there would be 960 values that need located and tied together. the poller ID helps find  the data for each type from the table, and the rowID seems to be the key for tying all of these values together.

                • Re: Report to query multiple custom poller values (Cisco WLC)
                  mesverrum

                  You just need to join all those separate select statements on rowid's.

                  Here is an example of a SWQL table I built a while ago doing something similar for a stack of custom F5 UNDP pollers before solarwinds did the native integration.  Sorry it is such a complex example but hopefully you can see how to apply the logic to your statements.

                   

                  select s.status as [F5 Node],t4.status as [Port], t2.status as [Pool Name], t3.status as [Enabled], t5.status as [Monitor Status], n.caption as [F5], n.detailsurl as [_linkfor_F5]

                  ,'/orion/images/statusicons/small-'+ n.statusled as [_iconfor_F5],

                  case when t5.status like 'unchecked' then '/orion/images/statusicons/small-unknown.gif' when t3.status !='enabled' then '/orion/images/statusicons/small-down.gif' when t5.status !='up' then '/orion/images/statusicons/small-down.gif' else '/orion/images/statusicons/small-up.gif'

                  end as [_iconfor_F5 Node]

                   

                   

                   

                   

                  from Orion.NPM.CustomPollerStatus s

                  join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID

                  join Orion.F5.Device d on d.nodeid=a.nodeid

                  join orion.nodes n on n.nodeid=d.nodeid

                   

                   

                  join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID

                  from Orion.NPM.CustomPollerStatus s

                  join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID

                  join Orion.F5.Device d on d.nodeid=a.nodeid

                  where a.CustomPollerName like 'F5ltmPoolMemberPoolName'

                  and minutediff(s.datetime,getutcdate())<6

                  and d.FailoverState=4) as t2 on s.rowid=t2.rowid

                   

                   

                  join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID

                  from Orion.NPM.CustomPollerStatus s

                  join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID

                  join Orion.F5.Device d on d.nodeid=a.nodeid

                  where a.CustomPollerName like 'F5ltmPoolMemberSessionStatus'

                  and minutediff(s.datetime,getutcdate())<6

                  and d.FailoverState=4) as t3 on s.rowid=t3.rowid

                   

                   

                  join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID

                  from Orion.NPM.CustomPollerStatus s

                  join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID

                  join Orion.F5.Device d on d.nodeid=a.nodeid

                  where a.CustomPollerName like 'F5ltmPoolMemberPort'

                  and minutediff(s.datetime,getutcdate())<6

                  and d.FailoverState=4) as t4 on s.rowid=t4.rowid

                   

                   

                  join (select s.status, s.CustomPollerAssignmentID, s.DateTime, s.Rate, s.Total, s.RawStatus, s.RowID

                  from Orion.NPM.CustomPollerStatus s

                  join Orion.NPM.CustomPollerAssignment a on s.CustomPollerAssignmentID=a.CustomPollerAssignmentID

                  join Orion.F5.Device d on d.nodeid=a.nodeid

                  where a.CustomPollerName like 'F5ltmPoolMemberMonitorStatus'

                  and minutediff(s.datetime,getutcdate())<6

                  and d.FailoverState=4) as t5 on s.rowid=t5.rowid

                   

                  where a.CustomPollerName like 'F5ltmPoolMemberNodeName'

                  and minutediff(s.datetime,getutcdate())<6