Display objects from an Orion group on Modern Dashboard

Hi all, Hope someone can help. I have no idea about SQL/WSQL. I've copied and altered as best I can to get the info needed.

I had started work a couple of years ago on modern dashboards. I've got a basic high level overview of all devices (up/down/critical etc) which is fine for now.

I then started to build dashboards for each system. So all devices in a particular orion group are shown, so system managers can look before complaining the network is down. This was all working well and I was going to then work with the teams to build/arrange more to their requirements.

However we updated to NPM 2022.4 (i think) the WSQL further down this text stopped working. But only for some widgets in the dashboard.

The overview as per image below all works as required (I haven't developed further yet)

This display was then copied and modified for individual systems. Did work at creation, but after the update I get the errors displayed.
The service status works widget works with this query.

SELECT Count (n.DisplayName) as ContainerMembers
FROM Orion.ContainerMembers n

WHERE n.ContainerID = '2' AND n.Status = '1'

The 4 tables below the status, (critical, up, warning, down) don't work with this query.

SELECT n.Caption AS [Node Name]
, n.Status
, n.StatusDescription AS [Status Description]
, n.DetailsURL AS [Node Details URL]
, n.MachineType AS [Vendor Name]
, n.VendorInfo.Icon AS [System Vendor Icon]
, n.CPULoad AS [CPULoad]
, n.ResponseTime AS [Response Time]
, n.PercentMemoryUsed AS [Memory Usge]
, n.PercentLoss AS [Packet Loss]
, n.DisplayName AS [ContainterMembers]
FROM Orion.Nodes n
JOIN Orion.ContainerMembers ncm ON n.DisplayName=ncm.DisplayName
WHERE n.Status = 1 AND ncm.ContainerID = '2'

However the tables will validate OK, but error on results

Removing the "AND ncm.ContainerID = '2'" makes the table work, but it shows all devices not just those in container 2.

I understand this is something to do with mixing data sources, but if this used to work, surely there's someway to make it work again.

Any help much appreciated, or we might just have to cut our losses and turn off Orion as there's only 3 things we really want from this 2 of those can be easily achieved at zero cost, email alerts for down nodes and bandwidth utilisation.

Find more posts tagged with

Modern Dashboard

wsql

modern widgets

npm

Accepted answers

All comments

sum_giais

Try removing the quotes on the '2', ContainerID is an integer.

SELECT n.Caption AS [Node Name]
, n.Status
, n.StatusDescription AS [Status Description]
, n.DetailsURL AS [Node Details URL]
, n.MachineType AS [Vendor Name]
, n.VendorInfo.Icon AS [System Vendor Icon]
, n.CPULoad AS [CPULoad]
, n.ResponseTime AS [Response Time]
, n.PercentMemoryUsed AS [Memory Usge]
, n.PercentLoss AS [Packet Loss]
, n.DisplayName AS [ContainterMembers]
FROM Orion.Nodes n
JOIN Orion.ContainerMembers ncm ON n.DisplayName=ncm.DisplayName
WHERE n.Status = 1 AND ncm.ContainerID = 2

spencercook

Tried. Still has the same result.

sum_giais

Edited this response...

This definitely seems like a bug, I'd submit a ticket to support because it works perfectly fine in SWQL studio. Tried a number of adjustments to the query, but ultimately the changes worked in SWQL studio but nothing in the Modern Dashboards.

Was able to replicate the same errors of "Subqueries are not supported." and the show results not working with the 1st query.

All 3 of these queries work in SWQL studio however.

1st Query (not working in Modern Dashboards) -- this is really the optimal way of querying it.

SELECT
	 n.Caption AS [Node Name]
	,n.Status
	,n.StatusDescription AS [Status Description]
	,n.DetailsURL AS [Node Details URL]
	,n.MachineType AS [Vendor Name]
	,n.VendorInfo.Icon AS [System Vendor Icon]
	,n.CPULoad AS [CPULoad]
	,n.ResponseTime AS [Response Time]
	,n.PercentMemoryUsed AS [Memory Usage]
	,n.PercentLoss AS [Packet Loss]
FROM Orion.Nodes AS n
INNER JOIN Orion.ContainerMembers AS c ON n.Uri = c.MemberUri
WHERE n.Status = 1 AND c.ContainerID = 2

The next 2 queries should work for you:

2nd Query (working in Modern Dashboards)

SELECT
	 n.Caption AS [Node Name]
	,n.Status
	,n.StatusDescription AS [Status Description]
	,n.DetailsURL AS [Node Details URL]
	,n.MachineType AS [Vendor Name]
	,n.VendorInfo.Icon AS [System Vendor Icon]
	,n.CPULoad AS [CPULoad]
	,n.ResponseTime AS [Response Time]
	,n.PercentMemoryUsed AS [Memory Usage]
	,n.PercentLoss AS [Packet Loss]
FROM Orion.Nodes AS n
INNER JOIN Orion.ContainerMembers AS c ON c.MemberUri = n.Uri AND n.Status = 1 AND c.ContainerID = 2

3rd query (also working in Modern Dashboards)

SELECT
	 n.Caption AS [Node Name]
	,n.Status
	,n.StatusDescription AS [Status Description]
	,n.DetailsURL AS [Node Details URL]
	,n.MachineType AS [Vendor Name]
	,n.VendorInfo.Icon AS [System Vendor Icon]
	,n.CPULoad AS [CPULoad]
	,n.ResponseTime AS [Response Time]
	,n.PercentMemoryUsed AS [Memory Usage]
	,n.PercentLoss AS [Packet Loss]
FROM Orion.Nodes AS n
INNER JOIN ( SELECT s1.MemberUri, s1.ContainerID FROM Orion.ContainerMembers AS s1 WHERE s1.ContainerID = 2 ) AS c ON c.MemberUri = n.Uri
WHERE n.Status = 1