Node Downtime Reports

rob.hock

We’ve prepared two reports within Report Writer to report on node downtime:

Node Down Time Report – shows outage duration for each node when and how long it was down

4-28-2014 5-07-02 PM.png

Summarized Node Down Report – summarize node outage duration for each node

4-28-2014 5-07-22 PM.png

These reports will work with or without view limitations applied.

Feedback most appreciated.

attachments.zip

Find more posts tagged with

report

Accepted answers

All comments

jeff.postle

Excellent report, thank you.

Is it possible to add a time range? e.g. last 7 days.

aclarkvonmaur

Can you modify this to display only nodes based on custom properties? Such as Model = 'Router'

wodonnell

Thanks for the reports, a little modification and they worked well for what I needed.

If you'd like a time range Jeff, just add a filter at the end, after outageDuration like:

where outageDurationInMinutes IS NOT NULL

AND datepart(hour, DownEventTime) >= 7

AND datepart(hour, UpEventTime) >= 7

AND datepart(hour, DownEventTime) <= 18

AND datepart(hour, UpEventTime) <= 18

Just change the datepart statement to match the criteria you're looking for.

Aclarkvonmaur, just add your custom field at the top, so it's included, eg like this:

SELECT * FROM (

SELECT

Nodes.Caption,

Nodes.NodeID,

Nodes.Reporting_Group,

Nodes.Reporting_Group2,

StartTime.Message,

StartTime.EventTime AS DownEventTime,

Nodes.Reporting_Group and Nodes.Reporting_Group2 are custom properties I added.

then at the bottom, just have it filter by adding another AND statement like

AND Reporting_Group IS NOT NULL

tiagoalmeida

In case I have a created group. How can I add the code to get this for a named group "South Florida" ? thank you guys

FormerMember

hi rob.hock thank you for making this report it is really helpful but can you tell me how to filter report based on custom reports basically i am non programming guy i tried to make change but i wouldn't get succeed in this so would you help me in this thank you

david.lam

Thanks Rob.Hock. Much appreciated. Report works as required. Although it is complicated and hard to breakdown (for me). I've added under the WHERE clause:

AND

datepart(MONTH, DownEventTime) = datepart(MONTH,GetDate())

To show only the current month's report. or

AND

datepart(MONTH, DownEventTime) = (datepart(MONTH,GetDate()) -1)

To get previous month's only.

I've then added this to the web portal for easy reporting.

CHeers

RichardLetts

I think your brackets are not quite right for the previous months' report.

datepart(MONTH, DownEventTime) = datepart(MONTH,GetDate()-1)

[which is I think the month for yesterday]

should be:

datepart(MONTH, DownEventTime) = datepart(MONTH,GetDate()) -1

which is the month for today, minus 1, which will be right for every month except January.

I think you need to say something like:

this month:

DownEventTime between dateadd(month, datediff(month, 0, getdate()), 0) and dateadd(month, datediff(month, 0, getdate())+1, 0)

Last Month

DownEventTime between dateadd(month, datediff(month, 0, getdate())-1, 0) and dateadd(month, datediff(month, 0, getdate()), 0)

david.lam

Awesome Richard Letts, Thanks!

You're most certainly right, I did a bad one there but edited it soon afterwards. And the fact that January minus 1 wouldn't work is something a newbie like me didn't think of.

I've updated my reports as you've recommended and working like a charm

This is why I like Solarwinds so much. A good product, backed by a great community and collaborative model where you could find anything.

Also i've noticed that the original report only gives out 2 month's worth of downtime reporting. Is this due to the databases only keeping 2 months worth or was that in the original query because i couldn't quite gather that from the query.

Cheers

jon.ashlock

This is great. I am at a total loss on sql though and even though above references how to add a date range I don't know where to add that in or proper formatting. We are needing a couple reports just like this but have the following flavors. Report by previous week, previous month, and previous year. We would also like it to return back the siteid for where the node is located.

Can anyone tweak the sql statement for me? Thank you in advance.

Jon

poison

Hello rob.hock,

Can you please edit the first report so we can add the time period for which we would like to see the downtime report? can you please edit and create that report for me, it would be very helpful. thanking you in advance.

avrilspirit

Hi,

Thanks a lot for this report, may we have the syntax for :

-Today

-This Month

-This Year

Because I already test with Orion Report Writer syntax (42029 42003...) but it doesn't works.

Thnaks for your help.

sahadeo_cybertech

Thank you, can we sort this report as "Node with highest down time shows first in the List and least down down time shows last in the list"

theroadguy

What would have to be done to make this report show outage minutes by MachineType in the nodes table?

Here is an example what I am looking to convert over from an excel spreadsheet

I have narrowed the code down to only see our switches, see below

SELECT MAX(VendorIcon) AS VendorIcon, MAX(Caption) AS Caption, NodeID, SUM(OutageDurationInMinutes) AS SumOutageDurationInMinutes, MAX(StatusLED) AS StatusLED, MAX(MachineType) AS MachineType

FROM (

SELECT

Nodes.StatusLED,

Nodes.VendorIcon,

Nodes.MachineType,

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 UpTimeTable

WHERE OutageDurationInMinutes IS NOT NULL AND DownEventTime between dateadd(month, datediff(month, 0, getdate())-1, 0) and dateadd(month, datediff(month, 0, getdate()), 0) AND (Caption LIKE '%swt%' OR Caption LIKE '%stk%')

GROUP BY NodeID

ORDER BY Caption ASC

Thanks in advance, I will must like post this on the normal forum as well.

bardeev

Hi,

Please help me,

Something wrong with table "Events".

Some events is missing.

For example:

EventTime Message Acknowledged NetObjectType TimeStamp

2015-07-19 01:22:34.010 node1.local has stopped responding (Request Timed Out) 0 N

2015-08-10 11:56:13.403 node1.local has stopped responding (Request Timed Out) 0 N

2015-08-10 12:15:45.910 node1.local is responding again. Response time is 15 milliseconds. 0 N

SumOutageDurationInMinutes = 32352, becouse missing event, when a node is responding again.

How I can correct a report, that is ignore first event where EventType = 1, if after this going another EventType = 1, and beetwin broken event with EventType = 5.

Thanks.

tru_st

2 Troubles in this part of report :

1)When Node down earlied when 7 and later when 18

2)When Node up earlied when 7 and later when 18

tru_st

Sorry,

like this:

1)When Node DOWN earlied when 7 and later when 18 and UP between 7-18

2)When Node UP earlied when 7 and later when 18 and DOWN between 7-18

FormerMember

Hey Guys,

Just one question. How do you convert the "Outage Duration In Minutes" in this SQL statement "

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 UpTimeTable

where outageDurationInMinutes IS NOT NULL

ORDER BY Caption ASC, DownEventTime DESC"

to reflect Date and Time in this Format to HH24:MI:SS? I know something has to change in the SQL Statement, above

Thanks

FormerMember

Thank You! X1000

FormerMember

Hey Guys I'm having a tad bit of trouble implementing the last month clause into my report, not sure where I went wrong in this?

SELECT * FROM (

SELECT

Nodes.StatusLED,

Nodes.Caption,

Nodes.NodeID,

StartTime.Message,

StartTime.EventTime AS DownEventTime between dateadd(month, datediff(month, 0, getdate())-1, 0) and dateadd(month, datediff(month, 0, getdate()), 0),

(

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

ORDER BY Caption ASC, DownEventTime DESC

brentonprice

Here is my sql statement. It is working but I need to filter to only show Cisco 1841 and Cisco 1921/K9. Also I need it to only show 8 - 5 and 7 days back. Can any one help me make this work. Thanks everyone.

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

orioncrack

Bump to see if anyone can modify to include 1 custom property filter. Love this report, I'm just needing to filter.

Thanks - all of you SQL geniuses rule.

dennis.shiburi

I'm not an SQL expert, I want to ask where can I change to show records for last month or last 30 days for all devices?

nwetherwax

is there any way I can filter this report so that it displays nodes only from a specific group?

arey

It's 2017. Is this report still not available as an editable web-based query in NPM? Rather than having people search the web for something to fulfill what should be a basic query, and then import a legacy, non-editable report and beg for modifications, why hasn't this been converted to a web-based report so users can modify it as necessary?

Should we really be expected to go edit the SQL code manually to generate a subset of all nodes, or a reporting period?

julessanchez

Did anyone figure out how to do this ?

Generate the report for:

-Today

-This Month

-This Year

i_like_eggs

HI, thank you for the report done some tweeking to only pull the last 7 days and filter by a custom property value needed something to get downtime for interface. good starting point two thumbs up

SELECT * FROM (

SELECT

Nodes.StatusLED,

Nodes.Caption,

Nodes.NodeID,

StartTime.Message,

--added convert so i can group by days

CONVERT(DATE,StartTime.EventTime) AS [DATE],

StartTime.EventTime AS DownEventTime,

( SELECT TOP 1 EventTime

FROM Events AS EndTimeTable

where EndTimeTable.EventTime >= StartTime.EventTime

AND EndTimeTable.EventType = 11

AND EndTimeTable.NetObjectType = 'I'

AND EndTimeTable.NetworkNode = StartTime.NetworkNode

AND EventTime IS NOT NULL

AND EventTime > DATEADD(DAY,-7,GETDATE())

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 = 10 AND EndTime.NetObjectType = 'I'

AND EndTime.NetworkNode = StartTime.NetworkNode AND EventTime > DATEADD(DAY,-7,GETDATE()) ORDER BY EndTime.EventTime)

) AS OutageDurationInMinutes

FROM Events StartTime

INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

---here is the custom property called n_type

WHERE (StartTime.EventType = 10) AND nodes.n_type = 'Something' and EventTime > DATEADD(DAY,-7,GETDATE())

) AS UpTimeTable

where outageDurationInMinutes IS NOT NULL

ORDER BY Caption ASC, DownEventTime DESC

modot

I'm not SQL savvy at all, so how do you modify the Node_Down_Time_Report to only show devices that are currently down? I know you need to query on something to the effect of "Node Status = Down", but I'm not sure the correct syntax or placement for that line.

Thanks.

cscoengineer

Did some more tweaking and got the total down duration time along with a readable format (Days, hours, minutes) - plus pulling last week, last month, etc. Also took care of the null condition with a case statement.

DECLARE @StartDate Datetime

DECLARE @EndDate Datetime

-- Last month

--SET @StartDate=DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)

--SET @EndDate=DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))

-- Last Week

SET @StartDate=dateadd(dd, -1, dateadd(wk, datediff(wk, 0, getdate()) - 1, 0))

SET @EndDate=dateadd(ss, -1, dateadd(wk, datediff(wk, 0, getdate()), 0)-1)

--Current week

--SET @StartDate=dateadd(wk,0,dateadd(wk,datediff(wk,0,getdate()),0)-1)

--SET @EndDate=dateadd(dd,0,getdate())

SELECT

'<img src="/orion/images/statusicons/Small-'+down.StatusLED+'"/>' +

'<a href="thwack.solarwinds.com/.../View.aspx as nvarchar)+'">'+down.Caption+'</>' as [Node]

,cast(sum(down.OutageDurationInMinutes)/1440 as nvarchar) + ' Days '

+cast((sum(down.OutageDurationInMinutes)-cast(sum(down.OutageDurationInMinutes)/1440 as int)*1440)/60 as nvarchar)+'hr '

+cast((sum(down.OutageDurationInMinutes))-(cast(sum(down.OutageDurationInMinutes)/1440 as int)*1440 +cast((sum(down.OutageDurationInMinutes)-cast(sum(down.OutageDurationInMinutes)/1440 as int)*1440)/60 as int)*60) as nvarchar)+'m' as [Down Duration]

--,sum(down.OutageDurationInMinutes) as [DownDuration]

FROM (

SELECT

Nodes.StatusLED, Nodes.Caption, Nodes.NodeID, Nodes.MachineType

,StartTime.Message, StartTime.EventTime AS DownEventTime

,case when (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

) is null then getdate() else (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

) end 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) and starttime.EventTime between @startdate and @enddate

) AS down

group by down.caption, down.StatusLED, down.NodeID

ORDER BY Caption ASC

kenji_sakuramoto

I would like to add a NodeCustomProperties (City) and I have tried:

adding INNER JOIN NodeCustomProperties on StartTime.NetworkNode = NodeCustomProperties.NodeID (After your INNER JOIN)
adding NodeCustomProperties.City as [City] (After your [Down Duration])

Syntax is not working, what else do I need to add?

ttbounty

Folks having a small issue with the report, it works in most cases but within the report, it states a, for example, a server Outage duration of 5743 minutes but at no time I remember that machine being down for such a long period.

Even when going back via NPM to look at availability for that specific server it also does not show the server being down for the time and day presented in the report, what could cause these anomalies