集中管理SQL Server Event Logs

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

Step 1 - 创建数据库

-----------------------------------USE[MASTER]GO-----------------------------------CREATEDATABASE[DBA]GO-----------------------------------

Step 2 - Create the Table

--用来放Event log信息

USE [DBA]GO-----------------------------------SET ANSI_NULLS ONGO-----------------------------------SET QUOTED_IDENTIFIERONGO-----------------------------------CREATETABLE[dbo].[EventLogStaging]([RecordNumber][int]NOTNULL,[Category][int]NOTNULL,[ComputerName][nvarchar](250)NOTNULL,[EventCode][int]NOTNULL,[EventType][int]NOTNULL,[Message][nvarchar](4000)NULL,[SourceName][nvarchar](250)NOTNULL,[TimeGenerated][datetime]NULL,[TimeWritten][datetime]NOTNULL)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------------------------------------------------------IFNOTEXISTS(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<>0OR @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<>0OR @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<>0OR @ReturnCode<>0)GOTO QuitWithRollbackEXEC @ReturnCode= msdb.dbo.sp_update_job @job_id= @jobId, @start_step_id=1IF(@@ERROR<>0OR @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<>0OR @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<>0OR @ReturnCode<>0)GOTO QuitWithRollbackEXEC @ReturnCode= msdb.dbo.sp_add_jobserver @job_id= @jobId, @server_name= N'(local)'IF(@@ERROR<>0OR @ReturnCode<>0)GOTO QuitWithRollbackCOMMITTRANSACTIONGOTO EndSaveQuitWithRollback:IF(@@TRANCOUNT>0)ROLLBACKTRANSACTIONEndSave:---------------------------------------------GO

Step5.测试：

打开SSMS 运行下面的语句：

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

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

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

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