2 Replies Latest reply on Jun 17, 2015 2:00 PM by taís

    Report

    taís

      Hello,

      I would like the support of you for creating availability reports to report in hours and minutes downtime.

       

      Today is the report used below, but  need to make manual adjustments in the collection period each month. It is possible an easier way to perform this type of report?

      1.

       

      SELECT

          StartTime.EventTime,

          Nodes.Estado,

          Nodes.Empresa,

          Nodes.Caption,

          StartTime.Message,

          DATEDIFF(Mi, StartTime.EventTime,

          (SELECT TOP 1

              EventTime

              FROM Events AS Endtime

              WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

                  AND EndTime.NetObjectType = 'N'

                  AND EndTime.NetworkNode = StartTime.NetworkNode

              ORDER BY EndTime.EventTime)) AS DurationInMinutes

      FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

      WHERE (EventTime BETWEEN 42123 AND 42132 ) -------->   This Month

      AND

      (

        (Nodes.Dispositivo = 'Roteador') AND

        (Nodes.Estado = 'Amazonas') AND

      (

         (EventType = 1) OR

         (EventType = 0))

      )

      ORDER BY Nodes.Caption ASC

       

      ----------------------------------------------------------------

       

      2.

       

      SELECT

          StartTime.EventTime,

          Nodes.Estado,

          Nodes.Empresa,

          Nodes.Caption,

          StartTime.Message,

          DATEDIFF(Mi, StartTime.EventTime,

          (SELECT TOP 1

              EventTime

              FROM Events AS Endtime

              WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

                  AND EndTime.NetObjectType = 'N'

                  AND EndTime.NetworkNode = StartTime.NetworkNode

              ORDER BY EndTime.EventTime)) AS DurationInMinutes

      FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

      WHERE (EventTime BETWEEN 42093 AND 42122.9999884259---------> Last Month

      AND

      (

        (Nodes.Dispositivo = 'Roteador') AND

        (Nodes.Estado = 'Amazonas') AND

      (

         (EventType = 1) OR

         (EventType = 0))

      )

      ORDER BY Nodes.Caption ASC

       

       

      Thanks!

        • Re: Report
          LadaVarga

          Hello, Which version of NPM do you use? From 10.7 we introduced WEb-based reporting. 1. Create web report with Custom Table with SQL datasource. 2. put this query to datasource. 3. Change that times to "${fromTime} and ${totime}" instead of "42093 AND 42122.9999884259". 4. Next to Custom table resource pick appropriate timeframe.