Report to query multiple custom poller values (Cisco WLC)
jaywi11 Mar 24, 2017 12:08 AMI 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
Description | OID | MIB Name | Custom Device Poller Table Assignment ID |
---|---|---|---|
WAP Hostname | 1.3.6.1.4.1.14179.2.2.1.1.3 | AIRESPACE-WIRELESS-MIB:bsnAPName | 90773187-9817-4D32-A9C6-756E0DC5C4CC |
WAP Location | 1.3.6.1.4.1.14179.2.2.1.1.4 | AIRESPACE-WIRELESS-MIB:bsnAPLocation | 98A54BA6-F768-4483-9DE5-C304C12294FB |
WAP Model | 1.3.6.1.4.1.14179.2.2.1.1.16 | AIRESPACE-WIRELESS-MIB:bsnAPModel | 0DE4634A-60F3-41EB-9285-DFCABB5E670B |
WAP Serial Number | 1.3.6.1.4.1.14179.2.2.1.1.17 | AIRESPACE-WIRELESS-MIB:bsnAPSerialNumber | A3A158F4-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.