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
