我们都知道,一般Windows系统的Event日志默认保留时间较短,主要是因为默认日志文件大小设定为200M,当系统日志量大的话,很容易增长到默认大小,这样会覆盖原有的,在审计的时候会很麻烦,当然可以修改日志大小,但是这样一来日志如果保留比较大的话,在查询的时候对系统有一定影响;现在很多企业都在做等保,需要有独立的日志系统,很多企业都会采购第三方产品,但是对于一般没有投入的话,也可以通过其他开源产品或者工具进行日志的收集及管理,今天主要介绍使用Powershell定时将系统的Event写入数据库中留存统计。

创建数据库表

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

);

Powershell定时将Event写入数据库_Windows

定义Powershell脚本,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()

}

我们在此模拟账户相关事件ID,所以就收集4720、4722、4726、4625的事件

#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

}

}

执行结果

Powershell定时将Event写入数据库_Windows_02

模拟日志:4720是创建账户,所以我们手动创建一个账户,再次执行

Powershell定时将Event写入数据库_ide_03

再次执行

Powershell定时将Event写入数据库_ide_04

查询数据库

select * from GeneralEvents

Powershell定时将Event写入数据库_ide_05