Create a Report to include data from other tables.

I have a simple report that will show failed backup jobs. 

In looking at the output, it shows everything but the name of the node that failed. So I need to adapt(add) to my report to pull the node name from NPM. Only I have no clue how to do JOINS, let alone much else in SWQL/SQL.

The code so far is simply:

SELECT TOP 1000 UserName, ModuleName, Type, Action, Details, DateTime
FROM Cirrus.Audit
WHERE Action LIKE '%Download%'
AND Type = 'Failed'

  • I hate leaving questions unanswered, and as I have had no responses I have wasted a fair bit of time finding one. To save someone else the grief of this then here is my completed query code.

    I forget what report I took this from now, but I adapted it to suit my need which was to have a report show me when a device was last backed up by NCM. What I was struggling to do was add in a second table (a JOIN) so that I could also have the device name in the report..

    The code I abused ends up like this:

    ,S1.DetailsUrl AS [_LinkFor_Node]
    ,'/Orion/images/StatusIcons/small-' + ToString(S1.StatusIcon) AS [_IconFor_Node]
    ,S1.DetailsUrl AS [_LinkFor_IP]
    	MAX(ca.DownloadTime) AS LastRunning
    	,ca.NodeProperties.Nodes.Caption AS Node
    	,ca.NodeProperties.Nodes.IPAddress AS IP
    		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.CustomProperties.Area
    	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
    	MAX(ca.DownloadTime) AS LastStartup
    	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

    Which gives me an output like:

    Perfect for my/our needs.

  • Thanks for coming back to share how you went about this!