SWQL Query for List

I'm tyring to get a list of all my devices, some of which are NCM licensed and some aren't. The query below has the answer, but won't show the nodes that aren't in NCM. Any input?

End state: I want a list of all devices within SolarWinds and have a link if there is a config file.

SELECT
n.IPAddress
,cn.NodeID
,n.Caption
,ca.Config
FROM Cirrus.Nodes cn
JOIN Orion.Nodes n ON cn.CoreNodeID = n.NodeID
JOIN Cirrus.ConfigArchive ca ON cn.NodeID = ca.NodeID
JOIN (
SELECT ca.NodeID, MAX(ca.DownloadTime) AS MostRecentDownload
FROM Cirrus.ConfigArchive ca
WHERE ca.ConfigType = 'Running'
GROUP BY ca.NodeID
) tbl1 ON ca.NodeID = tbl1.NodeID AND ca.DownloadTime = tbl1.MostRecentDownload

(credit: getting latest archived config )

Find more posts tagged with

SWQL

Modern Dashboards

npm

Dashboards

Accepted answers

All comments

JP_net

Have you tried a Right Join for the nodes table? That would keep rows that are in the nodes table but not in the NCM tables you are joining.

adam.beedell

I'm avoiding rewriting the query at the moment, but are you aware you can use this method?

Manage nodes -> Order by NCM Licenced

Seashore

If you want all nodes, you can't start by querying the table Cirrus.Nodes, start with the table that includes all nodes: Orion.nodes.

Also, you just state "JOIN", not what type of join you want. With only "Join" it will probably be an inner join and that only includes where both tables match. If you also want the results where both tables don't match you want to use a OUTER JOIN, and in my case a RIGHT OUTER JOIN.

Hope below query gives you what you want.

SELECT
    N.Caption
    ,N.IP_Address
    ,CA2.DownloadTime
    ,CA2.Config
FROM Orion.Nodes as N
LEFT OUTER JOIN  Cirrus.Nodes AS CN ON N.NodeID=CN.CoreNodeID
LEFT OUTER JOIN (
    SELECT 
        ca.NodeID
        ,MAX(ca.DownloadTime) AS MostRecentDownload

    FROM Cirrus.ConfigArchive ca
    WHERE ca.ConfigType = 'Running'
    GROUP BY ca.NodeID
    ) AS CA1 on CN.NodeID=CA1.NodeID
LEFT OUTER JOIN Cirrus.ConfigArchive AS CA2 ON CA1.nodeID=CA2.NodeID AND CA1.MostRecentDownload=CA2.DownloadTime
ORDER BY
    N.Caption