NPM 12 Report AlertHistory Downtime Minutes between Trigger and Reset

Version 1

    Created by Marc christian Fobair on Feb 18, 2017 11:17 PM. Last modified by Marc christian Fobair on Feb 18, 2017 11:17 PM.

    NPM ALERT HISTORY ADVANCED SQL REPORTING (TIME COALESCE / GROUP / EVENTS)

    This question is Assumed Answered.

    Marc christian FobairLevel 9

    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

    Content tagged with npm

    Content tagged with report alerthistoryview

    Content tagged with outage calculator