NCM Report

surnaihai899

Hello All

I am trying to extract a report of all the devices under network stream with backup status , Below is the query i am using

SELECT N.Caption, N.DetailsUrl, N.Status, N.ChildStatus, N.Vendor, NCP.Stream,V.Icon, N.IP_Address, N.IPAddressType,N.MachineType, C.DownloadTime, Stream

FROM NCM.NodeProperties NP

INNER JOIN Orion.Nodes N ON NP.CoreNodeID=N.NodeID

inner join Orion.NodesCustomProperties NCP on N.NodeID = N.NodeID

INNER JOIN Orion.Vendors V ON N.Vendor=V.Name

LEFT JOIN NCM.ConfigArchive C ON NP.NodeID=C.NodeID

WHERE Stream LIKE '%Network'

AND C.ConfigType LIKE '%Running'

Although, i am not getting any error , but there is no output as well. 

Any help is appreciated.

stuartd

Status - as in did it backup or not?

Then try this report: which can also be used in a modern dashboard: NCM Backup Job Config Status 

Alternatively, we have a report I created that shows only failures: NCM Backup Failures and Reason (- All Clients) for this one you would probably need to edit out the custom properties.

Finally, I have also created  a report that shows me nodes and their last backup status:

Code for that is below and, as with the other one, you will almost certainly need to edit out our custom properties.

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
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.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.Nodes.Vendor LIKE '%Juniper%'
	GROUP BY ca.NodeProperties.CoreNodeID
	) AS S2 ON S1.CoreNodeID=S2.CoreNodeID