I built this query out of frustration stemmed from network admins not selecting the correct 'List Resource' items, troubleshooting simple issues stemmed from resources not being selected and the time it takes to open the List Resources function.
This query checks for our mandated List Resource items of:
- Status & Response being ICMP/SNMP based.
- Hardware Health
- Topology
- POE
- VLAN <note, this is not interface VLANS, but VLAN context or the simple standalone VLAN check box>.
- CDP Table. <while note a resource that's in the List Resources menu if Cisco device is configured incorrectly then this will show as not being assigned>.
Here's what the widget looks like
Here's the query
SELECT DISTINCT
CASE
WHEN Response.PollerType = 'N.ResponseTime.ICMP.Native' then 'ICMP'
WHEN Response.PollerType = 'N.ResponseTime.SNMP.Native' then 'SNMP'
Else ' '
End as [Status and Response]
, CASE
WHEN CDP.PollerType like '%CDP%' and CDP.enabled = 'True' then 'Assigned'
Else ' '
End as [CDP Table Polling]
, CASE
WHEN VLAN.PollerType like '%Topology_Vlans%' and VLAN.enabled = 'True' then 'Assigned'
Else ' '
End as [VLAN Polling]
, CASE
WHEN T.PollerType like '%Topology_Layer%' and T.enabled = 'True' then 'Assigned'
Else ' '
End as [Topology]
, CASE
WHEN Poe.PollerType = 'N.Poe.SNMP.Generic' and poe.enabled = 'True' then 'Assigned'
Else ' '
End as [Poe Poller]
, CASE
when n.HardwareHealthInfos.PollingMethod is not null then 'Assigned'
Else ' '
End as [Hardware Health Polling]
FROM Orion.Nodes n
LEFT JOIN (SELECT PollerID, PollerType, NetObjectID, Enabled FROM Orion.Pollers WHERE PollerType LIKE '%CPU%') cpu ON cpu.NetObjectID = n.NodeID
LEFT JOIN (SELECT PollerID, PollerType, NetObjectID, Enabled FROM Orion.Pollers WHERE PollerType LIKE '%Poe%') Poe ON Poe.NetObjectID = n.NodeID
LEFT JOIN (SELECT PollerID, PollerType, NetObjectID, Enabled FROM Orion.Pollers WHERE PollerType Like '%ResponseTime%' and Enabled ='True') Response on Response.NetObjectID = NodeID
Left JOIN (SELECT PollerID, PollerType, NetObjectID, Enabled FROM Orion.Pollers WHERE PollerType LIKE '%Topology_Layer%') T on T.NetObjectID = n.NodeID
Left JOIN (SELECT PollerID, PollerType, NetObjectID, Enabled FROM Orion.Pollers WHERE PollerType LIKE '%cdpCacheTable%') CDP on cdp.NetObjectID = n.NodeID
LEFT JOIN (SELECT PollerID, PollerType, NetObjectID, Enabled FROM Orion.Pollers WHERE PollerType LIKE '%Topology_Vlans%' and enabled ='true') VLAN on vlan.NetObjectID = n.NodeID
where n.nodeid = ${nodeid}