Search This Blog

Friday, November 20, 2009

Creating SQL Server Alerts using WMI classes- Part 1

When we create alerts or when we try to create script we search net a lot .We get some thing but we do not get all what we look for .And then we stop pursuing it ...

I was given a task to create SQl Server alerts (as many as I can).I could find a couple of scripts ready but that did not satisfy my requirement as everyone creats the scripts as per their needs .

So I decided to do everything on my own and hence sharing my experiance with you .
To know what kind of alerts you can create go through this file



You will be amazed to see that almost everything is covered here :-) ..

Let us start now by creating an alert that will notify us whenever a new database is created .

Pre-requsites :

-> Need to be SQL Server 2005 SP2 or above or SQL Server 2008 as there is a bug in RTM or SP1 .

-> We need to execute it in master (do not change the script to be run in your user database).This is because Master captures the metadata of all the databases including itself .If you create it in anyother database then it will not work .Other scripts will also work only for that user database .

-> Make sure the SQL Server Agent is started and make sure that "REPLACE TOKENS FOR ALL JOB RESPONSES TO ALERTS" option is checked under Agent properties -> Alert system .

-> Read the code a few times and understand it .You might have to change it as per your need .


CODE :
Part 1:


/*******************************************************************************************
* This script will create an Alert to Monitor Create DATABASE event
* The alert will run a job and the job will enter data in a table.
*******************************************************************************************/


/* Step 1: creating the table to capture the Event information */

USE Master
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Create_database]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Create_database]
GO

CREATE TABLE [dbo].[Create_database] (
[PostTime] [datetime] NOT NULL ,
[Instance] varchar(20),
[DatabaseName] varchar(20),
[computerName] Varchar(20),
[Loginname] Varchar(20),
[TSQLCommand] varchar (1000),
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_Create_database_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

CREATE INDEX [Create_database_IDX01] ON [dbo].[Create_database]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the table created above*/
/*Service account and sql operator option are optional and hence commented*/

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Create_Database Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Create_Database Event', @delete_unused_schedule=1

GO
--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Create_Database Event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to Create_database events',
@category_name=N'[Uncategorized (Local)]',
--@owner_login_name=@ServiceAccount,
--@notify_email_operator_name=@SQLOperator,
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO Create_database (
PostTime,
Instance,
DatabaseName,
Computername,
LoginName,
TSQLCommand
)

VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
N''$(ESCAPE_NONE(WMI(DatabaseName)))'',
N''$(ESCAPE_NONE(WMI(ComputerName)))'',
N''$(ESCAPE_NONE(WMI(Loginname)))'',
N''$(ESCAPE_NONE(WMI(TSQLCommand)))''
)',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/*Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Create_database')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Create_database'

GO

DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to Create_database',
@enabled=1,
@notification_message=N'Your Message',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM Create_database',
@job_name='Capture Create_Database Event' ;

EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to Create_database', @operator_name=N'Test', @notification_method = 1
GO



--select * from master..Create_database


How it works :

As soon as the database is created WMI class create_database will generate an event and trigger the alert through SQL Server agent .The alert will in turn execute the job that will insert the event information to the table we have created .


Part 2:
This part will send a mail to the desired user that an alert has been fired .
This can be achieved by :

1) Using Database mail (including creating an operator)and choosing the option in the job itself for notifying the operator .

2) Using custom based CDO message via VBscript and execute it using XP_cmdshell as a job step.

Mail.vbs :
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "abhay.chaudhary@in.ibm.com"
objEmail.To = "abhay.chaudhary@in.ibm.com"
objEmail.Subject = "Mail for you using CDO :-) "
objEmail.Textbody = "Hi sir this mail has been sent without using SQL Server and from WMI script."
objEmail.AddAttachment "C:\Script repository\Alerts\final Alerts\readme.txt"
objEmail.Send


3) Using the Stored procedure below :
/* Step 5: Create a stored proc for sending the [Create_database] information as .CSV file */

Create proc [dbo].[Create_database_rpt]
as
DECLARE @SQL varchar(2000)
DECLARE @date varchar (2000)
DECLARE @File varchar(1000)
select @date= convert(date,GETDATE())
SET @SQL = 'select * from [Create_database] where flag = 0'
SET @File = 'Deadlock report'+@date+'.csv'

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'your email.com',
@subject = '[Create_database] report',
@body = '***URGENT***Attached please find the Create Database report',
@query =@SQL ,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file,
@query_result_header = 1,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_width = 32767

/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
update dbo.[Create_database] set flag = 1 where flag = 0
go

Like this Try your hands on different alerts by going through the classes copied above .You can also go through the classes at MSDN :http://msdn.microsoft.com/en-us/library/ms186449(SQL.90).aspx

Hope this helps .I am also learning ropes on WMI and will post some good stuff as soon as I am ready .Keep reading as there is much more to come .

Happy learning .

2 comments:

Unknown said...

Tried this and failed with the following:
Date 3/17/2011 8:31:15 AM
Log Job History (Capture Create_Database Event)

Step ID 1
Server DNRDENSQLMOSPRD
Job Name Capture Create_Database Event
Step Name Insert data into LogEvents
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Unable to start execution of step 1 (reason: Variable WMI(SQLInstance) not found). The step failed.

Abhay said...

Hi William ,
In the agent properties >> Alert system >> have you checked the option "Replace tokens for all job responses to alerts" ??
If no, do that and restart the agent .Still if you have issues mail me at 1978abhay@gmail.com and I will send you ample of WMI scripts including this one ..

I am sure with this option checked , everything will be fine ..

Regards
Abhay