Hi guys,
I'm pretty new at Thwack. I need to get a report for Node downtime, over the last 7 days,
and I need the filter only to show the Contains Node "sol" results and not all the nodes,
in addition, the report need to contains the Scheduled Maintenance work....
The report below in one that I found at the web that match my needs...
#####Please please please can u help to to add the features mentioned above?#####
SELECT * FROM (
SELECT
Nodes.StatusLED,
Nodes.Caption,
Nodes.NodeID,
Nodes.MachineType,
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 outageDurationInMinutes >= '15'
AND datepart(hour, DownEventTime) >= 8
AND datepart(hour, UpEventTime) >= 8
AND datepart(hour, DownEventTime) <= 17
AND datepart(hour, UpEventTime) <= 17
ORDER BY Caption ASC, DownEventTime DESC