Report Writer - Nodes Down More than 3 Days

I am using Orion NPM to monitor several hundred sites that are managed by local administrators. I would like to have a report produced that will list all devices that have been down for more than 3 days so we can contact the administrator to see if that device has been taken offline and should thus be removed. I have been playing around with Report Writer to try and find this functionality but have not yet succeeded. Any suggestions?

Find more posts tagged with

sql_nodes_down_for_3_days

Accepted answers

mezdem

SELECT TOP (100) PERCENT dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101)as AlertRaised, dbo.AlertLog.ObjectType, dbo.Nodes.IP_Address, dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.NodeIDFROM dbo.ActionDefinitions INNER JOINdbo.AlertDefinitions ON dbo.ActionDefinitions.AlertDefID = dbo.AlertDefinitions.AlertDefID INNER JOINdbo.AlertLog INNER JOINdbo.Nodes ON dbo.AlertLog.ObjectID = dbo.Nodes.NodeID ON dbo.AlertDefinitions.AlertDefID = dbo.AlertLog.AlertDefIDGROUP BY dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101), dbo.AlertLog.ObjectType, dbo.Nodes.IP_Address,dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.NodeIDHAVING (dbo.Nodes.Status <> '1') AND (dbo.AlertDefinitions.AlertName = N'Alert me when a node goes down') AND (convert(varchar(10),dbo.AlertLog.LogDateTime, 101) < GETDATE() - 3)

All comments

lbyoung

A report showing "Last Uptime" for down nodes would also work if it could be sorted by date so those sites down the most amount of time appeared first.

mezdem

Hi there, I am looking into something similar this avo.. I have come up with the below but it needs tweaking still..I only have one node down and its only gone down today...so you may need to come back to me if it does not look right.

This selects all nodes that are currently down, (not UP anyway) and where alerts for node down were raised within as down for greater than 3 days.

SELECT

TOP (100) PERCENT dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101)as AlertRaised, dbo.AlertLog.ObjectType,

dbo

.Nodes.IP_Address, dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.

NodeID

FROM

dbo.ActionDefinitions INNER

JOIN

dbo

.AlertDefinitions ON dbo.ActionDefinitions.AlertDefID = dbo.AlertDefinitions.AlertDefID INNER

JOIN

dbo

.AlertLog INNER

JOIN

dbo

.Nodes ON dbo.AlertLog.ObjectID = dbo.Nodes.NodeID ON dbo.AlertDefinitions.AlertDefID = dbo.AlertLog.

AlertDefID

GROUP

BY dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, convert(varchar(10),dbo.AlertLog.LogDateTime, 101), dbo.AlertLog.ObjectType, dbo.Nodes.IP_Address,dbo.Nodes.Caption, dbo.Nodes.Location, dbo.Nodes.Status, dbo.Nodes.

NodeID

HAVING

(dbo.Nodes.Status <> '1') AND (dbo.AlertDefinitions.AlertName = N'Alert me when a node goes down') AND (convert(varchar(10),dbo.AlertLog.LogDateTime, 101) < GETDATE() - 3)

Try this, It does not at this moment exclude any devices that have been flapping just those that have been down for 3 days..

lbyoung

Thanks for the script, unfortunately it is not giving me any nodes. I have 4-5 nodes that have been down for about a week and they are not showing up in the SQL Query. I am not getting any error messages and unfortunately, my background is in Network Architecture so I have very limited SQL experience to edit the script. Please keep me posted if you have any suggestions or updates and again, I thank you for your help.

lbyoung

It is possible that I am entering the script into the wrong location in Report Writer as this is my first attempt. I am going through File- New Report - Advanced SQL and then pasting the script under the SQL tab and running the Execute SQL Query function.

mezdem

no your doing it right - i have found the error in my code - will get back to you with it !