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

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

$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)

# 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 = @'
  
    *[System[TimeCreated[timediff(@SystemTime) <= 3900000]]]
  
'@
 
$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)