5 Replies Latest reply on Jun 21, 2018 10:48 AM by D_W_K

    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

            1 of 1 people found this helpful
              • Re: Creating report on Nodes for down time and down time counts
                rebelezna

                @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

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

                  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!