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 UserName LIKE '%CLIENT_NAME%'
--AND DateTime < ADDDATE('DAY', -7, GETUTCDATE())
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:

    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

    Which gives me an output like:

    Perfect for my/our needs.

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