SWQL : DOWNTIME, UPTIME & TOTAL HOURS DOWN

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

  • 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