Repetitive Email Alerts (Noise) - MUST HAVE REPORT!!!

< BACK TO TO THE MAGIC OF SQL SCRIPTS SERIES LIST

My Dear Thwackers,

If you are sick and tired of having to deal with continuous noise from hundreds of alerts and customer complaints due to missed problems - you must have this report, now!

Fact: 80% of your alerts and frustrations come from 20% of stuff within your infrastructure!


It is quite obvious that most of the negative impact on performance and productivity within the NOC is being caused by noise and repetitive alerts that is just keep coming through... non-stop. Ability to highlight them in a most efficient manner has never been easy, until now emoticons_grin.png

I am here to offer you a gift - a custom made report that has served us for years in attacking and eliminating all the noise. It is compatible with Orion Platform 2015.1.2, NPM 11.5+ (also works with latest NPM 12)

Download > Import > Run > Action

... and just for fun - please publish a screenshot in the comments below of the top 5 stats that you will get - it is very interesting to know how this will play out in your environment emoticons_wink.png

pastedImage_0.png

To your monitoring success,

Alex Soul

[UPDATES]

[30/08/2017]: Below we have discussed some bug fixes around extracting EmailTo from description. In the attached report this workaround was implemented, so, it should work for you straight out-of-the-box

[31/08/2017]: I have added % column, which will calculate percentage from total umber of email alerts sent over the defined period. I have also updated they way variables are being defined, so, you can now just open SQL script and change them all in one place defined in SET function on top of the script

pastedImage_2.png

pastedImage_3.png

[04/09/2017]: As suggested by tdanner​ - there is a better way of extracting EmailTo and EmailCC. This has been implemented in version 1.3 of the report

Anonymous
Parents
  • I like this report you can also look at it in another perspective on the node details page. emoticons_happy.png it will need to be stuck in to a report writer first

    pastedImage_0.png

    SQL:

    SELECT

    'Small-Relative-2.gif'

       ,ahv.Name AS 'AlertName'

      ,ahv.EntityCaption AS 'ObjectName'

        ,COUNT(*) AS 'Total'

    ,n.NodeID

      ,CASE

       WHEN DATEDIFF(DAY, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp)) > 1

       THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp))) + ' days'

       ELSE CASE

         WHEN DATEDIFF(HOUR, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp)) > 1

         THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp))) + ' hours'

         ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp))) + ' min'

        END

       END AS 'Over'

      ,MIN(ahv.TimeStamp) AS 'FirstInstance'

      ,MAX(ahv.TimeStamp) AS 'LastInstance'

    --,n.n_type

    FROM AlertHistoryView ahv

    LEFT JOIN ActionsAssignments act WITH(NOLOCK) ON act.ActionID = ahv.ActionID

    LEFT JOIN AlertStatusView asv WITH(NOLOCK) ON asv.AlertObjectID = ahv.AlertObjectID

    LEFT JOIN Nodes n WITH(NOLOCK) ON n.NodeID = ahv.RelatedNodeID

    WHERE

    n.NodeID = ${NodeID} AND

      DATEDIFF(DAY, ahv.TimeStamp, getUTCdate()) < 30 AND --pull from last X days

      ahv.ActionTypeID = 'Email' AND

      act.CategoryType = 'Trigger' AND

      (

    ahv.EventTypeWord = 'ActionSucceeded' OR

    ahv.EventTypeWord = 'ActionFailed'

      )

    GROUP BY  ahv.Name, n.NodeID,EntityCaption

    HAVING

      COUNT(*) > 2 AND --repeats more than Y times

      DATEDIFF(DAY, MAX(ahv.TimeStamp), getUTCdate()) < 7 --last alert is not older than Z days (fresh otherwise)

    ORDER BY COUNT(*) desc

Comment
  • I like this report you can also look at it in another perspective on the node details page. emoticons_happy.png it will need to be stuck in to a report writer first

    pastedImage_0.png

    SQL:

    SELECT

    'Small-Relative-2.gif'

       ,ahv.Name AS 'AlertName'

      ,ahv.EntityCaption AS 'ObjectName'

        ,COUNT(*) AS 'Total'

    ,n.NodeID

      ,CASE

       WHEN DATEDIFF(DAY, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp)) > 1

       THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp))) + ' days'

       ELSE CASE

         WHEN DATEDIFF(HOUR, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp)) > 1

         THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp))) + ' hours'

         ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, MIN(ahv.TimeStamp), MAX(ahv.TimeStamp))) + ' min'

        END

       END AS 'Over'

      ,MIN(ahv.TimeStamp) AS 'FirstInstance'

      ,MAX(ahv.TimeStamp) AS 'LastInstance'

    --,n.n_type

    FROM AlertHistoryView ahv

    LEFT JOIN ActionsAssignments act WITH(NOLOCK) ON act.ActionID = ahv.ActionID

    LEFT JOIN AlertStatusView asv WITH(NOLOCK) ON asv.AlertObjectID = ahv.AlertObjectID

    LEFT JOIN Nodes n WITH(NOLOCK) ON n.NodeID = ahv.RelatedNodeID

    WHERE

    n.NodeID = ${NodeID} AND

      DATEDIFF(DAY, ahv.TimeStamp, getUTCdate()) < 30 AND --pull from last X days

      ahv.ActionTypeID = 'Email' AND

      act.CategoryType = 'Trigger' AND

      (

    ahv.EventTypeWord = 'ActionSucceeded' OR

    ahv.EventTypeWord = 'ActionFailed'

      )

    GROUP BY  ahv.Name, n.NodeID,EntityCaption

    HAVING

      COUNT(*) > 2 AND --repeats more than Y times

      DATEDIFF(DAY, MAX(ahv.TimeStamp), getUTCdate()) < 7 --last alert is not older than Z days (fresh otherwise)

    ORDER BY COUNT(*) desc

Children
No Data