使用 PowerShell 将 Windows 转发事件导入 SQL Server

忘是亡心i 2023-09-28 18:23 7阅读 0赞

在过去的几周里，我研究了多种解析[Windows 转发事件][Windows]并将其导入SQL Server 的方法：从使用 SSIS 到 LogParser 到 PowerShell，再到将链接服务器设置为“Forwarding Events.evtx”文件。

最终，唯一有效的是 PowerShell 的 Get-WinEvent cmdlet。然后，它只适用于我的一个特定情况——如果事件是在 Windows 2012 服务器上收集和解析的。截至今天，Get-WinEvent 中存在一个[未解决的错误][Link 1]，该错误通常导致 NULL LevelDisplayName、Message 和 TaskDisplayName 列。我在 Win2k8 R2 服务器和 Win 8 工作站上复制了下面的确切代码，并反复遇到 NULLs 问题。但是，您的结果可能会有所不同，因为一些用户报告通过在 Win2k8 R2 Server 中调整一些东西取得了成功。

因此，启动一个 Windows 2012 机器，设置您的 SQL Server，让我们开始吧：

### SQL 部分 ###

查看Get-WinEvent返回的数据后，我发现以下列最有用：ID、LevelDisplayName、LogName、MachineName、Message、ProviderName、RecordID、TaskDisplayName、TimeCreated。然后我使用这些列创建了一个表：

CREATE DATABASE EventCollections
    GO
    USE EventCollections
    GO
    -- the table name loosely relates to the name of my Win Event Subscription name
    CREATE TABLE [dbo].[GeneralEvents]( 
         [Id] [int] NULL,
         [LevelDisplayName] [varchar](255) NULL,
         [LogName] [varchar](255) NULL,
         [MachineName] [varchar](255) NULL,
         [Message] [varchar](max) NULL,
         [ProviderName] [varchar](255) NULL,
         [RecordID] [bigint] NULL,
         [TaskDisplayName] [varchar](255) NULL,
         [TimeCreated] [smalldatetime] NULL
    )
    -- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates in sqlbulk imports
    CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-EventCombo] ON [dbo].[GeneralEvents]
    (
         [RecordID] ASC,
         [MachineName] ASC,
         [LogName] ASC
    ) WITH (IGNORE_DUP_KEY = ON)
    GO

为了避免在每小时导入期间出现重复，我使用 IGNORE\_DUP\_KEY = ON 在 3 列上的唯一索引创建了表：RecordID、MachineName 和 LogName。

接下来，我必须决定如何将数据从 PowerShell 获取到 SQL Server。在阅读了[sqlservercentral.com][]和[technet][]之后，我决定使用`sqlbulkcopy`.

### PowerShell 部分 ###

转发事件是一件棘手的事情。出于某种原因，通常使用[FilterHasTable][]过滤 Get-WinEvent 结果的方式一直返回结果*Get-WinEvent ：未找到与指定选择条件匹配的事件。*我发现许多[其他人][Link 2]也遇到了这个问题，当人们尝试使用 LogParser 时也发生了类似的错误。毕竟，我对 FilterXML 的工作没有太大希望，但它确实做到了！因此，我们将在执行初始导入后使用它。

这是`initial import`在转发事件中收集所有事件的代码。

$events = Get-WinEvent ForwardedEvents |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  
    
    $connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "GeneralEvents"
    $dt = New-Object "System.Data.DataTable"
    
    # build the datatable
    $cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
    foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}
      
    foreach ($event in $events)
      {
         $row = $dt.NewRow()
         foreach ($col in $cols) { $row.Item($col) = $event.$col }
         $dt.Rows.Add($row)
      }
      
     # Write to the database!
     $bulkCopy.WriteToServer($dt)

您可能已经注意到我手动构建了一个数据表，而不是使用[Out-DataTable.ps1][]，这似乎是粉丝的最爱。我觉得上面的代码让事情变得更加整洁，性能仍然相当不错。

由于事件收集是一个持续的事情，您可能希望定期导入它们。我通过右键单击事件查看器中的转发事件->过滤当前日志...->记录：（更改为一小时）->单击顶部的 XML 选项卡->复制/粘贴->瞧，构建了必要的 XML 查询。

![5ecb730019a548569d34855ff58d5d47.png][]

实际上，使用这个查询的语法，我找到了 FilterHashTable 的语法，但是让 GUI 构建我的查询很容易，所以我坚持了下来。这是`hourly import`您可以在任务计划程序中设置的代码。

# While this script is intended to run on an hourly basis, the filter is set for going back 65 minutes.
    # This allows the script to run for 5 minutes without any missing any events. Because we setup the 
    # table using the IGNORE_DUPE_KEY = ON, duplicate entries are ignored in the database.
    
    $xml = @'
    <QueryList>
      <Query Id="0" Path="ForwardedEvents">
        <Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]</Select>
      </Query>
    </QueryList>
    '@
    
    $events = Get-WinEvent -FilterXml $xml |  Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated  
    
    $connectionString = "Data Source=sqlserver;Integrated Security=true;Initial Catalog=EventCollections;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "GeneralEvents"
    $dt = New-Object "System.Data.DataTable"
    
    # build the datatable
    $cols = $events | select -first 1 | get-member -MemberType NoteProperty | select -Expand Name
    foreach ($col in $cols)  {$null = $dt.Columns.Add($col)}
      
    foreach ($event in $events)
      {
         $row = $dt.NewRow()
         foreach ($col in $cols) { $row.Item($col) = $event.$col }
         $dt.Rows.Add($row)
      }
     
    # Write to the database!
    $bulkCopy.WriteToServer($dt)

运气好的话，您的 SQL 输出应该如下所示：

![de2bcdce604c4a09adef6a925c69da44.png][]

[Windows]: https://blog.netnerds.net/2013/03/centralizing-windows-events-using-a-collector-initiated-subscription/
[Link 1]: https://connectppe.microsoft.com/PowerShell/feedback/details/716533/get-winevent-does-not-return-the-content-of-the-event-message-in-v3-ctp2
[sqlservercentral.com]: https://www.sqlservercentral.com/articles/powershell/65196/
[technet]: https://blogs.technet.com/b/heyscriptingguy/archive/2011/11/28/four-easy-ways-to-import-csv-files-to-sql-server-with-powershell.aspx
[FilterHasTable]: https://theboywonder.co.uk/2012/03/15/speeding-up-get-winevent-in-powershell-by-using-filterhashtable/
[Link 2]: https://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_27580312.html
[Out-DataTable.ps1]: https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd
[5ecb730019a548569d34855ff58d5d47.png]: https://img-blog.csdnimg.cn/5ecb730019a548569d34855ff58d5d47.png
[de2bcdce604c4a09adef6a925c69da44.png]: https://img-blog.csdnimg.cn/de2bcdce604c4a09adef6a925c69da44.png