Uptime and Downtime Report

Hello

Maybe this will be enough for you.

1. Create report.

2. Create Custom Table.

3. As datasource use Custom SQL:

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

4. Submit

5. Pick all columns and modify how do you want.

6. Save it.

This Shows -> Caption , StartDownTime, EndDownTime and Duration of downtime in minutes and Event message.

This show only downtimes last 30days. If they needs more he needs modify retention time of events.

1. Settings.

2. Events Retention      days    Network events will be deleted from the database after configured days.

How would you taylor this for 1 specific node ID (24) and where is the setting for retention?

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 AND NodeID = '24'

ORDER BY eventtime desc

For retention settings, open web console. Go to Settings > Polling Settings > find the Events Retention

Hi,

That does not work. The report writer says:

SQL Error:

Invalid column name 'NodeID'.

If I just do the report as this thread indicates, i get a SQL error:

Invalid column name 'eventtime'

BTW, im runining NPM 11.5.2

is this even good for my version?

probably not. there had been some changes on the Orion database starting NPM 11.5 release.

so this needs to be updated for 11.5

anyone know what needs to change?

npereira1‌ Try changing the "ORDER BY eventtime desc" on the last line to one of the following:

ORDER BY Down_Event_time desc

ORDER BY UpEventTime desc

ORDER BY Caption desc

ORDER BY Message desc

ORDER BY outageDurationInMinutes desc

All of these appear to work for me, and we are running the following:

Orion Platform 2015.1.2, NCM 7.4, NPM 11.5.2, DPA 10.0.0, QoE 2.0, IVIM 2.1.0, SAM 6.2.2

And, if you want to put this report on the Node Details page for all nodes, but have it show only the stats that belong to the single node you are viewing, you can add this near the end, BEFORE the order by line:

AND Nodes.NodeID='${NodeID}'

If you use the line above, the query will not run in the report writer, but, it will work once you add the report to the node details page.

I hope this helps you.

Thank you,

-Will

And how do I add this report to the node detail's page?

And i keep getting an error where ever I put the line AND Nodes.NodeID='${NodeID}'

Where does it actually need to be"?

npereira1‌

Save your query in the Report Writer.

Then, go to the node details page, in the top right corner of the page, click customize page.

Next, click the green plus button/icon for the column you want the report in, choose the "Report from Orion Report Writer" resource, and add it to the page.

Now all you need to do is edit the newly added "Report from Orion Report Writer" resource, choose your report from the dropdown list, and submit.

I hope that helps you out.

Thank you,

-Will

npereira1‌ You need to put the "${NodeID}" filter towards the bottom of the query, on the 2nd to last "WHERE" line.

The line should look like this:

WHERE (StartTime.EventType = 1) AND Nodes.NodeID = '${NodeID}'

I just tested it on my system, and it appears to work properly.

I hope this helps you.

Thank you,

-Will