Rotating Header Image

Look what I can do!! (w/Policy Based Management)

StuMafjdkf

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. :)

Happy Friday!!!

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

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

4 Comments

  1. SQLRockstar says:

    Glad we could help, but if you think you are going to steal PBM away from me you got another think coming.

  2. LOL can we set up some kind of a schedule where I take PBM on Mondays Wednesdays and Friday and you can have it Tuesdays and Thursdays?

    Seems fair :)

  3. [...] you have yet to explore Policy Based Management, PBM, get on it. I know that others have blogged about their experiences with it, and there’s a reason that people are excited. Track down Buck Woody’s session [...]

  4. Darren says:

    Hi
    I’ve started looking at Policy Based Mgmt and the custom example you provide here is great and provides other ideas. There is not too much on the web about PBM, I wonder how many people have started using.

    When I run your example I get an error ‘The Policy ‘Check SQLAgent jobs’ contains scripts. You should only run policies from a trustworthy source’. Have you come across this error?

    Many Thanks
    Darren

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.