cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Current Outage Duration for Down Nodes SQL query stopped working after upgrading to NPM 11.5.2

Jump to solution

Hey all - so we had a report that showed currently down nodes and their current downtime.  worked great in 11.0.1 and when we upgraded to 11.5.2 most of our sql stuff broke.  Is the below query still valid? because we currently have down nodes and the report isnt selecting anything.

SELECT

n.NodeID,n.Statusled,n.Caption,TriggerTimeStamp AS Event_Detected,DateDiff(hh,TriggerTimeStamp,GetDate()) AS Duration_In_Hours,

DateDiff(Mi,TriggerTimeStamp,GetDate()) AS Duration_In_Minutes

FROM AlertDefinitions ALD JOIN AlertStatus ALS ON ALD.AlertDefID=ALS.AlertDefID

JOIN Nodes n ON ALS.ActiveObject=n.NodeID

Where AlertName LIKE '%Down%' AND als.ObjectType='Node'

AND als.State=2

pastedImage_0.png

Labels (2)
0 Kudos
1 Solution
Level 8

So I i found another query on the forums here Re: Include downtime in current node down report

I removed some of the custom stuff from that and ended up with this

SELECT

  1. N.StatusLED as Status,N.Caption AS Device,MAX(E.EventTime) AS DownTime,

Cast(DateDiff(day,MAX(E.EventTime),getdate()) as varchar) + ' Day(s) ' + convert(char(8),dateadd(second,DateDiff(second,MAX(E.EventTime),getdate()),0),14) as Duration

FROM

Nodes N

INNER JOIN Events E ON E.NetworkNode = N.NodeID

where N.status = 2 and eventtype = 1

GROUP BY

     N.StatusLED,

     N.Caption

So now the report is reporting correctly.  I really have no idea what got messed up in my original query after upgrading but im glad i found a working query. 

pastedImage_5.png

View solution in original post

12 Replies

Hello,

Why is not all down nodes we have is not showing on the report?

0 Kudos

There is a join from the nodes to the events table, so my first guess would be that your nodes have been down so long that their down event has aged out of the events table, 30 days is the default for that.

Based on that specific edge case I went ahead and rewrote the query I usually use for that kind of outage duration report, try this and see if it works better for you.

Node Downtime with Duration and Minimum Length Filtering

- Marc Netterfield, Github
0 Kudos
Level 16

Did you run hot fix 6 ?

0 Kudos
Level 8

Thanks for the reply - I did not - does the hotfix fix sql related things? 

0 Kudos
Level 16

SolarWinds Orion Platform 2015.1.2 Hotfix 6

---------------------------------------------

This cumulative Hotfix addresses these issues:

Orion Platform 2015.1.2 Hotfix 1

* Interface alerts retrigger when a node is deleted.

* Orion thresholds are not filtered by type.

* Updates the topology calculation behavior. When possible, MAC Address information related to a port is added to help map the connection target interface from the LLDP data when the RemotePortId uses the MAC Address Port Subtype.

Orion Platform 2015.1.2 Hotfix 2

* Allied-Telesis device shown as Allied-Telesyn.

* The Message Center does not work for non-administrator user accounts.

* Filter Nodes (SQL) fails for the Report from Orion Report Writer resource.

* The 2 MB line is duplicated on the Report Chart.

* You are notified that your database maintenance is overdue after your nightly database maintenance.

* The polling engine is slow or non-reporting.

* Network Discoveries does not complete.

Orion Platform 2015.1.2 Hotfix 3

* Limitations do not work on Custom Properties and Forecast Capacity for nodes and Forecast Capacity for volumes.

* The Machine Type is not polled for some device types.

* Improved the page load time on the Web Console.

Orion Platform 2015.1.2 Hotfix 4

* IIS is crashing and the Orion web site is unavailable.

* You cannot set topology calculation to skip repeated MAC/IP addresses.

* Alerts with the condition Node IsServer do not work.

* PowerShell script cannot be executed from the Alert Manager.

* Windows accounts cannot be added to Orion if "Force SmartCard logon" is set.

Orion Platform 2015.1.2 Hotfix 5

* List of vendor specific MAC prefixes updated.

* Missing edit button on a COR with chartv2 resource.

* Cannot edit the name of a Multi Object Chart.

* Email Action breaks HTML messages.

* Trap Action execution fails if the Trap template has incorrect Macro syntax.

* Manage Pollers functionality is not available in NCM 7.4 standalone.

* Auto Dependency creates dependency loop.

Orion Platform 2015.1.2 Hotfix 6

* Incorrect group status is propagated to parent groups for some nested group structures.

* TCP/IP connection and memory leak in BusinessLayer alerting.

* Custom Chart - data not available on Group views.

* Subscriber improvements for version 11.5.

* Performance issues related to limitations and groups.

* Some resources are blank because the SQL query processor ran out of resources and could not build a query plan.

* Small issues related to limitations and groups, such as broken group member links.

* Alerts with Group Member conditions cause errors and fail to fire.

* Issue with increased Memory/CPU consumption.

0 Kudos
Level 8

So i've installed hotfix 6 and still the same behavior.  Can anyone tell me if the SQL query is still legit after the  upgrade to 11.5.2? Im no SQL expert by any means. 

0 Kudos
Level 16

Query seems to run fine can you re-create the query and see what happens

query.PNG

Level 8

3 nodes down

pastedImage_1.png

When i execute the SQL query it doesn't find anything.  I'm wondering if a table or something got changed? Am i looking for some data in the wrong place now?

pastedImage_2.png

0 Kudos
Level 8

So I i found another query on the forums here Re: Include downtime in current node down report

I removed some of the custom stuff from that and ended up with this

SELECT

  1. N.StatusLED as Status,N.Caption AS Device,MAX(E.EventTime) AS DownTime,

Cast(DateDiff(day,MAX(E.EventTime),getdate()) as varchar) + ' Day(s) ' + convert(char(8),dateadd(second,DateDiff(second,MAX(E.EventTime),getdate()),0),14) as Duration

FROM

Nodes N

INNER JOIN Events E ON E.NetworkNode = N.NodeID

where N.status = 2 and eventtype = 1

GROUP BY

     N.StatusLED,

     N.Caption

So now the report is reporting correctly.  I really have no idea what got messed up in my original query after upgrading but im glad i found a working query. 

pastedImage_5.png

View solution in original post

Level 8

Hi, I thank you for this code but I have a problem.

This code doesn't show nodes with long down duration, for instance, I have a node which has been down for more than 150 days which is not shown in the table. However, fewer durations are shown.

0 Kudos
Level 16

Cool I was trying to do the same thing but got tied up.

0 Kudos

currently we have 30 nodes down but only showing 20 nodes on the report.

0 Kudos