Create / export a list of nodes in NCM

Dal

Hi.

I have a list of nodes that fails the nightly backup.

Is it possible to export a list of those nodes (in CSV format for example) in NCM?

Thanks

stuartd

If you mean via a report, then sure, you can do that.

We have a handcrafted report that we run on an ad-hoc basis that looks at last dates on the Run and Startup Config columns. The code you need to use is:

SELECT
S1.Node
,N.StatusDescription
,N.Status
--,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

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
WHERE N.Status NOT IN ('2', '4', '9', '11', '12')

I've left the JOIN to custom properties as you might want to add your own in to aid. The one we use is to distinguish between clients, so I just removed that from the SELECT statement. So, create your report, run it and then you can export that to CSV and manipulate away.

CAVEAT: This works for us, but I will add this. Don't rely on the 'LoginStatus' field as a means to identify what might be wrong. That only points at the last known login issue. We always order the columns and then look at the dates for the Last Running and Last Startup. We usually allow a leeway of x weeks (differs per client) as to what is considered a failure or a transitory issue. YMMV.

Finally, this report relies on the field ca.NodeProperties.LoginStatus to list issues, but I remain unconvinced that it picks up all we need. For example, I just discovered we have an NCM job that is still  'Post Processing'. It appears to have been doing this for a while and subsequently the backup file dates are from last year! But this report doesn't pickup on that as they don't have any errors.