Hi,
Anyone please help me with query to create a nodes availability report including downtime in minutes.
similar to attached images including ip address, node name, device type, vendor, availability in percentage, downtime in minutes.
Thanks
@kashif091 The best route would be to utilize SWQL to generate this report. For now, I have a quick SQL query that could assist. Create a new Report > Select Custom Table > and for the Data Source - choose Advanced and hit the radio button for SQL. Input the following query:
SELECT * FROM ( SELECT Nodes.StatusLED, Nodes.Caption, Nodes.NodeID, 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 UpTimeTablewhere outageDurationInMinutes IS NOT NULLORDER BY Caption ASC, DownEventTime DESC
anyone please help me to get this report,
thanks
Thanks for helping, Would you please add timestamp in this query so i can filter specific time results from this query, thanks
this query give report of how many days? which line to be modified if last 30 days report to be extracted.
Look at the Availability and Downtime reports published in the Content Exchange for Reports.
