1 Reply Latest reply on Feb 20, 2017 9:24 AM by mcfobair

    NPM Alert History Advanced SQL Reporting (Time Coalesce / Group / Events)

    mcfobair

      How do we Create a report that uses the NPM 12 AlertHistoryView Table (instead of the Events Table) to show the TIME DIFFERENCE between a 'Trigger' and a 'Reset' of an Alert?

       

      Here are 2 completely different examples from showing SQL coding that I would need to change Tables and Fields. This gets the ball rolling for me.

      1)

      SELECT TOP 10000 * FROM

       

       

       

       

        (

        SELECT

                                      AlertActiveID,

                                      EntityNetObjectID as ENOid,

                                      EventTypeWord,

        StartTime.TimeStamp AS DownTime,

       

       

        (

        SELECT TOP 1 AlertHistoryID

        FROM AlertHistoryView AS EndTimeTable

        where EndTimeTable.TimeStamp >= StartTime.TimeStamp

        AND EndTimeTable.EventTypeWord = 'Triggered'

        AND EndTimeTable.AlertActiveID = StartTime.AlertActiveID

        AND TimeStamp IS NOT NULL

        ORDER BY EndTimeTable.TimeStamp

        )  AS DownID,

       

       

                                      (

        SELECT TOP 1 EventTypeWord

                                                      FROM AlertHistoryView AS EndTime

        where EndTime.TimeStamp > StartTime.TimeStamp

                                                      AND EndTime.EventTypeWord = 'Reset'

        AND EndTime.AlertActiveID = StartTime.AlertActiveID 

                                                      ORDER BY EndTime.TimeStamp

        )  AS UpMsg,

       

       

        (

        SELECT TOP 1 TimeStamp

                                                      FROM AlertHistoryView AS EndTime

        where EndTime.TimeStamp > StartTime.TimeStamp

                                                      AND EndTime.EventTypeWord = 'Reset'

        AND EndTime.AlertActiveID = StartTime.AlertActiveID 

                                                      ORDER BY EndTime.TimeStamp

        )  AS UpTime,

       

       

                                      (

        SELECT TOP 1 AlertHistoryID

                                                      FROM AlertHistoryView AS EndTime

        where EndTime.TimeStamp > StartTime.TimeStamp

                                                      AND EndTime.EventTypeWord = 'Reset'

        AND EndTime.AlertActiveID = StartTime.AlertActiveID 

                                                      ORDER BY EndTime.TimeStamp

        )  AS UpID,

       

       

       

        DATEDIFF(Mi, StartTime.TimeStamp,

        (

        SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime

        where EndTime.TimeStamp > StartTime.TimeStamp

                                                                      AND EndTime.EventTypeWord = 'Reset'

        AND EndTime.AlertActiveID = StartTime.AlertActiveID

                                                                     ORDER BY EndTime.TimeStamp

        )

        )  AS OutageMinutes,

       

       

                                     Cast(Coalesce(

            DATEDIFF(Mi, StartTime.TimeStamp,

        (

        SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime

        where EndTime.TimeStamp > StartTime.TimeStamp

                                                                      AND EndTime.EventTypeWord = 'Reset'

        AND EndTime.AlertActiveID = StartTime.AlertActiveID

                                                                     ORDER BY EndTime.TimeStamp

        )

            )      / 60.0000, 0 ) as float

                                      )  AS OutageHrs,

       

       

                                     floor( Cast(Coalesce(

            DATEDIFF(Mi, StartTime.TimeStamp,

        (

        SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime

        where EndTime.TimeStamp > StartTime.TimeStamp

                                                                      AND EndTime.EventTypeWord = 'Reset'

        AND EndTime.AlertActiveID = StartTime.AlertActiveID

                                                                     ORDER BY EndTime.TimeStamp

        )

            )      / 60, 0 ) as float)

                                      )  AS OH,

       

       

                                      Cast(Coalesce((

            DATEDIFF(Mi, StartTime.TimeStamp,

        (

        SELECT TOP 1 TimeStamp FROM AlertHistoryView AS EndTime

        where EndTime.TimeStamp > StartTime.TimeStamp

                                                                      AND EndTime.EventTypeWord = 'Reset'

        AND EndTime.AlertActiveID = StartTime.AlertActiveID

                                                                     ORDER BY EndTime.TimeStamp

        )

            )   % 60) , 0 ) as float

                                      )  AS OHM

       

       

        FROM AlertHistoryView StartTime

       

       

        WHERE (StartTime.EventTypeWord = 'Triggered' )

       

       

        ) AS UpTimeTable

       

       

        where OutageMinutes IS NOT NULL

       

       

        ORDER BY  AlertActiveID, DownTime DESC

       

      ---------------------------------------------------------------------- derived from ( Node Downtime Reports )

       

      2) NPM 11.5 AlertHistory (since there is no AlertHistoryView to work with in this version)

      I was able to do a successful subtract script. But, didn't have the right tables to at home to test with. Also, I never finished the group script.

      Here is one sample sql script I figured out. My NPM is version 11.5 so tables are different.

      You will have to change table name and column names.

      ****SELECT
      --  ThisLog.*,
      Thislog.TimeStamp,
      --  Distinct
        Convert(datetime, Floor(Cast((ThisLog.TimeStamp) as Float)),0) AS SummaryDate,
        DatePart(minute,COALESCE(ThisLog.TimeStamp - PrevLog.TimeStamp, 0)) as TimeDiff,
        ThisLog.AlertActiveID

      FROM [dbo].[AlertHistory]   AS ThisLog

      LEFT JOIN
        [dbo].[AlertHistory]   AS PrevLog
          ON  PrevLog.AlertActiveID   = ThisLog.AlertActiveID
          AND PrevLog.TimeStamp =
      (
      SELECT MAX(TimeStamp)
              FROM [dbo].[AlertHistory]
              WHERE
         AlertActiveID   = ThisLog.AlertActiveID
         and
         TimeStamp < ThisLog.TimeStamp   
      )

      where  ThisLog.AlertActiveID = '2'

       

      ****

      ref KB: sql coalesce cmd +

      https://msdn.microsoft.com/en-us/library/ms174420.aspx

       

      Message was edited by: Marc christian Fobair

        • Re: NPM Alert History Advanced SQL Reporting (Time Coelasce / Group / Events)
          mcfobair

          -- updated - node downtime for example 1

           

          SELECT * FROM

            (

            SELECT

            Nodes.StatusLED,

            Nodes.Caption,

            Nodes.NodeID,

            StartTime.Message,

            StartTime.EventTime AS DownEventTime,

            (

            SELECT TOP 1 EventTime

            FROM Events AS EndTimeTable

            where EndTimeTable.EventTime >= StartTime.EventTime

            AND EndTimeTable.EventType = 5

            AND EndTimeTable.NetObjectType = 'N'

            AND EndTimeTable.NetworkNode = StartTime.NetworkNode

            AND EventTime IS NOT NULL

            ORDER BY EndTimeTable.EventTime

            ) AS UpEventTime,

           

            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 OutageDurationInMinutes

           

            FROM Events StartTime

           

            INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

            WHERE (StartTime.EventType = 1)

            ) AS UpTimeTable

           

           

            where outageDurationInMinutes IS NOT NULL

           

           

            ORDER BY Caption ASC,

           

           

            DownEventTime DESC