SWQL : DOWNTIME, UPTIME & TOTAL HOURS DOWN

mieric56

Hi,

I am apologizing in advance if my post is not on the right channel/thread.

I'm a beginner at SWQL and have no knowledge of SQL. I just want to create a modern dashboard with Downtime, Uptime, and Total hours down. I've been reading articles here in the forum but what I collected were in parts and I don't know how to combine them in a single SWQL query.

Please below SWQL queries:

========================================================================

--Im able to get Uptime and used filter custom properties

SELECT
n.Caption,
n.DetailsUrl,
n.Status,
n.CustomProperties.Manage_Customer_CPE AS CustomProperties_Manage_Customer_CPE,
AddHour(+8,n.LastBoot) as Last_Boot,=
FROM Orion.Nodes n

WHERE (Status LIKE '%2%' AND n.CustomProperties.Manage_Customer_CPE LIKE '%Yes%')

========================================================================

-- I want to extract HoursDown and Downtime from below and add it to the above SWQL.

SELECT

Nodes.Caption AS NodeName, Nodes.DetailsUrl AS [_LinkFor_NodeName],

'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_NodeName],

HOURDIFF(T1.DownTime, GetDate()) AS HoursDown,

T1.DownTime
FROM (SELECT MAX(EventTime) AS DownTime,

NetObjectID,

NetworkNode

FROM Orion.Events

WHERE (EventType = 1)

AND (NetObjectType = 'N')

GROUP BY NetObjectID, NetworkNode)

AS T1 INNER JOIN Orion.Nodes

ON T1.NetworkNode = Nodes.NodeID

WHERE Nodes.Status = '2'

ORDER BY HoursDown ASC,

Nodes.Caption,

T1.DownTime ASC,

Nodes.NodeID

KMSigma.SWI

If the lower is working why not add the upper query information to that one?  The only things it looks like you are missing are the 'Last_Boot' and Custom Property 'Manage_Customer_CPE'.

This runs fine for me, but I don't have that custom property, so I commented it out.

SELECT [N].Caption AS NodeName
	, [N].DetailsUrl AS [_LinkFor_NodeName]
	, '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_NodeName]
	, HOURDIFF([Events].DownTime, GetDate()) AS HoursDown
    , AddHour(8, [N].LastBoot) AS Last_Boot
--   , [N].CustomProperties.Manage_Customer_CPE AS CustomProperties_Manage_Customer_CPE
	, [Events].DownTime
FROM (
	SELECT MAX(EventTime) AS DownTime
		, NetObjectID
		, NetworkNode
	FROM Orion.Events
	WHERE (EventType = 1)
		AND (NetObjectType = 'N')
	GROUP BY NetObjectID
		, NetworkNode
	) AS [Events]
INNER JOIN Orion.Nodes AS [N]
	ON [Events].NetworkNode = [N].NodeID
WHERE [N].STATUS = '2'
ORDER BY HoursDown ASC
	, [N].Caption
	, [Events].DownTime ASC
	, [N].NodeID