NCM Backup Job Config Status

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