There were two articles that I've read recently that helped me create a solution to a question that my boss want answered.
Basically, she wants our team, the Monitoring Support team, to be able to offer a SW page where any person can check & search for which types of data are being collected by our NPM and SAM modules from each machine/node.
One article is from adatole where he posted a custom SQL query that exactly does this. His article can be found here -> 4 Inevitable Questions When Joining a Monitoring Group, Pt.3 - Packet Pushers -
I used the custom SQL query mentioned in the article and have put it into a "Custom Table" custom resource, selecting Advanced SQL.
We have around 2,500 nodes and we have around 100,000 component monitors. These are distributed in 14 polling engines.
The query is taking a lot of time as it attempts to load a lot of stuff.
Then I came across another article, this time from antonis.athanasiou about leveraging the "Custom Query" custom resource in using its pagination and search features. His article can be found here -> SolarWinds Orion Custom Query Resource - A Hidden Gem!
The problem I then encountered is that the Custom Query resource does not accept SQL. It only accepts SWQL.
I took the time & converted the SQL query from adatole's article to SWQL. The SWQL version of the query is then used inside Custom Query resource, all the while leveraging its 'Enable Search' option.
** Notes **
- The "SELECT TOP 2500" part was what I've decided to use in my environment. You can change this to what you want or what works for you.
- Since the 'Enable Search' option relies on the ${SEARCH_STRING} macro, I've utilized it in the final WHERE clause so that the Search box of the Custom Query resource can look things up either for a node's node name, DNS name, Sys name, IP Address, vendor or machine type.
- Initially when you've finished configuring this resource, it will load empty. The only time that it will spit out data is after you've used the search box to look up either a node's node name, DNS name, Sys name, IP Address, vendor or machine type.
Here's the result of the conversion:
SELECT TOP 2500
nodes.StatusLed AS [icmpStatus],
nodes.caption AS [NodeName],
nodes.IP_Address,
s1.elementtype AS [ElementType],
s1.element AS [Element],
s1.LED AS [ElementStatus],
s1.Description AS [ElementDescr],
s1.capacity AS [ElementCapacity],
s1.threshold_value AS [threshold_for],
s1.warn AS [warningLevel],
s1.crit AS [criticalLevel]
FROM
orion.nodes AS nodes
LEFT JOIN
(
SELECT
'01' as elementorder,
'CPU' as elementtype,
c1.NodeID,
'Up.gif' as LED,
'CPU Count:' as element,
TOSTRING(COUNT(c1.CPUIndex)) as description,
'' as capacity,
'CPU Utilization' as threshold_value,
TOSTRING(t1.Level1Value) as warn,
TOSTRING(t1.Level2Value) as crit
FROM
(
SELECT DISTINCT CPUMultiLoad.NodeID, CPUMultiLoad.CPUIndex from Orion.CPUMultiLoad
) AS c1
join Orion.CpuLoadThreshold t1 on c1.nodeID = t1.InstanceId
GROUP BY
c1.NodeID, t1.Level1Value, t1.Level2Value
-- 01 conversion from sql to swql = successful
UNION ALL (
SELECT
'02' as elementorder,
'RAM' as elementtype,
nodes.NodeID,
'Up.gif' as LED,
'Total RAM' as element,
'' as description,
TOSTRING(ROUND(((nodes.TotalMemory/1048576)/1024),2)) + ' Gigabytes' as capacity,
'RAM Utilization' as threshold_value,
TOSTRING(NodesPercentMemoryUsedThreshold.Level1Value) as warn,
TOSTRING(NodesPercentMemoryUsedThreshold.Level2Value) as crit
FROM Orion.nodes AS Nodes
JOIN Orion.PercentMemoryUsedThreshold AS NodesPercentMemoryUsedThreshold on nodes.nodeid = NodesPercentMemoryUsedThreshold.InstanceId
-- 02 conversion from sql to swql = successful
)
UNION ALL (
SELECT
'95' as elementorder,
'HW' as elementtype,
APM_HardwareAlertData.nodeid,
APM_HardwareAlertData.StatusLED as LED,
APM_HardwareAlertData.Description as element,
APM_HardwareAlertData.CategoriesWithStatus as description,
'' as capacity,
'' as threshold_value,
'' as warn,
'' as crit
FROM Orion.HardwareHealth.HardwareInfo as APM_HardwareAlertData
-- 95 conversion from sql to swql = successful
)
UNION ALL (
SELECT
'03' as elementorder,
'NIC' as elementtype,
interfaces.nodeid as NodeID,
interfaces.statusled as LED,
interfaces.InterfaceName + ' @ifIndex ' + TOSTRING(interfaces.InterfaceIndex) as element,
interfaces.InterfaceTypeDescription as Description,
TOSTRING(interfaces.InterfaceSpeed) as capacity,
'bandwidth in/out' as threshold_value,
TOSTRING(i1.Level1Value)+'/'+TOSTRING(i2.level1value) as warn,
TOSTRING(i1.Level2Value)+'/'+TOSTRING(i2.level2value) as crit
FROM Orion.NPM.Interfaces as interfaces
JOIN (
SELECT InterfacesThresholds.instanceid, InterfacesThresholds.level1value , InterfacesThresholds.level2value
FROM Orion.NPM.InPercentUtilizationThreshold AS InterfacesThresholds
WHERE InterfacesThresholds.name = 'NPM.Interfaces.Stats.InPercentUtilization'
) AS i1 on interfaces.interfaceid = i1.InstanceId
JOIN (
SELECT InterfacesThresholds.instanceid, InterfacesThresholds.Level1Value, InterfacesThresholds.level2value
FROM Orion.NPM.OutPercentUtilizationThreshold AS InterfacesThresholds
WHERE InterfacesThresholds.name = 'NPM.Interfaces.Stats.OutPercentUtilization'
) AS i2 on interfaces.interfaceid = i2.InstanceId
-- 03 conversion from sql to swql = successful
)
UNION ALL (
SELECT
'04' as elementorder,
'DISK' as elementtype,
volumes.nodeid as NodeID,
volumes.statusled as LED,
volumes.caption as element,
volumes.VolumeType as description,
TOSTRING(ROUND(((volumes.VolumeSize/1048576)/1024),2)) + ' Gigabytes' as capacity,
'' as threshold_value,
'' as warn,
'' as crit
FROM Orion.volumes AS volumes
-- 04 conversion from sql to swql = successful
)
UNION ALL (
SELECT
'05' AS elementorder,
'APP component' AS elementtype,
cs.NodeID AS NodeID,
si.ShortDescription + '.gif' AS LED,
cs.ComponentName AS element,
c.Application.name AS description,
'' AS capacity,
'CPU ; PhyMem ; VirtMem ; Stat ; RespTime' as threshold_value,
ISNULL(TOSTRING(cat.ThresholdCPUWarning),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdPhysicalMemoryWarning),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdVirtualMemoryWarning),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdStatisticWarning),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdResponseTimeWarning),'no_data') + ' ; ' AS warn,
ISNULL(TOSTRING(cat.ThresholdCPUCritical),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdPhysicalMemoryCritical),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdVirtualMemoryCritical),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdStatisticCritical),'no_data') + ' ; ' + ISNULL(TOSTRING(cat.ThresholdResponseTimeCritical),'no_data') + ' ; ' AS crit
FROM
Orion.APM.CurrentStatistics AS cs
INNER JOIN Orion.APM.ComponentAlertThresholds AS cat ON cs.ComponentID = cat.ComponentID
INNER JOIN Orion.APM.Component AS c ON cs.ComponentID = c.ComponentID
INNER JOIN Orion.StatusInfo AS si ON cs.ComponentAvailability = si.StatusID
-- 05 conversion from sql to swql = successful
)
UNION ALL (
SELECT
'06' as elementorder,
'POLLER' as elementtype,
CustomPollerAssignmentView.NodeID,
'up.gif' as LED,
CustomPollerAssignmentView.CustomPollerName as element,
CustomPollerAssignmentView.CustomPollerDescription as description,
'' as capacity,
'' as threshold_value,
'' as warn,
'' as crit
FROM (
SELECT
AncestorDetailsUrls, AncestorDisplayNames, AssignmentName, CurrentValue, CustomPollerAssignmentID,
CustomPollerDescription, CustomPollerID, CustomPollerMIB, CustomPollerName, CustomPollerOid,
Description, DetailsUrl, DisplayName, ID, Image, InstanceSiteId, InstanceType,
InterfaceID, NodeID, Status, StatusDescription, StatusIconHint, StatusLED, UnManaged, UnManageFrom, UnManageUntil, Uri
FROM Orion.NPM.CustomPollerAssignmentOnNode
UNION ALL (
SELECT
AncestorDetailsUrls, AncestorDisplayNames, AssignmentName, CurrentValue, CustomPollerAssignmentID,
CustomPollerDescription, CustomPollerID, CustomPollerMIB, CustomPollerName, CustomPollerOid,
Description, DetailsUrl, DisplayName, ID, Image, InstanceSiteId, InstanceType,
InterfaceID, NodeID, Status, StatusDescription, StatusIconHint, StatusLED, UnManaged, UnManageFrom, UnManageUntil, Uri
FROM Orion.NPM.CustomPollerAssignmentOnInterface
)
) AS CustomPollerAssignmentView
-- 06 conversion from sql to swql = successful
)
) AS s1 ON nodes.nodeid = s1.NodeID
WHERE
Nodes.Caption LIKE '%${SEARCH_STRING}%' OR
Nodes.DNS LIKE '%${SEARCH_STRING}%' OR
Nodes.Sysname LIKE '%${SEARCH_STRING}%' OR
Nodes.IP_Address LIKE '%${SEARCH_STRING}%' OR
Nodes.Vendor LIKE '%${SEARCH_STRING}%' OR
Nodes.MachineType LIKE '%${SEARCH_STRING}%'
ORDER BY
nodes.nodeid, s1.elementtype
Here are some screenshots of how it looks like on my end.