1 Reply Latest reply on Aug 30, 2018 9:53 AM by jamiewillis

    Creating custom report

    raisul

      Assalamu Alaikum.

       

      Greetings everyone.

      In solarwind NPM I want to create a custom report which will show the down nodes in a specific period of time on a specific day.

       

      I want the report in this way:

       

      Down nodeTimeDateDown for (Minutes)
      A12.00-14.0017/7/201830
      B12.00-14.0017/7/201820
      C12.00-14.0017/7/2018180

       

      I want to make this report with SQL code.

       

      Kindly share a SQL code with me with that I can generate this report.

       

      Thanks in advance.

        • Re: Creating custom report
          jamiewillis

          I have a similar query - but this is what I found (SWQL)

          SELECT

            NodeName AS [Node Name],
            machinetype AS [Machine Type],
            IP_Address AS [IP Address],
            '/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, machinetype, IP_Address
          ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate())  desc