Create a Report to include data from other tables.

stuartd

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'

stuartd

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.