We are currently using the built in downtime report within report writer to view which nodes have gone down and for how long within the past month.
I am trying to create two separate reports that accomplish a similar task but for a specific group. We have a group named "Critical Nodes". I would like (1) a report that shows the downtime events for nodes within this group while managed (unplanned downtime) within the past month (30 days). I would also like (2) a report showing the downtime duration for the nodes within this group while the nodes were unmanaged (planned downtime).
I'm not much of a SQL person but i believe a simple modification to the query should help me at least accomplish task1. The current query for the built in downtime report (listing all nodes, not specifically for the desired group) is as follows:
SELECT * FROM (
SELECT
Nodes.StatusLED,
Nodes.Caption,
Nodes.NodeID,
Nodes.Device_Type,
StartTime.Message,
StartTime.EventTime AS DownEventTime,
(
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
where EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 5
AND EndTimeTable.NetObjectType = 'N'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
ORDER BY EndTimeTable.EventTime
) AS UpEventTime,
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)
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
AND datepart(MONTH, DownEventTime) = datepart(MONTH,GetDate())
ORDER BY Caption ASC, DownEventTime DESC
I would like like to filter it so that the results are selected FROM Containers WHERE NAME ='Critical Nodes'
any help would be appreciated, thanks.
