I am in a situation where I have to incorporate SQL Server Agent alerts in my monitoring and alerting strategy.
I needed a query (DMV) to get details on what alerts are setup on each server. And the result is the following query that I will run as a multi-server query.
SELECT a.[id] [alert_id],
a.[name] [alert_name],
a.[enabled] [is_alert_enabled],
o.[enabled] [is_operator_enabled],
o.[email_address] [email_address],
o.[pager_address] [pager_address],
o.[netsend_address] [netsend_address],
j.[name] [job_name],
a.[event_source] [alert_event_source],
a.[event_category_id] [alert_event_category_id],
sc.[name] [alert_category_name],
CASE sc.[category_class]
WHEN 1 THEN 'JOB'
WHEN 2 THEN 'ALERT'
WHEN 3 THEN 'OPERATOR'
ELSE '0'
END [alert_class_name],
sm.[description] [alert_message_description],
a.[event_id] [alert_event_id],
a.[message_id] [alert_message_id],
a.[severity] [alert_severity],
a.[enabled] [alert_enabled],
a.[delay_between_responses] [alert_delay_between_responses],
a.[last_occurrence_date] [alert_last_occurrence_date],
a.[last_occurrence_time] [alert_last_occurrence_time],
a.[last_response_date] [alert_last_response_date],
a.[last_response_time] [alert_last_response_time],
a.[notification_message] [alert_notification_message],
a.[include_event_description] [alert_include_event_description],
a.[database_name] [alert_database_name],
a.[event_description_keyword] [alert_event_description_keyword],
a.[occurrence_count] [alert_occurrence_count],
a.[count_reset_date] [alert_count_reset_date],
a.[count_reset_time] [alert_count_reset_time],
a.[job_id] [alert_job_id],
a.[has_notification] [alert_has_notification],
a.[flags] [alert_flags],
a.[performance_condition] [alert_performance_condition],
a.[category_id] [alert_category_id]
FROM msdb.dbo.sysalerts a
LEFT OUTER JOIN msdb.dbo.syscategories sc ON a.category_id = sc.category_id
LEFT OUTER JOIN msdb.dbo.sysnotifications sn ON ( a.id = sn.alert_id )
LEFT OUTER JOIN msdb.dbo.sysoperators o ON ( o.id = sn.operator_id )
LEFT OUTER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
LEFT OUTER JOIN msdb.dbo.sysmessages sm ON sm.error = a.message_id
and sm.msglangid = SERVERPROPERTY('LCID')
ORDER BY 1And here is the sample result:
