SWQL query for single node full monitored status

Does anyone have a SWQL query to share that will pull the green/yellow/red state for each monitored resource of a node?

For example I have a family of network firewall nodes, some nodes have 150 monitored objects, while another node from the same family might only have 12 objects. One node will exclude physical memory because a cache or database keeps it at 100%, while the identical node next to it does not exclude memory because it doesn't have the software feature that keeps memory at 100%. So the query should only select the objects selected in the nodes "list resources" page, there are different monitored resources for each node.

For those that see that I asked the same question twice, you will note the other post is 20 times longer than this one. I ask this short version to get the answer that I seek today, but the longer form of the question is to see if someone that has a better "big picture" idea than the big picture in my other post. This is a simpler question for those that like me have not solved the bigger picture problem.

Thanks!

Find more posts tagged with

swql query

node monitored resource state

network_monitoring

dashboard

SWQL

node state

Accepted answers

bobmarley

Here is a SWQL query that has all of the tables linked together. You can go into each table now and grab the additional parameters you wish such as status LED, volume percent used and add some Case statements to adjust the thresholds you would like. To include / exclude specific items such as memory you can set the thresholds on the specific nodes to alert at different levels or another thing I do for memory is put in a custom property, for example 'Is_SQL_Server' and exclude items based on if the custom property is populated with a Yes.

Adjust the Where statement to fit your specific needs. I put it in there as an example.

If this helps this is a similar article, I believe it is in SQL as its pretty old Report to show all Nodes and their monitored resources - Forum - Network Performance Monitor (NPM) - THWACK (solarwinds.com)

Good luck!

SELECT 
     [Nodes].IPAddress
    ,[Nodes].ObjectSubType
    ,[Nodes].Caption
    ,[Nodes].StatusDescription
    ,[Nodes].Volumes.Caption AS [Volume]
    ,[Nodes].Volumes.StatusDescription AS [VolumeStatusDescription]
    ,[Nodes].Interfaces.Name AS [InterfaceName] 
    ,[Nodes].Interfaces.StatusDescription AS [InterfaceStatusDescription]
    ,[Nodes].Applications.DisplayName AS [ApplicationDisplayName]
    ,[Nodes].Applications.StatusDescription AS [ApplicationStatusDescription]    
    ,[Nodes].Applications.Components.Name AS [ComponentName]
    ,[Nodes].Applications.Components.StatusDescription AS [ComponentStatusDescription]
FROM Orion.Nodes
Where [Nodes].ObjectSubType LIKE '%WMI%' OR [Nodes].ObjectSubType LIKE '%AGENT%'  OR [Nodes].ObjectSubType LIKE '%SNMP%'
Order BY [Nodes].Caption ASC

All comments

adam.beedell

Read this, looked for the long one, posted there.

Something with recursion or with a long list of all the possible stuff you care about.
Might be bridge-able using the child status calculator

bobmarley

Adjust the Where statement to fit your specific needs. I put it in there as an example.

Good luck!

SELECT 
     [Nodes].IPAddress
    ,[Nodes].ObjectSubType
    ,[Nodes].Caption
    ,[Nodes].StatusDescription
    ,[Nodes].Volumes.Caption AS [Volume]
    ,[Nodes].Volumes.StatusDescription AS [VolumeStatusDescription]
    ,[Nodes].Interfaces.Name AS [InterfaceName] 
    ,[Nodes].Interfaces.StatusDescription AS [InterfaceStatusDescription]
    ,[Nodes].Applications.DisplayName AS [ApplicationDisplayName]
    ,[Nodes].Applications.StatusDescription AS [ApplicationStatusDescription]    
    ,[Nodes].Applications.Components.Name AS [ComponentName]
    ,[Nodes].Applications.Components.StatusDescription AS [ComponentStatusDescription]
FROM Orion.Nodes
Where [Nodes].ObjectSubType LIKE '%WMI%' OR [Nodes].ObjectSubType LIKE '%AGENT%'  OR [Nodes].ObjectSubType LIKE '%SNMP%'
Order BY [Nodes].Caption ASC

keoki

This looks like a promising starting point. it times out for me, but I need to work on it a bit to make it run faster. I'll use it as inspiration and see what I can do to tune it for my needs. I'll post back when I get something I like.

keoki

So I ended up running it as two queries, one for the volumes and one for the interfaces. I'm guessing that the chip temperatures and other random things are in their own table, I'll have to go track those down. Because I share my two SolarWinds systems with other departments, I'm using a lot of custom properties for groupings and feature clusters, so my final swql isn't useful to share. But here is the core of the interface list for a node:

SELECT
[Nodes].IPAddress
,[Nodes].Caption AS [Name]
,[Nodes].StatusDescription
,[Nodes].Interfaces.Name AS [InterfaceName]
,[Nodes].Interfaces.StatusDescription AS [InterfaceStatusDescription]
FROM Orion.Nodes
WHERE [Nodes].Caption LIKE '%Some-node-name%'

When I ran the full join, it gave me a interface list for every volume (or was it a volume list for every interface?) so the query inflated the rows of output, so two flat queries worked better. It doesn't have to be one query, I don't know why I worded the original question like that... The goal is to efficiently ship the states over and then trigger updates from alerts or a timer or both. We can use alerts to make api calls into our application, and the application can make a query back into SolarWinds or do something else. I don't know how this one will come together yet, but the plan is to make as few queries as we can while showing accurate state.

keoki

This gives me 66% of what I need. I adjusted the swql to meet my needs of course, but the table columns made all the difference for me.

The remaining 33% I need are the chips, fans, power supplies, and other monitored components.

keoki

Ok, I found time to complete this. 3 queries give me slightly more data than I was getting from Orion Maps, so if my web guys can turn it into something useful we will be in good shape.

Monitored Interface state for one node:

SELECT 
     [Nodes].IPAddress
    ,[Nodes].ObjectSubType
    ,[Nodes].Caption AS [Name]
    ,[Nodes].StatusDescription
    ,[Nodes].Interfaces.Name AS [InterfaceName] 
    ,[Nodes].Interfaces.StatusDescription AS [InterfaceStatusDescription]
FROM Orion.Nodes
WHERE [Nodes].Caption LIKE '%nodename%'


Monitored Volumes:

SELECT 
     [Nodes].IPAddress
    ,[Nodes].ObjectSubType
    ,[Nodes].Caption
    ,[Nodes].StatusDescription
    ,[Nodes].Volumes.Caption AS [Volume]
    ,[Nodes].Volumes.StatusDescription AS [VolumeStatusDescription]
FROM Orion.Nodes
WHERE [Nodes].Caption LIKE '%nodename%'


monitored Fans, temperatures, other sensors:

SELECT ID, Name, Value, Unit, OriginalStatus, FullyQualifiedName, HasHistoricalData, HardwareCategoryID, HardwareUnitDescription, StatusDescription
FROM Orion.HardwareHealth.HardwareItemBase
WHERE FullyQualifiedName LIKE '%nodename%'

The third query needs a join to link it to caption, but I can find the node name in the FullyQualifiedName, and on a by node name selection that works. If I am asked to select the nodes by vendor or other grouping, I'll have to go figure that join out.