Windows事件記錄寫入SQL Server並PowerBI統計分析

標籤：進階   cad   reader   vpd   nts   member   record   安全事件   寫入   

在這裡我準備了2台系統，一個Windows Server 2012 R2的域控伺服器DC01，一台SQL on CentOS7的SQL資料庫伺服器

首先我使用SQL Manager Studio串連到SQL資料庫伺服器建立需要存放Windows轉寄事件記錄的資料庫“EventCollections”

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

為了避免後面每小時匯入一次日誌資料時出現重複，對RecordID，MachineName和LogName使用IGNORE_DUPE_KEY=ON建立唯一的叢集索引

接下來回到DC伺服器配置事件服務

首先需要配置WinRM，顯示可用的接聽程式

winrm e winrm/config/listener

執行winrm get winrm/config

檢查

allowremoteAccess = true

在日誌原始伺服器（我們只有DC一台伺服器，使用這台既是源也是收集Log Service器）把network Service加入到Event Log Readers組裡

然後在日誌原始伺服器和收集Log Service器執行如下命令：

wevtutil sl security /ca:O:BAG:SYD:(A;;0xf0005;;;SY)(A;;0x5;;;BA)(A;;0x1;;;S-1-5-32-573)(A;;0x1;;;S-1-5-20)

接下來開啟事件檢視器，點擊訂閱，這是會出現提示，是否啟用Windows事件收集器服務，點擊“是”

之間轉寄使用的HTTP連接埠是5985

然後建立一個新的訂閱，指定需要收集的電腦，這裡輸入DC01

選擇需要訂閱哪些日誌，這裡我選擇System

選擇收集的事件層級

在進階裡指定收集日誌的帳戶為網域系統管理員帳戶，然後確定

點擊使用者名稱密碼進行輸入

正常：每15分鐘

最小化頻寬：每6小時

最小化延遲：每30秒

確定

這樣就建立好一個收集系統日誌的訂閱了

按照同樣的方法再建立一個安全日誌的訂閱

如果要執行命令的審計日誌，可以開啟下面2個位置的組策略，然後通過事件ID4688查看

電腦配置 > 策略 > Windows 設定 > 安全設定 > 進階審核配置 > 詳細跟蹤>審核建立進程

管理 模板\系統\審核建立的進程\在建立事件的過程中包含命令列

備忘：Microsoft不建議永久啟用命令列審核。啟用此功能後，對Windows安全事件的讀取存取許可權的任何使用者將能夠讀取任何成功建立的進程的命令列參數。請記住，命令列命令可能包含機密資訊，包括密碼和其他使用者資料

等待15分鐘後事件檢視器的已轉寄事件裡就出現了我們訂閱的安全和系統日誌了

最後我在DC上執行如下PowerShell命令，將已轉寄事件的日誌寫入SQL裡

• 如果SQL是台Windows並且加域，那麼可以採用整合身分識別驗證方式登陸，執行下面指令碼

# 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) &lt;= 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)

• 如果是採用sa帳戶登陸就執行如下：

$xml = @‘

<QueryList>

<Query Id="0" Path="ForwardedEvents">

<Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) &lt;= 3900000]]]</Select>

</Query>

</QueryList>

‘@

$events = Get-WinEvent -FilterXml $xml | Select-Object ID, LevelDisplayName, LogName, MachineName, Message, ProviderName, RecordID, TaskDisplayName, TimeCreated

$connectionString = "Data Source=sqlserver;user id=sa;[email protected];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)

其中上面這段：

<QueryList>

<Query Id="0" Path="ForwardedEvents">

<Select Path="ForwardedEvents">*[System[TimeCreated[timediff(@SystemTime) &lt;= 3900000]]]</Select>

</Query>

</QueryList>

取自於已轉寄事件的“篩選當前日誌”

XML內容

執行完成以後可以到SQL去檢查日誌是否已經寫進SQL

select * from GeneralEvents

可以看到日誌成功寫入SQL裡

最後就是做一個Windows計劃任務把上面的Powershell指令碼每隔1小時自動執行一次了

把上面成功執行的指令碼儲存成ps1檔案，並把這個檔案剪下到C盤根目錄下

開啟工作排程器，建立一個基本任務

下一步

選擇每天

下一步

啟動程式

在程式裡選擇powershell的啟動路徑C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

參數添加-command ". ‘c:\event-into-sql.ps1‘"

勾選“但單擊“完成”時，開啟此任務屬性的對話方塊”，然後完成

設定執行該計劃任務的帳戶，以及許可權

在觸發器裡修改每日為如所示

確定，建立完成

到這裡就大功告成了。既然事件記錄都寫入SQL了，那麼就可以利用PowerBI Desktop去讀取SQL的資料進行事件記錄統計分析了，如：

Windows事件記錄寫入SQL Server並PowerBI統計分析