This query is an attempt to pull together as much information within NCM as far as its backup jobs go, NCM login status and the age of the last backup. It's not perfect and relies on a few not so ideal ways to join various bits of data but it's sufficient. Long story short, SolarWinds NCM could do a better job at auditing its own happenings.
Regardless, here's what the query shows and also a mockup of what it looks like if you use within a modern dashboard.
- Last Login status, "Login OK" = 1 (Status of UP, else 3 / Warning)
- Last Backup, "Successful" = 1 (Status of UP, else 3 / Warning)
- Pulls data from the NCM Job Audit logs based off IP Address match join
- Backup Age < 48 hrs = 1 (Status of UP, else 3 / Warning)
- Checks the attempted download time of the last backup
- Link to most recent config and its ConfigID
The query:
SELECT n.Caption ,n.IP_Address ,n.Status ,n.DetailsUrl ,nc.NodeID ,CASE WHEN nc.LoginStatus = 'Login OK' THEN 1 ELSE 3 END AS [Last Login] ,CASE WHEN j.Result = 'Successful' THEN 1 ELSE 3 END AS [Last Backup] ,CASE WHEN HOURDIFF(TOLOCAL(a.AttemptedDownloadTime), GETDATE()) < 48 THEN 1 ELSE 3 END AS [Backup Age] ,a.AttemptedDownloadTime ,CASE WHEN a.ConfigID IS NOT NULL THEN CONCAT('/Orion/NCM/ConfigDetails.aspx?configID={', a.ConfigID, '}') ELSE NULL END AS [Most Recent Config] ,'Config' AS [Config] FROM NCM.Nodes AS nc INNER JOIN Orion.Nodes AS n ON n.NodeID = nc.CoreNodeID LEFT JOIN ( SELECT ca.Username AS [JobName] ,ca.ModuleName AS [Function] ,ca.Action ,ca.Type AS [Result] ,ca.Details AS [ResultDetails] ,CASE WHEN ca.Type = 'Successful' AND ca.Details LIKE 'Config downloaded from %' THEN REPLACE(ca.Details, 'Config downloaded from ', '') WHEN ca.Type = 'Failed' AND ca.Details LIKE 'Connection Refused by %' THEN REPLACE(ca.Details, 'Connection Refused by ', '') ELSE NULL END AS [IPAttempt] ,MAX(ca.DateTime) AS [DateTime] FROM Cirrus.Audit AS ca WHERE ca.ModuleName = 'JobsAudit' AND HOURDIFF(TOLOCAL(ca.DateTime), GETDATE()) < 48 GROUP BY ca.Username ,ca.ModuleName ,ca.Action ,ca.Type ,ca.Details ) AS j ON j.IPAttempt = n.IP_Address LEFT JOIN ( SELECT nc.NodeID ,ca2.ConfigID ,ca2.AttemptedDownloadTime FROM NCM.Nodes AS nc INNER JOIN ( SELECT c.NodeID ,MAX(c.AttemptedDownloadTime) AS [AttemptedDownloadTime] FROM NCM.ConfigArchive AS c GROUP BY c.NodeID ) AS ca1 ON ca1.NodeID = nc.NodeID LEFT JOIN NCM.ConfigArchive AS ca2 ON ca2.NodeID = nc.NodeID AND ca2.AttemptedDownloadTime = ca1.AttemptedDownloadTime ) AS a ON a.NodeID = nc.NodeID