Modern Dashboard SWQL from multiple tables

spencercook

I'm a Total noob with sql and swql, so please help.

I'm building a Modern dashboard, based on one I downloaded from Thwackfest.  My standard overview of all networking equipment works fine.  Now I'm trying to start building system specific dashboards.

my current sql is 

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]
FROM Orion.Nodes n
WHERE n.Status = 14

I want to add to this widget to display the above but only for devices which are in a specific solarwinds group.... In this case my group ID is 2, group name is PACS

The following SQL shows number of up nodes in that group for a different widget.

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

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

Once I get this I can then start adding widgets for disk space and hopefully application alerts, but I just need to know how to get information from two tables at the same time.

This image is entire infrastructure overview which works fine.

This is what I'm trying to build for a specific group.  The top widgets do work, Is the bottom ones where I want to actually show more details for the group I'm struggling with.

Thanks 

stuartd

So presumably your 'group' is created via a custom property? If so, you need to do a JOIN to that table and then utilise whatever your CP is.

A suggestion, using your code above is:

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]
FROM Orion.Nodes n

-- add next line
JOIN Orion.NodesCustomProperties AS NCM ON n.NodeId = NCM.NodeID

WHERE n.Status = 14 
-- also add next line
AND NCM.Name_of_Your_CP = 'Whatever'

-- you could use LIKE instead of = just use LIKE '%Key_Word_Here%' which allows for partial searches
-- i.e. instaed of = '123456' you could use LIKE '%2345%'

So, if I wanted to limit this to a group for a specific client then I could do this:

FROM Orion.Nodes AS n
JOIN Orion.NodesCustomProperties AS NCM ON n.NodeId = NCM.NodeID
WHERE NCM.ClientPrefix LIKE '%Name_or_Part_Name_of_Client%'