I am trying to create a SWQL query which shows the Server availability and downtime date, start and end time and error message why it was down

Question

I am trying to create a SWQL query which shows the Server availability and downtime date, start and end time and error message why it was down.

I have created below query to get 30 days but I need downtime date, start time and downtime end time in the report as well.

Select N.Caption, N.IP_Address, N.NodeID, Avg(RT.Availability) As AvgAvailability, RT.ObservationTimestamp, CP.<Custom Property Name>, E.Message

FROM Orion.Nodes as N

Inner Join Orion.ResponseTime as RT ON N.NodeID = RT.NodeID

Left Join Orion.NodesCustomProperties as CP ON N.NodeID = CP.NodeID

Inner Join Orion.Events as E ON N.NodeID = E.NetObjectID

Where N.nodeID = 'XXXX'

AND RT.ObservationTimestamp >= DATETRUNC('Month',(ADDMONTH(-1, GETUTCDATE())))

AND RT.ObservationTimestamp < DATETRUNC('Month', GETUTCDATE())

GROUP BY N.Caption, N.IP_Address, Availability, N.NodeID, RT.ObservationTimestamp, CP. .<Custom Property Name>, E.Message

stevenstadel · Answer

Try using the event table to get the start and end dates, this should get you started. SELECT
 n.caption AS [Device], -- Device name
 '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device], -- Status icon
 n.DetailsUrl AS [_linkfor_Device], -- Link to node details
 ISNULL(
 TOSTRING(t2.[Down Event]),
 CONCAT('Greater than ', (SELECT CurrentValue FROM Orion.Settings WHERE settingid='SWNetPerfMon-Settings-Retain Events'), ' days ago')
 ) AS [Down Event], -- Down event timestamp or message if older than retention period
 ISNULL(TOSTRING(t2.[Up Event]), 'Still Down') AS [Up Event], -- Up event timestamp or 'Still Down'
 ISNULL(
 MINUTEDIFF(t2.[Down Event], ISNULL(t2.[Up Event], GETDATE())),
 99999
 ) AS Minutes -- Minutes between down and up events, or 99999 if unknown
FROM
 orion.nodes n
LEFT JOIN (
 SELECT
 StartTime.Nodes.NodeID,
 TOLOCAL(StartTime.EventTime) AS [Down Event],
 (
 SELECT TOP 1
 TOLOCAL(EventTime) AS [EventTime]
 FROM Orion.Events AS [EndTime]
 WHERE
 EndTime.EventTime >= StartTime.EventTime
 AND EndTime.EventType = 5
 AND EndTime.NetObjectID = StartTime.NetObjectID
 AND EventTime IS NOT NULL
 ORDER BY EndTime.EventTime
 ) AS [Up Event]
 FROM Orion.Events StartTime
 WHERE StartTime.EventType = 1
) t2 ON n.NodeID = t2.nodeid
WHERE
 (n.status = 2 OR t2.nodeid IS NOT NULL)
ORDER BY
 t2.[Down Event] DESC

stevenstadel · Answer

For sure, this adds the event message that is related to the event that starts the clock in the Events table. SELECT
 n.caption AS [Device], -- Device name
 '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device], -- Status icon
 n.DetailsUrl AS [_linkfor_Device], -- Link to node details
 ISNULL(
 TOSTRING(t2.[Down Event]),
 CONCAT('Greater than ', (SELECT CurrentValue FROM Orion.Settings WHERE settingid='SWNetPerfMon-Settings-Retain Events'), ' days ago')
 ) AS [Down Event], -- Down event timestamp or message if older than retention period
 ISNULL(TOSTRING(t2.[Up Event]), 'Still Down') AS [Up Event], -- Up event timestamp or 'Still Down'
 ISNULL(
 MINUTEDIFF(t2.[Down Event], ISNULL(t2.[Up Event], GETDATE())),
 99999
 ) AS Minutes, -- Minutes between down and up events, or 99999 if unknown
 ISNULL(t2.[Message], 'No Message Available') AS [Down Event Message] -- Down event message
FROM
 orion.nodes n
LEFT JOIN (
 SELECT
 StartTime.Nodes.NodeID,
 TOLOCAL(StartTime.EventTime) AS [Down Event],
 (
 SELECT TOP 1
 TOLOCAL(EventTime) AS [EventTime]
 FROM Orion.Events AS [EndTime]
 WHERE
 EndTime.EventTime >= StartTime.EventTime
 AND EndTime.EventType = 5
 AND EndTime.NetObjectID = StartTime.NetObjectID
 AND EventTime IS NOT NULL
 ORDER BY EndTime.EventTime
 ) AS [Up Event],
 StartTime.Message AS [Message] -- Include message for down event
 FROM Orion.Events StartTime
 WHERE StartTime.EventType = 1
) t2 ON n.NodeID = t2.NodeID
WHERE
 (n.status = 2 OR t2.NodeID IS NOT NULL)
ORDER BY
 t2.[Down Event] DESC