将 Windows 事件日志错误加载到 SQL 表中

不念不忘少年蓝@ 2023-09-28 18:23 45阅读 0赞

成为积极主动的 DBA 的一部分是获取有关服务器级别和 SQL 级别正在发生的事情的信息。我们知道 Windows 事件日志已经存在，但我发现没有多少 DBA 定期检查它们。您可以通过使用 vbscript 将事件日志中的错误和警告加载到 SQL 表中来简化此操作。

至少查看或抓取事件日志，以确定是否写入了任何与 Windows 或硬件相关的错误或警告。大多数硬件供应商在预计会发生错误时会向事件日志写入警告，因此这使您有机会在计划的停机时间内主动纠正问题，而不是在中午发生紧急情况。

构建一个 SQL 表来保存事件日志信息：

— table for the Windows Event Log
    
    CREATE TABLE [dbo].[WinEventLog](
    
    [ID] INT IDENTITY(1,1) NOT NULL,
    
    [ComputerName] VARCHAR(128) NULL,
    
    [EventCode] INT NULL,
    
    [RecordNumber] INT NULL,
    
    [SourceName] VARCHAR(128) NULL,
    
    [EventType] VARCHAR(50) NULL,
    
    [WrittenDate] DATETIME NULL,
    
    [UserName] VARCHAR(128) NULL,
    
    [Message] VARCHAR(MAX) NULL
    
    ) ON [PRIMARY]
    
    GO

使用下面的 vbscipt 代码，将其保存到扩展名为 .vbs 的记事本文档中。确保修改连接字符串以连接到您的实例和正确的数据库。并且，设置您想要检查的天数。我将其默认为 2 天，但您可以将其设置为对您有意义的任何内容。

************************
    
    ‘VBScript
    
    ‘Purpose of script to query Application log for errors
    
    dim strConnect, strComputer, strMessage, RoleStr
    
    dim Category, Computer_Name, Event_Code, Message, Record_Number, Source_Name, Time_Written, Event_Type, User
    
    dim dtmStartDate, dtmEndDate, DateToCheck
    
    dim dtTimeWritten
    
     
    
    ‘Connection string for SQL Server database.
    
    strConnect = “DRIVER=SQL Server;” _
    
    & “Trusted_Connection=Yes;” _
    
    & “DATABASE=<<Database Name>>;” _
    
    & “SERVER=<<SQL Instance Name>>”
    
     
    
    ‘Use this string if SQL Server driver does not work
    
    ‘strConnect = “Provider=SQLOLEDB;” _
    
    ‘& “Data Source=<<SQL Instance Name>>;” _
    
    ‘& “Initial Catalog=<<Database Name>>;” _
    
    ‘& “Integrated Security=SSPI;”
    
     
    
    ‘ Connect to database.
    
    Set adoConnection = CreateObject(“ADODB.Connection”)
    
    adoConnection.ConnectionString = strConnect
    
    adoConnection.Open
    
     
    
    Set dtmStartDate = CreateObject(“WbemScripting.SWbemDateTime”)
    
    Set dtmEndDate = CreateObject(“WbemScripting.SWbemDateTime”)
    
    ‘ Set number of days to scrape here
    
    DateToCheck = Date – 2
    
    dtmEndDate.SetVarDate Date, True
    
    dtmStartDate.SetVarDate DateToCheck, True
    
     
    
    strComputer = “.”
    
    Set objWMIService = GetObject(“winmgmts:” _
    
    & “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)
    
    Set colLoggedEvents = objWMIService.ExecQuery _
    
    (“Select * from Win32_NTLogEvent Where Logfile = ‘Application’ and (” & _
    
    “TimeWritten >= ‘” & dtmStartDate & _
    
    “‘ and TimeWritten < ‘” & dtmEndDate & _
    
    “‘) and (EventType = ‘1’ or EventType = ‘2’)”)
    
     
    
    For Each objEvent in colLoggedEvents
    
    Category = objEvent.Category
    
    Computer_Name = objEvent.ComputerName
    
    Event_Code = objEvent.EventCode
    
    Message = objEvent.Message
    
    Record_Number = objEvent.RecordNumber
    
    Source_Name = objEvent.SourceName
    
    Time_Written = objEvent.TimeWritten
    
    Event_Type = objEvent.type
    
    User = objEvent.User
    
     
    
    ‘Fix single quotes in the message string
    
    strSQ = Chr(39)
    
    strDQ = Chr(34)
    
    if len(Message) > 0 then
    
    strMessage = Replace(Message, strSQ, strDQ)
    
    else
    
    strMessage = ” “
    
    end if
    
     
    
    dtTimeWritten = WMIDateStringToDate(Time_Written)
    
     
    
    RoleStr = “SET NOCOUNT ON INSERT INTO WinEventLog (ComputerName, EventCode, RecordNumber,” _
    
    & “SourceName, EventType, WrittenDate, UserName, Message) VALUES” _
    
    & “(‘” & Computer_Name & “‘, ‘” & CLng(Event_Code) & “‘, ‘” & CLng(Record_Number) _
    
    & “‘, ‘” & Source_Name & “‘, ‘” & Event_Type & “‘, ‘” & dtTimeWritten _
    
    & “‘, ‘” & User & “‘, ‘” & strMessage & “‘)”
    
    adoConnection.Execute RoleStr
    
    Next
    
     
    
    adoConnection.Close
    
     
    
    Function WMIDateStringToDate(Time_Written)
    
    WMIDateStringToDate = CDate(Mid(Time_Written, 5, 2) & “/” & _
    
    Mid(Time_Written, 7, 2) & “/” & Left(Time_Written, 4) _
    
    & ” ” & Mid (Time_Written, 9, 2) & “:” & _
    
    Mid(Time_Written, 11, 2) & “:” & Mid(Time_Written, _
    
    13, 2))
    
    End Function
    
    ************************

要运行 vbscript，您可以双击它以手动运行它，或者您可以设置一个使用 CMDEXEC 调用脚本的 SQL 作业。

@subsystem = N’CmdExec’,

@command = N’cscript E:\\SQLRX\\ScrapeWindowsEventLog.vbs’

希望这将帮助您更积极主动地了解您的服务器。

享受！