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 **

  1. 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.
  2. 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.
  3. 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.