Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Outage report for nodes on NPM 11.5.2+

ok, im opening a new discussion as suggested by others so I can get help on this specific request and not getting suggestions on how to do it another way. this is what I need:

I need to have a report showing up in NPM in the nodes detail that would show when the event node down shows and how long (duration) the node was down. Seems pretty simple, yet I know nothing of SQL so I need help. I was given a script that works in the Report Write tool, but not on the web. I need this available (same output) in the web and based on only the node your looking at. Right now, in the report writer, it shows all nodes. On the web, it needs to show for that specific node.

Here is the SQL I was given that works in the report writer but not in the Web.

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 Caption desc

The above is what shows in the report tool, but i need this in the web and filtered based on the node your looking at.

When I add this report to the web on NPM, I get the below:

So can anyone help me getting this to work properly in the web PLEASE ! Thanks.

Find more posts tagged with

Accepted answers

zackm

*Edited to add hours column with more granularity*

Ok, so this is going to be a long one, but it should end up getting you the results you want.

First off, you're going to want to ditch the SQL query and get down on some SWQL!!!

In line with that, you're going to want to use the 'Custom Query' resource, instead of a 'Custom Table'. This allows us to make a nice resource where we can limit the # of rows shown on the page, thus conserving that precious pixel space on your Node Details view. It's also key to making a resource that is relative to the node you are on.

Source code will be at the bottom, here are your steps:

Navigate to a Node Details view, click 'Customize Page' and add a 'Custom Query' resource to your page
Edit the new 'Custom Query' resource. Give it a meaningful title, copy/paste the source code into the query section, limit the rows you would like to see, and click 'Submit'

That's it. Now you have a cool resource that will summarize your downtime per node!!!

Node that is currently down (notice the top 'Up Event' is blank...)

Node that was down at one time

SWQL Query (with comments to try and explain what we are doing):

SELECT  -- Device name  StartTime.Nodes.Caption AS [Device]  -- This will generate the status icon to the left of the device name  ,'/Orion/images/StatusIcons/Small-' + StartTime.Nodes.StatusIcon AS [_IconFor_Device]  -- I found that the 'Message' column was too wide for my purposes and opted to remove it  -- If you want it in your resource, just remove the comment marker (--) from the line below  --,StartTime.Message  -- Down Event time stamp in local time zone  ,ToLocal(StartTime.EventTime) AS [Down Event]  -- Up Event time stamp in local time zone  ,(SELECT TOP 1   ToLocal(EventTime) AS [EventTime]   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime >= StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULL  ORDER BY EndTime.EventTime  ) AS [Up Event]  -- Downtime duration in minutes. Based on either the current time (node is still down) or Up Event time (node is back up)  ,CASE  WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime]   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime >= StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULL  ORDER BY EndTime.EventTime) IS NULL THEN MINUTEDIFF(StartTime.EventTime, GETUTCDATE())  ELSE MINUTEDIFF(StartTime.EventTime,   (SELECT TOP 1 EventTime   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime > StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  ORDER BY EndTime.EventTime))  END AS [Minutes]  -- Downtime duration in hours. Based on either the current time (node is still down) or Up Event time (node is back up)  ,CASE  WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime]   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime >= StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULL  ORDER BY EndTime.EventTime) IS NULL THEN ROUND((MINUTEDIFF(StartTime.EventTime, GETUTCDATE())/60.0),2)  ELSE ROUND((MINUTEDIFF(StartTime.EventTime,   (SELECT TOP 1 EventTime   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime > StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  ORDER BY EndTime.EventTime))/60.0),2)  END AS [Hours]-- This is the table we are queryingFROM Orion.Events StartTime-- EventType = 1 is to correlate with our 'Down Event' time stamp from line 13 aboveWHERE StartTime.EventType = 1-- Here's the trick to get the query to dynamically pull details for the node you are looking atAND StartTime.NetObjectID = ${NodeID}-- Order our results from the newest to oldest 'Down Event'ORDER BY [Down Event] DESC

Hope this helps you out. Let me know if there are questions!

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

All comments

zackm

*Edited to add hours column with more granularity*

Ok, so this is going to be a long one, but it should end up getting you the results you want.

First off, you're going to want to ditch the SQL query and get down on some SWQL!!!

Source code will be at the bottom, here are your steps:

Navigate to a Node Details view, click 'Customize Page' and add a 'Custom Query' resource to your page
Edit the new 'Custom Query' resource. Give it a meaningful title, copy/paste the source code into the query section, limit the rows you would like to see, and click 'Submit'

That's it. Now you have a cool resource that will summarize your downtime per node!!!

Node that is currently down (notice the top 'Up Event' is blank...)

Node that was down at one time

SWQL Query (with comments to try and explain what we are doing):

SELECT  -- Device name  StartTime.Nodes.Caption AS [Device]  -- This will generate the status icon to the left of the device name  ,'/Orion/images/StatusIcons/Small-' + StartTime.Nodes.StatusIcon AS [_IconFor_Device]  -- I found that the 'Message' column was too wide for my purposes and opted to remove it  -- If you want it in your resource, just remove the comment marker (--) from the line below  --,StartTime.Message  -- Down Event time stamp in local time zone  ,ToLocal(StartTime.EventTime) AS [Down Event]  -- Up Event time stamp in local time zone  ,(SELECT TOP 1   ToLocal(EventTime) AS [EventTime]   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime >= StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULL  ORDER BY EndTime.EventTime  ) AS [Up Event]  -- Downtime duration in minutes. Based on either the current time (node is still down) or Up Event time (node is back up)  ,CASE  WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime]   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime >= StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULL  ORDER BY EndTime.EventTime) IS NULL THEN MINUTEDIFF(StartTime.EventTime, GETUTCDATE())  ELSE MINUTEDIFF(StartTime.EventTime,   (SELECT TOP 1 EventTime   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime > StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  ORDER BY EndTime.EventTime))  END AS [Minutes]  -- Downtime duration in hours. Based on either the current time (node is still down) or Up Event time (node is back up)  ,CASE  WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime]   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime >= StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULL  ORDER BY EndTime.EventTime) IS NULL THEN ROUND((MINUTEDIFF(StartTime.EventTime, GETUTCDATE())/60.0),2)  ELSE ROUND((MINUTEDIFF(StartTime.EventTime,   (SELECT TOP 1 EventTime   FROM Orion.Events AS [EndTime]  WHERE EndTime.EventTime > StartTime.EventTime  AND EndTime.EventType = 5  AND EndTime.NetObjectType = 'N'  AND EndTime.NetObjectID = StartTime.NetObjectID  ORDER BY EndTime.EventTime))/60.0),2)  END AS [Hours]-- This is the table we are queryingFROM Orion.Events StartTime-- EventType = 1 is to correlate with our 'Down Event' time stamp from line 13 aboveWHERE StartTime.EventType = 1-- Here's the trick to get the query to dynamically pull details for the node you are looking atAND StartTime.NetObjectID = ${NodeID}-- Order our results from the newest to oldest 'Down Event'ORDER BY [Down Event] DESC

Hope this helps you out. Let me know if there are questions!

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

npereira1

zackm‌

Thanks for the help this works like a charm.

Once last question, what is the time span of these results? I would like to see the last 90 days. What would need to change? I think this currently only shows last 30 days.

Thanks !

npereira1

There seems to be an issue with the duration in hours. If the downtime goes from 6am to 7am but is only 24 minutes, the hours shows 1 eventhough the downtime was only 24 minutes but spanned a different hour. See example:

ALB-FWEXT01.dc.cbn

ALB-FWEXT01.dc.cbn has stopped responding (Timeout)

12/12/2015 6:37:53 AM

12/12/2015 7:01:45 AM

npereira1

zackm‌,

can you tell me what to change if I want last 90 days instead of last 30 days? i have changes the settings so that the events are kept for a minimum of 90 days.

Thanks greatly appreciated.

zackm

I've updated the query to add more granularity to the hours column. try it now.

zackm

If you have changed your event retention to 90 days, then you just need to wait. There is nothing in this query that is limiting the time, so it will return all results available in the table.

Just a note, changing the retention to 90 days does not immediately give you 90 days of events. It gives you 90 days of capacity that you need to fill up. If you orginally had 30 days of retention, and moved that to 90, you will need to wait 60 days to see a full 90 in the table.

npereira1

ok, understood Zack thanks !

abdulraheemsidz

Can we have the same SWQL to work in reports ?? I mean instead of ${NodeID} can we specify a particular node and generate report.

zackm

surely, you just need to identify the node you want and add its particular nodeid in place of the ${NodeID} variable

abdulraheemsidz

Thanks for your response, tried giving nodeid in the same query it doesn't work.

abdulraheemsidz

No problem, I used same SWQL query and changed it to plain SQL query which anyone can use in generating reports for specific nodes.

SELECT * from (SELECTStartTime.EventTime AS Down_Event_time,(SELECT TOP 1EventTimeFROM Events AS EndtimeWHERE EndTime.EventTime >= StartTime.EventTimeAND EndTime.EventType = 5AND EndTime.NetObjectType = 'N'AND EndTime.NetObjectID = StartTime.NetObjectIDAND EventTime IS NOT NULLORDER BY EndTime.EventTime) AS Up_Event_Time, Nodes.Caption, Nodes.IP_Address-- Downtime duration in Minutes. Based on either the current time (node is still down) or Up Event time (node is back up) ,CASEWHEN(SELECT TOP 1 EventTime FROM Events AS EndtimeWHERE EndTime.EventTime >= StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULLORDER BY EndTime.EventTime) IS NULL THEN ROUND  (  (DATEDIFF(Minute, StartTime.EventTime, GETUTCDATE())),2 ) ELSE ROUND ((DATEDIFF  (Minute, 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.NetObjectID = StartTime.NetObjectID  ORDER BY EndTime.EventTime  )  )),2) END AS Minutes-- Downtime duration in Hours. Based on either the current time (node is still down) or Up Event time (node is back up),CASEWHEN(SELECT TOP 1 EventTime FROM Events AS EndtimeWHERE EndTime.EventTime >= StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'AND EndTime.NetObjectID = StartTime.NetObjectID  AND EventTime IS NOT NULLORDER BY EndTime.EventTime) IS NULL THEN ROUND  (  (DATEDIFF(Minute, StartTime.EventTime, GETUTCDATE())/60.0),2 ) ELSE ROUND ((DATEDIFF  (Minute, 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.NetObjectID = StartTime.NetObjectID  ORDER BY EndTime.EventTime  )  )/60.0),3) END AS Hours-- Displaying the required columns (in this case Hours, Minutes, IP_Address)FROM Events StartTime INNER JOIN Nodes ON StartTime.NetObjectID = Nodes.NodeIDWHERE (StartTime.EventType = 1)) as uptimetableWHERE Minutes IS NOT NULL-- Specify which nodes you want in this report.AND IP_Address IN ('172.30.1.1', '1.1.1.2')ORDER BY Down_Event_time desc