This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

  • 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!

  • 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="thwack.solarwinds.com/.../View.aspx as varchar)+'">'+n.Caption+'</>' [Node]

    ,'<a href="thwack.solarwinds.com/.../View.aspx 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

  • @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.

  • 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!