Powershell 收集系统日志信息

Powershell 收集系统日志信息：

  主要是利用smo对象连接数据库，定时（一个小时或者10分钟）将服务器的Application,System,Security 错误信息收集到特定的数据库

  脚本如下（没有过多的注释，将就）

 

[system.reflection.assembly]::loadwithpartialname("Microsoft.sqlserver.smo")|out-null
[system.reflection.assembly]::loadwithpartialname("Microsoft.sqlserver.connectioninfo")|out-null

# interval 
# $timediff =10
# $10minutes = new-timespan  -Minutes $timediff 
# $10minutesDiff=(get-date) - $10minutes


$timediff =1
$oneHours = new-timespan  -Hours $timediff 
$10minutesDiff=(get-date) - $oneHours


$errors=get-eventlog -logname application |where {$_.EntryType -eq "Error"} |where {$_.timewritten -gt $10minutesDiff}

$server="192.168.1.7"
$connection=new-object system.data.sqlclient.sqlconnection
$connection.connectionstring="data source="+$server+";integrated security=true;initial catalog=db1;"
$cmd=$connection.createcommand()
$cmd.commandtype=[system.data.commandtype]::text
$connection.Open()
foreach($errormsg in $errors)
{

 # write-host($errormsg.index)
 # write-host($errormsg.timewritten)

  $insert=" INSERT INTO Applog ([index],[time],[type],[source],[eventid],[message],[servername] )
 VALUES ("+$errormsg.index+",'"+$errormsg.timewritten+"','"+$errormsg.entrytype+"','"
+$errormsg.source+"',"+$errormsg.eventid+",'"+$errormsg.message+"','"+$server+"')"
 # write-host($insert)
  $cmd.CommandText=$insert
  $cmd.ExecuteNonQuery()
}
$connection.Close()

 表结构如下：

 CREATE  TABLE   Applog
 (
     id INT IDENTITY(1,1) NOT NULL  PRIMARY KEY,
     [servername] VARCHAR(20),
     [index] INT ,
     [time]     VARCHAR(20),
     [type]  VARCHAR(10),
     [source] VARCHAR(30),
     [eventid] INT,
     [message] VARCHAR (MAX),
     [writetime] DATETIME  DEFAULT(GETDATE())
 )
 

 希望对大家有所参考。