Backup Status by last backup date - Errors Only

This report expands on (or contracts really) my previously published report, here: https://thwack.solarwinds.com/content-exchange/the-orion-platform/m/reports/3308 - as previously mentioned, we have had to do a join to pull in the node name, and now I've done a JOIN to custom properties to pull in the client prefix, and a JOIN to Orion.Nodes to pull back the status.

This report only shows the nodes that generated an error and what that error is. This quickly allows you to identify the nodes that are up but have issues, especially if you organise by status. It looks like this (with sensitive info removed:

SELECT
S1.Node
,S1.DetailsUrl AS [_LinkFor_Node]
,'/Orion/images/StatusIcons/small-' + ToString(S1.StatusIcon) AS [_IconFor_Node]
,S1.IP
,S1.DetailsUrl AS [_LinkFor_IP]
--,S1.Area
,S1.MachineType
,S1.Version
,S1.Serial
,S1.LastBoot
,S1.LoginStatus
,S1.LastRunning
,S2.LastStartup
,NCP.Country
,N.StatusDescription
FROM(
	SELECT
	MAX(ca.DownloadTime) AS LastRunning
	,ca.NodeProperties.CoreNodeID
	,ca.NodeProperties.Nodes.Caption AS Node
	,ca.NodeProperties.Nodes.StatusIcon
	,ca.NodeProperties.Nodes.DetailsUrl
	,ca.NodeProperties.Nodes.IPAddress AS IP
	,ca.NodeProperties.Nodes.MachineType
	--,ca.NodeProperties.Nodes.
	,CASE
		WHEN ca.NodeProperties.LoginStatus LIKE '%Cannot Log into Device : bad password%' THEN 'Error: Bad Password'
		WHEN ca.NodeProperties.LoginStatus LIKE '%Connection Refused by%' THEN 'Connection Refused'
		ELSE ca.NodeProperties.LoginStatus
	END AS LoginStatus
	,ca.NodeProperties.Nodes.IOSVersion AS Version
	,ca.NodeProperties.Nodes.IOSImage AS Serial
	,ca.NodeProperties.Nodes.LastBoot
    ,ca.NodeID
--	,ca.NodeProperties.Nodes.CustomProperties.Area
	--,ca.NodeProperties.Nodes.CustomProperties.
	FROM NCM.ConfigArchive AS ca
	WHERE ca.ConfigType='Running'
--	AND ca.NodeProperties.Nodes.Vendor LIKE '%Juniper%'
	GROUP BY ca.NodeProperties.CoreNodeID,ca.NodeProperties.LoginStatus,ca.NodeProperties.Nodes.Caption,ca.NodeProperties.Nodes.NodeID,ca.NodeProperties.Nodes.IPAddress,ca.NodeProperties.Nodes.MachineType,ca.NodeProperties.Nodes.IOSVersion,ca.NodeProperties.Nodes.IOSImage,ca.NodeProperties.Nodes.LastBoot,ca.NodeProperties.Nodes.StatusIcon,ca.NodeProperties.Nodes.DetailsUrl
	) AS S1
INNER JOIN(
	SELECT
	MAX(ca.DownloadTime) AS LastStartup
	,ca.NodeProperties.CoreNodeID
	FROM NCM.ConfigArchive AS ca
	WHERE ca.ConfigType='Startup'
AND ca.NodeProperties.LoginStatus NOT LIKE '%Login OK%'
--	AND ca.NodeProperties.Nodes.Vendor LIKE '%Juniper%'
	GROUP BY ca.NodeProperties.CoreNodeID
	) AS S2 ON S1.CoreNodeID=S2.CoreNodeID
INNER JOIN Orion.Nodes AS N ON s1.CoreNodeID=N.NodeID
LEFT OUTER JOIN Orion.NodesCustomProperties AS NCP ON n.NodeID=NCP.NodeID

stuartd