Notifications on Jobs

Have you ever been in a situation where you started to create this masterful script only to find that it was already out and available to begin with. I think that is one of the biggest advantages within the SQL community; everyone really seems to try to help each other out by publishing items, sharing knowledge on issues and solutions, pointing others in the right direction. In saying that I recently wanted to find every job on every server that did not have a notification; simple enough task. As I sat down and began to write the code out I began to read through some blogs and I came across one that was already doing the same thing that I had just started on. Jonathan (a.k.a. FatherJack) wrote an article based on finding notification settings on SQL jobs. After review I ended up tweaking the script somewhat to only pull jobs I had enabled at the time and also to only order by the actual email alert level. Below is the tweaked script (does not include the only enabled filter):

SELECT  [j].[name], 
        CASE WHEN[j].[notify_level_eventlog]=1THEN'On success' 
            WHEN[j].[notify_level_eventlog]=2THEN'On failure' 
            WHEN[j].[notify_level_eventlog]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Event Log Entry], 
        CASE WHEN[j].[notify_level_page]=1THEN'On success' 
            WHEN[j].[notify_level_page]=2THEN'On failure' 
            WHEN[j].[notify_level_page]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Page Alert], 
        CASE WHEN[j].[notify_level_netsend]=1THEN'On success' 
            WHEN[j].[notify_level_netsend]=2THEN'On failure' 
            WHEN[j].[notify_level_netsend]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Net Send Alert], 
        CASE WHEN[j].[notify_level_email]=1THEN'On success' 
            WHEN[j].[notify_level_email]=2THEN'On failure' 
            WHEN[j].[notify_level_email]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Email Alert] 
FROM    [dbo].[sysjobs] AS j 
ORDER BY [j].[notify_level_email] DESC

I give props to Jonathan and to view his script along with an additional script that shows operators you can click here

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s