Tuesday, December 14, 2010

Capture SQL Server Agent WMI Event Alerts with SQL Sentry Event Manager

SQL Sentry Event Manager is able to monitor SQL Server Agent Alerts and react to them out of the box. One limitation with that functionality creeps up when dealing with Agent alerts that are set to fire off of WMI events. While WMI event based alerts aren’t what I would call common place, they do make it easy to do some interesting things with an Agent alert that might normally take a bit more customization to do.
One thing that I know we can alert on using WMI events is when a database is dropped, so to keep the scope of this post small we’ll be using that for our Agent alert.
The first thing you want to do is create the alert using SQL Server Management Studio (SSMS from now on). In the SSMS Object Explorer look for the SQL Server Agent node, then expand it until you see the folder named “Alerts”. Right click the “Alerts” folder and select “New Alert…”. You should get a dialogue similar to the one below:
image
First give the Alert a name. You can call it whatever you want, but try and keep it simple, because we’re going to use this name someplace else later, and the simpler it is, the easier it will be to use. I simply named mine for the purpose it will serve “Database Dropped”.
Under Type select “WMI event alert”. Namespace should default to “\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER”, but if it doesn’t you want to enter that for the Namespace field. Last, under Query, enter “SELECT * FROM DROP_DATABASE”.
In very short terms, we’re telling SQL Server to run an asynchronous query against the SQL Server WMI Provider that will return a row when someone executes a “DROP DATABASE” command. It’s not my goal here to get too far into the subject of WMI or even the SQL Server WMI Provider for Server Events, but you can find a wealth of information on it at http://msdn.microsoft.com/en-US/library/ms180560(v=SQL.100).aspx.
Now, just click ok, and leave SSMS running. The alert will actually be fired as is, but what we’re going to do is create a custom alert trap that will feed SQL Sentry enough information to display this alert on a calendar and perform actions when conditions are met involving the alert. We’ll come back in a bit, and do one more thing to it, but for now it is configured the way we want.
The way we’re going to have the Agent alert tell SQL Sentry all about the event is with an Agent job. An alert already watched by SQL Sentry uses the SQL Sentry Alert Trap Job that is maintained by SQL Sentry. We’re going to create a special alert trap job, just for our alert, that does some of the same things the default SQL Sentry alert trap does, but provides some different information. The most important piece of information is going to be the name of the Agent alert that this event data is from.
So, back in SSMS I create a new job called “Database Dropped Alert Trap”. I give the job one “Transact-SQL script” step and drop the following code into the "Command field:
set quoted_identifier off

 

begin tran

 

declare @msg varchar(256)

 

set @msg = 'Database [' + '$(ESCAPE_SQUOTE(WMI(DatabaseName)))' + '] was dropped by [' + '$(ESCAPE_SQUOTE(WMI(LoginName)))' + ']  from computer [' + '$(ESCAPE_SQUOTE(WMI(ComputerName)))' + ']'

 

exec msdb.dbo.spTrapAlert_20 '50000', '0','$(ESCAPE_SQUOTE(WMI(DatabaseName)))', @msg

 

update msdb.dbo.SQLSentryAlertLog_20 

set AlertName = 'Database Dropped' 

where ID = @@IDENTITY

 

commit tran
The above code may look a little complicated, but all we’re really doing is grabbing some basic information from the WMI event row. This is provided to us by SQL Server Agent, and is accessed using the bits formatted like “(WMI(XXX)))”. Those have to be escaped a certain way, thus the bits that look like “$(ESCAPE_SQUOTE”. Then we’re using the stored procedure SQL Sentry uses to add the alert event to the alert log table in MSDB. Finally, we update the most recently added row for Alerts giving it the name of our Alert (in my case ‘Database Dropped’), so that the SQL Sentry polling service can identify and associate the object that generated the event data. This all happens inside of a short transaction to make sure it all runs as a singular operation.
Don’t create any schedule for this job. Remember, it will be run as the target for our Agent alert, so no schedule is necessary.
For the next part, we need to perform the steps in order, so I will number them.
1.) Use the SQL Sentry console application to ensure that the new SQL Server Agent Alert (for me it’s named “Database Dropped”) is watched by SQL Sentry Event Manager.
2.) Back in SSMS, right click the agent alert and select properties.
3.) In the alert properties dialogue, select “Response” from the left side panel. If you have done everything to this point, it should look like the image below:
image
4.) In the drop-down under “Execute job” select the Agent job that we created special for this alert:
image
5.) Click ok.
That is all there is to it. Using the code from above for our job step, whenever someone drops a database from this server, the alert should fire and you will get an event in SQL Sentry telling you what database was dropped, what login executed the drop command, and what machine it was executed from. You can use the standard SQL Sentry Event Manager conditions and actions off of this alert, including Send Email and Send Page to be notified proactively.
You can experiment with other WMI events as well such as CREATE or DROP LOGIN. The WMI query is what needs to change, as well as some of the WMI properties gathered in the alert trap job step.
If anyone out there wants some assistance getting this set up, or if you just have questions, head over to forum.sqlsentry.net and post a new thread or just email us at support at sqlsentry.net.
Until next time,
-JRH