Powershell 收集系统日志信息

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

 希望对大家有所参考。

posted on 2011-06-30 11:32  Henry.Lau 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/Henry1225/archive/2011/06/30/2094303.html