Version 1

    In order to gain confidence by the users of Orion, you need to limit the amount of alerts they receive.   Too many alert emails, and people will start ignoring the messages instead of proactively assessing the situation.  Too little alert emails, and you could miss a potential issue and it might escalate into a fire.

     

    Here are a few scripts I use to let me know what's going on in the client's environment.  These are base scripts which can be modified to meet the needs of the client.

     

    Alert Count (today)

    In this script, I have listed the alert and a count.  This gives me an idea of how frequently an alert is triggering and lets me tune the thresholds.  Generally, I will create a report for today, yesterday, and last 7 days.

    select ac.Name as Alert, count(*) as Count

    from AlertConfigurations ac

    join AlertObjects ao on ao.AlertID = ac.AlertID

    where (DateDiff(dd,dateadd(mi,datediff(mi,getutcdate(), getdate()), ao.LastTriggeredDateTime), getDate())) = 0

    group by ac.Name

    order by 2 desc

     

    Alert History (top 20)

    In this script, a simple listing of the events for the client.  This can help in troubshooting an alert.  Is it really doing what you thought it is supposed to be doing?

    SELECT top 20

    convert(varchar, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), ahv.timestamp), 20) as time

    ,ahv.name as Alert_Name, ahv.EntityCaption, ahv.ActionTypeID as Action --,left(Message,599) as Action

    FROM AlertHistoryview ahv

    where ahv.ActionTypeID is not null and

       (DateDiff(dd,dateadd(mi,datediff(mi,getutcdate(), getdate()), ahv.TimeStamp), getDate())) = 0

    order by time desc

     

     

    Alert Email Status Count (today)

    In this script, I have listed the alert and if the email was successful or failed by the hour.  Very useful, since some of the alerts are active by default - but the email address is not configured.

    select  datepart(hour, dateadd(mi,datediff(mi, getutcdate(), getdate()), ah.timestamp)) as localHour

    ,ac.name as Name, count(*) as 'Count', 'Success' as 'Email Satus'

    from Alerthistory ah

    join AlertObjects ao on ao.AlertObjectID = ah.AlertObjectID

    join AlertConfigurations ac on ao.AlertID = ac.AlertID

    where (DateDiff(dd,dateadd(mi,datediff(mi,getutcdate(),getdate()),ah.timestamp),getDate())) = 0

       and (ah.Message like '%email%' and ah.Message not like '%errormessage%')

    group by datepart(hour, dateadd(mi,datediff(mi, getutcdate(), getdate()), ah.timestamp)), ac.name

    union all

    select  datepart(hour, dateadd(mi,datediff(mi, getutcdate(), getdate()), ah.timestamp)) as localHour

    ,ac.name as Name, count(*) as 'Count', 'Failed' as 'Email Satus' from Alerthistory ah

    join AlertObjects ao on ao.AlertObjectID = ah.AlertObjectID

    join AlertConfigurations ac on ao.AlertID = ac.AlertID

    where (DateDiff(dd,dateadd(mi,datediff(mi,getutcdate(),getdate()),ah.timestamp),getDate())) = 0

       and (ah.Message like '%email%' and ah.Message like '%errormessage%')

    group by datepart(hour, dateadd(mi,datediff(mi, getutcdate(), getdate()), ah.timestamp)), ac.name

    order by 1 asc

     

    btw/We need to to aware that the alert table stores datatime as UTC, so appropriate conversion is needed.

     

    Thanks

    Amit Shah

    Loop1 Systems