Query For Alert History

cehottle

Do you any queries available to use against the database. I'd be interested in queries for alerts. I put together this one, but the TargetObject column is not very intuitive and if you have any way to parse that, it would be helpful.

SELECT A.[AlertId]
--,utils.TicksToDateTime([Date]) AS AlertDateUTC
,ServerAudit.[dbo].[ufnConvertUtcToLocal](utils.TicksToDateTime([Date])) AS AlertDateLocal
,T.[Event]
,[Severity]
,T.Name AS AlertName
,SUBSTRING(TargetObject,
(CHARINDEX('4:Name,s', TargetObject, 1) + 11),
(CHARINDEX('sql', TargetObject, 1) + 5) - (CHARINDEX('4:Name,s', TargetObject, 1) + 11)) AS ServerName
,[TargetObject]
FROM [RedGateMonitor].[alert].[Alert_Severity] Sev
INNER JOIN
[RedGateMonitor].[alert].[Alert] A
ON A.AlertId = Sev.AlertId
INNER JOIN
[RedGateMonitor].[alert].[Alert_Type] T
ON T.AlertType = A.AlertType
WHERE [Severity] > 0
ORDER BY A.[AlertId] DESC

Adam W

The free SQL Monitor SSRS reporting pack has a report called Alerts.rdl which the lists the number of alerts by server and/or alert type.

Give it a go and let us know what you think!

http://www.red-gate.com/products/dba/sql-monitor/ssrs-pack

Thanks
Adam