Query on list of nodes - SWQL requirement

Hi,

I have a requirement to work on, where a user wants to see the list of all the nodes which is grouped by custom properties along with their status. I have below query that shows the count of nodes based on their custom properties and its status:

SELECT n.CustomProperties.Comments,
COUNT (CASE n.Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE n.Status WHEN '2' THEN 1 else NULL end) AS DOWN,
COUNT (CASE n.Status WHEN '3' THEN 1 else NULL end) AS WARNING,
COUNT (CASE n.Status WHEN '14' THEN 1 else NULL end) AS CRITICAL,
COUNT (CASE n.Status WHEN '9' THEN 1 else NULL end) AS UNMANAGED,
CONCAT('/NetPerfMon/Images/Vendors/',N.VendorIcon) AS [_IconFor_Vendor]

FROM Orion.Nodes n
--WHERE n.Vendor like 'Cisco' or n.Vendor like 'Windows'
GROUP BY n.CustomProperties.Comments, N.VendorIcon

The output looks like this in below screenshot.

However, in addition to this, I would like to attach an hyperlink that displays all the nodes that belongs to custom property (Active directory) and its status. So, if i click on Active directory, it should show me all the nodes that belongs to it.

Can anyone help here please?

Thank you in advance.

Find more posts tagged with

orion_sdk

solarwinds

SWQL

swql_studio

solarwinds orion sdk

orion sdk

Accepted answers

All comments

WinterSoldier

SELECT
n.CustomProperties.Primary_Category,
'<a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + TO_STRING(n.NodeID) + '">' + TO_STRING(COUNT(CASE n.Status WHEN '1' THEN 1 ELSE NULL END)) + '</a>' AS UP,
'<a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + TO_STRING(n.NodeID) + '">' + TO_STRING(COUNT(CASE n.Status WHEN '2' THEN 1 ELSE NULL END)) + '</a>' AS DOWN,
'<a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + TO_STRING(n.NodeID) + '">' + TO_STRING(COUNT(CASE n.Status WHEN '3' THEN 1 ELSE NULL END)) + '</a>' AS WARNING,
'<a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + TO_STRING(n.NodeID) + '">' + TO_STRING(COUNT(CASE n.Status WHEN '14' THEN 1 ELSE NULL END)) + '</a>' AS CRITICAL,
'<a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + TO_STRING(n.NodeID) + '">' + TO_STRING(COUNT(CASE n.Status WHEN '9' THEN 1 ELSE NULL END)) + '</a>' AS UNMANAGED,
'<a href="/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:' + TO_STRING(n.NodeID) + '">' + TO_STRING(COUNT(*)) + '</a>' AS Total
FROM Orion.Nodes n
GROUP BY n.CustomProperties.Primary_Category;

Tried this but didn't get the exact output.

sum_giais

There's possibly a way to do what you're trying to achieve if you create a modern dashboard and are on a version >= 2020.2.5 using modern dashboard global filters (https://documentation.solarwinds.com/en/success_center/orionplatform/content/core-fusion-dashboard-global-filters.htm)

You would need to create a modern dashboard with a single table widget with a query for the data you need (like so):

SELECT
     n.Caption
    ,n.DetailsUrl
    ,n.Status
    ,n.Vendor
    ,cp.Comments
FROM Orion.Nodes AS n
INNER JOIN Orion.NodesCustomProperties AS cp ON cp.NodeID = n.NodeID

See documentation for creating a modern dashboard and creating a table widget:

https://documentation.solarwinds.com/en/success_center/orionplatform/content/core-fusion-dashboards.htm

https://documentation.solarwinds.com/en/success_center/orionplatform/content/core-fusion-table-widgets.htm

That modern dashboard would be then linked to with global filters applied in the URL syntax from your view where you have this query.

SELECT 
     cp.Comments
    ,ISNULL(COUNT(CASE n.Status WHEN 1 THEN 1 ELSE NULL END), 0) AS [UP]
    ,CONCAT('https://SolarWindsPlatformServer/apps/platform/dashboard/DASHID', '?filters=0_Orion.Nodes_Status:eq:1', '-0_Orion.NodesCustomProperties_comments:eq:', cp.comments) AS [_LinkFor_UP]
    ,ISNULL(COUNT(CASE n.Status WHEN 2 THEN 1 ELSE NULL END), 0) AS [DOWN]
    ,CONCAT('https://SolarWindsPlatformServer/apps/platform/dashboard/DASHID', '?filters=0_Orion.Nodes_Status:eq:2', '-0_Orion.NodesCustomProperties_comments:eq:', cp.comments) AS [_LinkFor_DOWN]
    ,ISNULL(COUNT(CASE n.Status WHEN 3 THEN 1 ELSE NULL END), 0) AS [WARNING]
    ,CONCAT('https://SolarWindsPlatformServer/apps/platform/dashboard/DASHID', '?filters=0_Orion.Nodes_Status:eq:3', '-0_Orion.NodesCustomProperties_comments:eq:', cp.comments) AS [_LinkFor_WARNING]
    ,ISNULL(COUNT(CASE n.Status WHEN 14 THEN 1 ELSE NULL END), 0) AS [CRITICAL]
    ,CONCAT('https://SolarWindsPlatformServer/apps/platform/dashboard/DASHID', '?filters=0_Orion.Nodes_Status:eq:14', '-0_Orion.NodesCustomProperties_comments:eq:', cp.comments) AS [_LinkFor_CRITICAL]
    ,ISNULL(COUNT(CASE n.Status WHEN 9 THEN 1 ELSE NULL END), 0) AS [UNMANAGED]
    ,CONCAT('https://SolarWindsPlatformServer/apps/platform/dashboard/DASHID', '?filters=0_Orion.Nodes_Status:eq:9', '-0_Orion.NodesCustomProperties_comments:eq:', cp.comments) AS [_LinkFor_UNMANAGED]
    ,CONCAT('/NetPerfMon/Images/Vendors/',n.VendorIcon) AS [_IconFor_Vendor]
FROM Orion.Nodes AS n
INNER JOIN Orion.NodesCustomProperties AS cp ON cp.NodeID = n.NodeID
--WHERE n.Vendor like 'Cisco' or n.Vendor like 'Windows'
GROUP BY cp.comments,n.Vendor

Would need to replace these values in the query above:

SolarWindsPlatformServer with the server name of your SolarWinds Platform web UI
DASHID with the unique integer value of the modern dashboard ID (this can be found at the end of the URL when accessing the modern dashboard you create

Hope that's enough to get you going and works, but if not let us know and maybe this is enough to get the wheels churning for you.

WinterSoldier

Hi @sum_giais

Thank you for sharing this. My main objective is to get the list of all nodes when I click on the Total column of that relevant custom property count (example 4). Please refer to the output below.

Group	Up	Down	Warning	Critical	Unmanaged	Total
Active Directories	4	0	0	0	0	4

I have used below query for the above output.

SELECT n.CustomProperties.Primary_Category AS [Group],
COUNT (CASE n.Status WHEN '1' THEN 1 else NULL end) AS UP,
COUNT (CASE n.Status WHEN '2' THEN 1 else NULL end) AS DOWN,
COUNT (CASE n.Status WHEN '3' THEN 1 else NULL end) AS WARNING,
COUNT (CASE n.Status WHEN '14' THEN 1 else NULL end) AS CRITICAL,
COUNT (CASE n.Status WHEN '9' THEN 1 else NULL end) AS UNMANAGED,
Count(*) as [Total]
--CONCAT('/NetPerfMon/Images/Vendors/',N.VendorIcon) AS [_IconFor_Vendor]

FROM Orion.Nodes n  
WHERE n.CustomProperties.Primary_Category != ' '
GROUP BY n.CustomProperties.Primary_Category
Order By n.CustomProperties.Primary_Category ASC

Only thing which I am looking for is the hyperlink option on the Total counts of each custom properties. If I click on 4 then it should show me the list of all 4 nodes of Active Directory.

Can you help here please?

adam.beedell

There's a few dynamic URL places (perfstack, modern dashboard filters) but that's a tough ask. In theory you would build the URL and then assign that as a link.
Or you make the links available in another way (say with a bunch of pre-built summary dashboards) and produce those links
Or you make these as actual groups and change the query significantly to look for container membership, and then call the group's detailsURL