Powershell定时将Event写入SQL

1、创建数据库表

CREATE DATABASE EventCollections
GO
USE EventCollections
GO
CREATE TABLE "GeneralEvents" (
"id" BIGINT identity(1,1) not null PRIMARY key,
"MachineName" VARCHAR(50) NULL DEFAULT (NULL),
"logname" VARCHAR(50) NULL DEFAULT (NULL),
"eventid" VARCHAR(50) NULL DEFAULT (NULL),
"LevelDisplayName" VARCHAR(50) NULL DEFAULT (NULL),
"TimeCreated" VARCHAR(50) NULL DEFAULT (NULL),
"Message" VARCHAR(max) NULL DEFAULT (NULL),
"type_of_operation" VARCHAR(50) NULL DEFAULT (NULL),
"Operator" VARCHAR(50) NULL DEFAULT (NULL),
"operated_by" VARCHAR(50) NULL DEFAULT (NULL),
);

2、SQL连接器

定义数据库连接信息

param
(
[string]$server = '10.1.0.11',
[UInt16]$port = 1433,
[string]$username = 'GlogMSrv',
[string]$pass = '123456',
[string]$database = 'EventCollections',
[string]$query = "select @@version;"
)
$连接1参数 = 'Data Source= ' + $server + ',' + $port + ';User ID= ' + $username + ';Password=' + $pass + ';Initial Catalog= ' + $database + ';'
#Data Source=1.2.3.4,1433;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
$连接1 = New-Object System.Data.SqlClient.SqlConnection
$连接1.ConnectionString = $连接1参数
$连接1命令运行器 = New-Object System.Data.SqlClient.SqlCommand
$连接1命令运行器.CommandText = $query
$连接1命令运行器.Connection = $连接1
try
{
$连接1.Open()
$连接1命令运行器.ExecuteNonQuery() | Out-Null
}
catch [Exception]
{
Write-Warning $_.Exception.Message
Write-Warning $query
}
finally
{
$连接1命令运行器.Dispose()
$连接1.close()
$连接1.Dispose()
}

1. 数据收集

收集4720、4722、4726、4625的事件ID log

#Requires -RunAsAdministrator
#需要管理员权限
$a = Get-WinEvent -ComputerName $env:COMPUTERNAME -FilterHashtable @{
Providername = 'Microsoft-Windows-Security-Auditing'
Id           = 4724
StartTime    = (Get-Date).AddHours(-1)
}
if ( ($a -eq $null) -or ($a -eq '') )
{
Write-Warning '信息：过去1小时，未发现4720事件'
}
else
{
Write-Warning '信息：过去1小时，发现4720事件'
$a.count
foreach ($a001 in $a)
{
$temp1 = $a001.Message.split("`n")
$msg1 = $temp1[0].Trim().replace('试图','').replace('。','')
$temp2 = $temp1 | Select-String -Pattern '帐户名'
$msg2 = $temp2[0].line.Trim().split(':')[-1].Trim()
$msg3 = $temp2[1].line.Trim().split(':')[-1].Trim()
$q = @"
INSERT INTO EventCollections.dbo.GeneralEvents (MachineName, logname, eventid, LevelDisplayName, TimeCreated, Message,type_of_operation,Operator,operated_by)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')
;
"@ -f $a001.MachineName, $a001.logname, $a001.id, $a001.LevelDisplayName, $a001.TimeCreated, $a001.Message,$msg1,$msg2,$msg3
#$q
#Pause
& "${PSScriptRoot}/SqlDB_Con.ps1" -query $q
}
}
$b = Get-WinEvent -ComputerName $env:COMPUTERNAME -FilterHashtable @{
Providername = 'Microsoft-Windows-Security-Auditing'
Id           = 4722
StartTime    = (Get-Date).AddHours(-1)
}
if ( ($b -eq $null) -or ($b -eq '') )
{
Write-Warning '信息：过去1小时，未发现4722事件'
}
else
{
Write-Warning '信息：过去1小时，发现4722事件'
$b.count
foreach ($b001 in $b)
{
$temp1 = $b001.Message.split("`n")
$msg1 = $temp1[0].Trim().replace('试图','').replace('。','')
$temp2 = $temp1 | Select-String -Pattern '帐户名'
$msg2 = $temp2[0].line.Trim().split(':')[-1].Trim()
$msg3 = $temp2[1].line.Trim().split(':')[-1].Trim()
$q = @"
INSERT INTO EventCollections.dbo.GeneralEvents (MachineName, logname, eventid, LevelDisplayName, TimeCreated, Message,type_of_operation,Operator,operated_by)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')
;
"@ -f $b001.MachineName, $b001.logname, $b001.id, $b001.LevelDisplayName, $b001.TimeCreated, $b001.Message,$msg1,$msg2,$msg3
#$q
#Pause
& "${PSScriptRoot}/SqlDB_Con.ps1" -query $q
}
}
$c = Get-WinEvent -ComputerName $env:COMPUTERNAME -FilterHashtable @{
Providername = 'Microsoft-Windows-Security-Auditing'
Id           = 4726
StartTime    = (Get-Date).AddHours(-1)
}
if ( ($c -eq $null) -or ($c -eq '') )
{
Write-Warning '信息：过去1小时，未发现4726事件'
}
else
{
Write-Warning '信息：过去1小时，发现4726事件'
$c.count
foreach ($c001 in $c)
{
$temp1 = $c001.Message.split("`n")
$msg1 = $temp1[0].Trim().replace('试图','').replace('。','')
$temp2 = $temp1 | Select-String -Pattern '帐户名'
$msg2 = $temp2[0].line.Trim().split(':')[-1].Trim()
$msg3 = $temp2[1].line.Trim().split(':')[-1].Trim()
$q = @"
INSERT INTO EventCollections.dbo.GeneralEvents (MachineName, logname, eventid, LevelDisplayName, TimeCreated, Message,type_of_operation,Operator,operated_by)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')
;
"@ -f $c001.MachineName, $c001.logname, $c001.id, $c001.LevelDisplayName, $c001.TimeCreated, $c001.Message,$msg1,$msg2,$msg3
#$q
#Pause
& "${PSScriptRoot}/SqlDB_Con.ps1" -query $q
}
}
$d = Get-WinEvent -ComputerName $env:COMPUTERNAME -FilterHashtable @{
Providername = 'Microsoft-Windows-Security-Auditing'
Id           = 4625
StartTime    = (Get-Date).AddHours(-1)
}
if ( ($d -eq $null) -or ($d -eq '') )
{
Write-Warning '信息：过去1小时，未发现4625事件'
}
else
{
Write-Warning '信息：过去1小时，发现4625事件'
$d.count
foreach ($d001 in $d)
{
$temp1 = $d001.Message.split("`n")
$msg1 = $temp1[0].Trim().replace('试图','').replace('。','')
$temp2 = $temp1 | Select-String -Pattern '帐户名'
$msg2 = $temp2[0].line.Trim().split(':')[-1].Trim()
$msg3 = $temp2[1].line.Trim().split(':')[-1].Trim()
$q = @"
INSERT INTO EventCollections.dbo.GeneralEvents (MachineName, logname, eventid, LevelDisplayName, TimeCreated, Message,type_of_operation,Operator,operated_by)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')
;
"@ -f $d001.MachineName, $d001.logname, $d001.id, $d001.LevelDisplayName, $d001.TimeCreated, $d001.Message,$msg1,$msg2,$msg3
#$q
#Pause
& "${PSScriptRoot}/SqlDB_Con.ps1" -query $q
}
}

4、执行结果

模拟日志

4720是创建账户，所以我们手动创建一个账户，再次执行

5、查看数据库

select * from GeneralEvents