集中管理SQL Server Event Logs

693579551

前很多工具是可以做到监视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]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