2 Replies Latest reply on Aug 30, 2017 2:24 PM by ricardo.morais

    Creating report on Nodes for down time and down time counts

    haneef.mohammed@landmarkgroup.com

      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

        • Re: Creating report on Nodes for down time and down time counts
          ricardo.morais

          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."

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

          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!

          • Re: Creating report on Nodes for down time and down time counts
            cscoengineer

            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.

             

            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.

             

            Select

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

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

            ,'<a href="/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