If you’ve never watched Stuart from MadTV the Title of this blog post will probably be lost on you but if you want to get up to speed just Watch This Video on YouTube
So if you read my blog (first thanks!! 🙂 ) you know that I’ve been having a love affair lately with Policy Based Management. I’m starting to look outside of the canned Policies and working on creating my own. I wanted to start simple just to get something going so I came up with an idea for a policy:
Jobs That Do Not Have Email Operator On Failure Enabled
So first I created my Condition:
- Name: SQLAgentJobsWithNoNotificationOnFailure
- Facet: Server (I picked server because the query I’m going to be only needs to be ran once vs msdb)
I built my SQL Statement:
SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE [enabled] = 1 AND notify_level_email != 2
and went to add it to the “Field” column of my condition but I remembered the Podcast by Brent Ozar and Thomas LaRock where they demoed the ExecuteSQL() function within Policy Based Management.
So my code in my field column ended up looking like this instead:
EXECUTESQL('numeric', 'SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name NOT LIKE ''%TestDatabaseMail%'' AND [enabled] = 1 AND notify_level_email != 2')
So then I populated the expected value…. = 0 and I was ready to go.
I next created my Policy using my new Check condition and voila, I just built my first custom Policy. I evaluated the Policy and saw immediate value as there were a couple production jobs that did NOT have any email notification set on failure.
My love affair with Policy Based Management continues… I think I’m ready to commit and start picking out china together. 🙂
P.S. Already working on v.2 of the policy… I want to be able to filter particular jobs as well as return the list of non conforming jobs 🙂 stay tuned LOL