March 1, 2018 at 2:54 pm
Team-
Seems like this should be an easy one.
What might be the SQL to list all of the Alerts on my server where 'Notify operators' has no entry?
The example alerts would be
'Peer-to-peer conflict attention alert'
...
'Replication: agent failure'
...
'Replication: Subscription reinitialized after validation failure'
Basically, all Alerts on my server notify no one - so I wanted to add a weekly report listing any alerts where we have no coverage.
TIA,
SQLOzzie
March 1, 2018 at 2:59 pm
Ok - Playing around I can see that
SELECT *
FROM msdb.dbo.sysalerts
WHERE has_Notification = 0
lists all of the alerts with no coverage. What is the JOIN to operators to see who will be notified via E-mail, Pager or Net Send?
TIA,
SQLOzzie
March 1, 2018 at 3:26 pm
Douglas Osborne-229812 - Thursday, March 1, 2018 2:59 PMOk - Playing around I can see thatSELECT *
FROM msdb.dbo.sysalerts
WHERE has_Notification = 0
lists all of the alerts with no coverage. What is the JOIN to operators to see who will be notified via E-mail, Pager or Net Send?
TIA,
SQLOzzie
Look at dbo.sysnotifications in msdb.
Sue
March 2, 2018 at 7:09 am
So here are my final scripts, since no one seems to ever post the 'final' result
SQL Agent Jobs where you are not configured to receive email
-- Jobs not assigned to you
SELECT DISTINCT 'Misconfigured SQL Agent job(s) operators' AS Warning, SJ.name AS JobName, SO.Name, SO.email_address
FROM msdb.dbo.sysjobs AS SJ
LEFT OUTER JOIN msdb.dbo.sysoperators AS SO ON SJ.notify_email_operator_id = SO.id
LEFT OUTER JOIN msdb.dbo.sysnotifications AS SN ON SO.id = SN.operator_id
WHERE SO.Name IS NULL OR SO.email_address <> 'You@Business.com'
--SJ.enabled = 1 -- Job is enabled
--AND SJ.notify_level_email NOT IN (1, 2, 3) -- Figure out what this is
ORDER BY SJ.name ASC;
Alerts where you are not configured to receive email
-- Alerts with notification for me
SELECT SA.Name, SO.email_address
FROM msdb.dbo.sysalerts AS SA
LEFT OUTER JOIN msdb.dbo.sysnotifications AS SN ON SA.ID = SN.alert_id
LEFT OUTER JOIN msdb.dbo.sysoperators AS SO ON SN.operator_id = SO.id
WHERE SO.email_address IS NULL OR SO.email_address <> 'You@Business.com' AND NOT EXISTS
(
SELECT 1
FROM msdb.dbo.sysalerts AS A1
INNER JOIN msdb.dbo.sysnotifications AS N1 ON A1.ID = N1.alert_id
INNER JOIN msdb.dbo.sysoperators AS O1 ON N1.operator_id = O1.id
WHERE O1.email_address = 'You@Business.com' AND A1.Name = SA.name )
ORDER BY SA.Name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply