I have a report that runs at the first of every month to give me an outage report for the previous month. This report is currently working and gives me each locations name, event down time, event up time, and then an outage duration in minutes.
Currently I'm trying to get this report to give me a summary of all of the outages by means of summing the Outage in minutes column. All I want to see (or rather my management) is a total number in minutes of down time last month. Unfortunately I've had very little luck in doing so. When I try to sum OutageDurationInMinutes it gives me an error
"SQL Error:
SELECT top 1000
Nodes.Caption,
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,
DATEDIFF(Minute, 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
(DATEPART(month, EventTime) = (DATEPART(month, getdate())) -1 ) AND
NOT (Nodes.Caption LIKE '%Test%') AND
(StartTime.EventType = 1) AND
(
(Nodes.MachineType LIKE '%1720%') OR
(Nodes.MachineType LIKE '%1760%') OR
(Nodes.MachineType LIKE '%1841%') OR
(Nodes.MachineType LIKE '%1861%') OR
(Nodes.MachineType = 'Cisco') OR
(Nodes.MachineType LIKE '%2811%') OR
(Nodes.MachineType LIKE '%Cisco ASA 5505%') OR
(Nodes.MachineType LIKE '%Cisco 871%')
)
ORDER BY 1 ASC, 2 ASC, 3 ASC