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

Creating report on Nodes for down time and down time counts

Hi Team,
We want to generate report on specific node or group of nodes that how many times the node went down. Please help us to create this report.Our senior management requested report some business analysis.

Regards

Haneef

0 Kudos
5 Replies
Level 12

This has been posted else where in SQL, I made it into a SWQL.  You can modify it to put in custom properties as needed.

pastedImage_0.png

SELECT

  NodeName AS [Node Name],

  '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],

  DetailsUrl AS [_LinkFor_Node Name],

  concat(SUBSTRING(tostring(MAX(e.EVENTTIME)),1,4),SUBSTRING(tostring(MAX(e.EVENTTIME)),5,2),

        SUBSTRING(tostring(tolocal(MAX(e.EVENTTIME))),12,8)) as Downtime,

  CONCAT(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24,' Day(s) ',

        HOURDIFF(tolocal(max(e.eventtime)),getdate())-(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24)*24,'h ',

        MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   (MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60,'m') AS Duration

        -- ,MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate()) as minutes_since

        --HOURDIFF(tolocal(max(e.eventtime)),getdate())/24 as Days,

        --HOURDIFF(tolocal(max(e.eventtime)),getdate())     -   (HOURDIFF  (tolocal(max(e.eventtime)),getdate())/24)*24 as hours,

        --MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   --(MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60 as mins

FROM ORION.NODES NODES

INNER JOIN ORION.EVENTS e ON NODES.NODEID = e.NETWORKNODE

WHERE STATUS = 2 and E.Eventtype=1 --and nodes.customproperties.SystemsGrouping Like '%CPE%'

GROUP BY NodeName, StatusIcon, DetailsUrl

ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate())  desc

For the number of times a node went down (today), you can change the time period as needed.

pastedImage_1.png

Select

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

'<a href="https://thwack.solarwinds.com/Orion/View.aspx?View=NodeDetails&NetObject=N:'+cast(n.NodeID as varchar)+'">'+n.Caption+'</>' [Node]

,'<a href="https://thwack.solarwinds.com/Orion/View.aspx?View=NodeDetails&NetObject=N:'+cast(n.NodeID as varchar)+'">'+n.IP_Address+'</>' [IP_Address]

,count(1) [DownCount]

From Nodes n

join Events e on n.nodeid = e.NetObjectID

where e.NetObjectType='N' and e.EventType=1

      and datediff(dd,e.EventTime,getdate()) = 0

Group by n.nodeid, n.caption, n.ip_address, n.statusled

order by count(1) desc

Thanks

Amit

This is excellent, and almost exactly what I was looking for.  I know nothing about SWQL, but would like to be able to filter this so it only includes nodes with certain custom properties (a product name), and reverse the order so that the most recently down nodes are at the top of the list.  Any advice offered gratefully received!

0 Kudos

@cscoengineer

First off, thanks for this! The top query is perfect for the report i need. I did add a few more columns i needed, although IP address syntax didnt match the rest of the query, but works. (In bold below) The only real issue, is that is seems to limit what it reports on. For instance, i have 20 nodes down, it only reports on some, but not all nodes that are down. (possibly something in the join statement?) Being a SQL/SWQL noob, any help would be appreciated.

SELECT

  NodeName AS [Node Name],

  '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],

  DetailsUrl AS [_LinkFor_Node Name],

  machinetype AS [Machine Type],

  IP_Address AS [IP Address],

  concat(SUBSTRING(tostring(MAX(e.EVENTTIME)),1,4),SUBSTRING(tostring(MAX(e.EVENTTIME)),5,2),

        SUBSTRING(tostring(tolocal(MAX(e.EVENTTIME))),12,8)) as Downtime,

  CONCAT(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24,' Day(s) ',

        HOURDIFF(tolocal(max(e.eventtime)),getdate())-(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24)*24,'h ',

        MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   (MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60,'m') AS Duration

        -- ,MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate()) as minutes_since

        --HOURDIFF(tolocal(max(e.eventtime)),getdate())/24 as Days,

        --HOURDIFF(tolocal(max(e.eventtime)),getdate())     -   (HOURDIFF  (tolocal(max(e.eventtime)),getdate())/24)*24 as hours,

        --MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   --(MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60 as mins

FROM ORION.NODES NODES

INNER JOIN ORION.EVENTS e ON NODES.NODEID = e.NETWORKNODE

WHERE STATUS = 2 and E.Eventtype=1

GROUP BY NodeName, StatusIcon, DetailsUrl, MachineType, IP_Address

ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate())  desc

The only scenario I can think of where those nodes would not have corresponding down events might be if they have been down longer than your event retention period.  You can check how long yours is set to by going to settings > Polling Settings.  My lab is set to 30 days, but I can't recall if that's the default value, I feel like it may have been 60 out of the box.

- Marc Netterfield, Github
0 Kudos

Hi Mohammed,

I did a few tests and came up with the following SQL query that may help you accomplish the report that you seek.

select

nodes.nodeid as ID,

nodes.caption as NodeName,

count(events.netobjectid) as Cnt

from nodes

left join events on nodes.nodeid = events.netobjectid

where events.message like 'Node%is Down.'

group by nodes.nodeid, nodes.caption

To create the report, you need to:

1- Create a new report on the Web Console, and give it a name

2- Add Custom table

3- On the Selection Method choose "Select Advanced Database Query."

4- Tick the SQL, paste the query above and click "Add to Layout."

pastedImage_1.png

5-  When Editing the resource, click on Add Columns, select all the columns and press "Add Column."

pastedImage_2.png

6- Then press Submit

On the preview page, you should see the output of the report where the CNT column is the number of "Node XX is down" on the events table, the ID is the NodeID and the name of the node.

I hope this info will help you accomplish the task in hands!