集中管理SQL Server Event Logs

前很多工具是可以做到监视SQL Server Event log并且通知对应的人，但是都是需要钱的，通过下面的Code也可以实现相应的功能，而且不需要花老板的钱。

Step 1 - 创建数据库

----------------------------------- USE [ MASTER ] GO ----------------------------------- CREATE DATABASE [ DBA ] GO -----------------------------------

Step 2 - Create the Table

--用来放Event log信息

USE [DBA]GO-----------------------------------SET ANSI_NULLS ONGO-----------------------------------SET QUOTED_IDENTIFIERONGO-----------------------------------CREATETABLE [dbo].[EventLogStaging]([RecordNumber][int]NOT NULL,[Category][int]NOT NULL,[ComputerName][nvarchar](250)NOT NULL,[EventCode][int]NOT NULL,[EventType][int]NOT NULL,[Message][nvarchar](4000)NULL,[SourceName][nvarchar](250)NOT NULL,[TimeGenerated][datetime]NULL,[TimeWritten][datetime]NOT NULL)ON [PRIMARY]-----------------------------------GO

Step 3 - 创建VB Scitpt收集Event log 信息

strComputer ="."Set objConn= CreateObject("ADODB.Connection")Set objRS= CreateObject("ADODB.Recordset")objConn.Open"Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=DBA;Integrated Security=SSPI"objRS.CursorLocation= 3objRS.Open"SELECT * FROM EventLogStaging", objConn,3, 3' Get to the Event LogSet objWMIService= GetObject("winmgmts:" _& "{impersonationLevel=impersonate}!\\"& strComputer &"\root\cimv2")' get the events we wantquery ="Select * from __InstanceCreationEvent" _& " "& "where TargetInstance isa 'Win32_NTLogEvent'" _& " "& "and TargetInstance.Logfile = 'Application'" _& " "& "and (TargetInstance.EventType = 1 or TargetInstance.EventType = 2)" _& " "& "and (TargetInstance.SourceName like 'MSSQL%')"' get ready to insert into our DBA tableSet colMonitoredEvents= objWMIService.ExecNotificationQuery(query)DoSet objLatestEvent = colMonitoredEvents.NextEventobjRS.AddNewobjRS("RecordNumber")= objLatestEvent.TargetInstance.RecordNumber objRS("Category")= objLatestEvent.TargetInstance.Category objRS("ComputerName")= objLatestEvent.TargetInstance.ComputerName objRS("EventCode")= objLatestEvent.TargetInstance.EventCode objRS("EventType")= objLatestEvent.TargetInstance.EventType objRS("Message")= objLatestEvent.TargetInstance.Message objRS("SourceName")= objLatestEvent.TargetInstance.SourceName objRS("TimeGenerated")= WMIDateStringToDate(objLatestEvent.TargetInstance.TimeGenerated) objRS("TimeWritten")= WMIDateStringToDate(objLatestEvent.TargetInstance.TimeWritten) objRS.UpdateLoop' if we ever finish, we close cleanly.objRS.CloseobjConn.CloseSet objRS = NothingSet objConn= Nothing'******************************************************************************'* This conversion is necessary because WMI uses a different date/time format *'******************************************************************************Function WMIDateStringToDate(dtmInstallDate) WMIDateStringToDate= CDate(Mid(dtmInstallDate,5, 2) &"/" & _ Mid(dtmInstallDate,7, 2) &"/" & Left(dtmInstallDate,4) _& " "& Mid (dtmInstallDate,9, 2) &":" & _ Mid(dtmInstallDate,11, 2) &":" & Mid(dtmInstallDate, _13, 2))EndFunction

Step4: 创建SQL Agent job定期收集数据

USE[msdb]GO------------------------------------------------------BEGINTRANSACTIONDECLARE @ReturnCodeINTSELECT @ReturnCode= 0------------------------------------------------------IFNOT EXISTS(SELECT nameFROM msdb.dbo.syscategoriesWHERE name=N'[Uncategorized (Local)]'AND category_class=1)BEGINEXEC @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'Monitor Event Log', @enabled=1,@notify_level_eventlog=0,@notify_level_email=0,@notify_level_netsend=0,@notify_level_page=0,@delete_level=0,@description=N'No description available.',@category_name=N'[Uncategorized (Local)]',@owner_login_name=N'sa',@job_id = @jobId OUTPUTIF(@@ERROR <>0 OR @ReturnCode<> 0)GOTO QuitWithRollback------------------------------------------------------EXEC @ReturnCode= msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'always running',@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'CmdExec',@command=N'cscript "E:\Monitor\EventLog2DB.vbs"',@flags=0IF(@@ERROR <>0 OR @ReturnCode<> 0)GOTO QuitWithRollbackEXEC @ReturnCode= msdb.dbo.sp_update_job @job_id= @jobId, @start_step_id= 1IF(@@ERROR <>0 OR @ReturnCode<> 0)GOTO QuitWithRollbackEXEC @ReturnCode= msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'always',@enabled=1,@freq_type=64,@freq_interval=0,@freq_subday_type=0,@freq_subday_interval=0,@freq_relative_interval=0,@freq_recurrence_factor=0,@active_start_date=20100831,@active_end_date=99991231,@active_start_time=0,@active_end_time=235959IF(@@ERROR <>0 OR @ReturnCode<> 0)GOTO QuitWithRollbackEXEC @ReturnCode= msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'check every 1 minute',@enabled=1,@freq_type=4,@freq_interval=1,@freq_subday_type=4,@freq_subday_interval=1,@freq_relative_interval=0,@freq_recurrence_factor=0,@active_start_date=20100901,@active_end_date=99991231,@active_start_time=0,@active_end_time=235959IF(@@ERROR <>0 OR @ReturnCode<> 0)GOTO QuitWithRollbackEXEC @ReturnCode= msdb.dbo.sp_add_jobserver @job_id= @jobId, @server_name= N'(local)'IF(@@ERROR <>0 OR @ReturnCode<> 0)GOTO QuitWithRollbackCOMMITTRANSACTIONGOTO EndSaveQuitWithRollback:IF (@@TRANCOUNT> 0)ROLLBACK TRANSACTIONEndSave:---------------------------------------------GO

Step5.测试：

打开SSMS 运行下面的语句：

raiserror ('working great',16,1) with log

这条语句会在Application Log产生一个event .

检查可以看到Event已经放到监控表了。

SELECT * FROM [DBA].[dbo].[EventLogStaging]order by TimeWritten desc