I built this query out of frustration stemmed from server 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/agent based.
- CPU & Memory <note, this is not physical/virtual memory utilization but rather the CPU & Memory poller.
- Asset Inventory
- SCM monitoring <note, I did not distinguish on what type of polling. Selecting any of the SCM options will trigger the assigned indicator>.
- Volumes <note, selecting any volume will trigger the assigned indicator>
- UnDP. <note, not a widespread requirement, there are certain servers that have special functions that require UnDP. This field is selected for the certain servers. If you don't need this then simply delete it, comment it out or don't add it to the widget table>.
Here's what the widget looks like
Here's the SWQL query:
SELECT DISTINCT
CASE
WHEN Response.PollerType = 'N.ResponseTime.ICMP.Native' then 'ICMP'
WHEN Response.PollerType = 'N.ResponseTime.Agent.Native' then 'Agent'
Else ' '
End as [Status and Response]
,CASE
WHEN cpu.PollerType IS NOT NULL THEN 'Assigned'
ELSE ' '
END AS [CPU and Memory Poller]
,CASE
WHEN asset.PollerType IS NOT NULL THEN 'Assigned'
ELSE ' '
END AS [Asset Poller]
,CASE
WHEN n.CustomPollerAssignmentOnNode.AssignmentName IS NOT NULL THEN 'Assigned'
ELSE ' '
END AS [UnDP Poller]
,CASE
WHEN n.SCMNode.Enabled IS NOT NULL THEN 'Assigned'
ELSE ' '
END AS [SCM Poller]
,CASE
WHEN n.Volumes.Type IS NOT NULL THEN 'Assigned'
ELSE ' '
END AS [Volumes Poller]
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 '%asset%') asset ON asset.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
where n.nodeid = ${nodeid}