Below is the SQL query that shows outage in my system. Currently it shows screen shot below. I would like to group by date and total time for that date. we can exclude the caption, messages and uptime. Example of all that im looking for would be:
10/27/2020 total downtime 45 (I just picked a random number for this example)
10/26/2020 total downtime 27
ETC..
Is this something that can be down? if so can some update the SQL query below
SELECT
StartTime.EventTime,
(SELECT TOP 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime
AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime) AS UpEventTime,
Nodes.Caption, StartTime.Message, DATEDIFF(Mi, StartTime.EventTime,(SELECT TOP 1 EventTime FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1)
ORDER BY eventtime desc
