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

Did you run hot fix 6 ?

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

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.

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. 

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

3 nodes down

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?

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. 

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

Hello,

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

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