Morning/Afternoon/Evening all,
I'm trying to get a table created that shows all my printers that are down, and how long they have been down. I've found quite a few downtime sql reports such as the one below but have been unable to get any of them to work in the current 2020 enviroment. Anyone have any suggestions on how to do this?
I've tried scripts such as the following:
SELECT * from (
SELECT
StartTime.EventTime AS Down_Event_time,
(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
AND EventTime IS NOT NULL
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)
) as uptimetable
WHERE outageDurationInMinutes IS NOT NULL
ORDER BY eventtime desc
All I get is "Query not valid". The custom property I'm trying to use is "SAP_Printer = 'YES'.
Anyone able to advise?