我们都知道，一般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脚本，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
}
}
执行结果
模拟日志：4720是创建账户，所以我们手动创建一个账户，再次执行
再次执行
查询数据库
select * from GeneralEvents
