POWERSHELL--读取Window日志并写入SQL SERVER

创建表脚本

CREATE TABLE [dbo].[WinErrorLog](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [LogIndex] [int] NULL,
    [LogTime] [datetime] NULL,
    [LogEntryType] [nvarchar](100) NULL,
    [LogInstanceId] [bigint] NULL,
    [LogSource] [nvarchar](500) NULL,
    [LogMessage] [nvarchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

读取Windows日志并写入数据库

echo "注册SqlServerCmdletSnapin100"
try
{
    Add-PSSnapin -Name SqlServerCmdletSnapin100
}
catch
{
}
echo "已注册SqlServerCmdletSnapin100"

##读取出错误日志
$items =Get-EventLog -LogName "Windows PowerShell" -Newest 10 
$varResult="共有"+$items.count.tostring()+"条错误日志"
echo $varResult

##遍历错误日志
foreach($item in $items)
{
    $LogIndex=$item.Index.tostring()
    $LogTime=$item.TimeWritten.tostring("yyyy-MM-dd HH:mm:ss").replace("'","")
    $LogTime
    $LogEntryType=$item.EntryType.tostring()
    $LogEntryType
    $LogInstanceId=$item.InstanceID
    $LogSource=$item.Source.tostring().replace("'","")
    $LogMessage=$item.Message.replace("'","")
    
    
    echo "将日志写入数据库"
    Invoke-Sqlcmd -Query "INSERT INTO [dbo].[WinErrorLog]([LogIndex],[LogTime],[LogEntryType],[LogInstanceId],[LogSource],[LogMessage])VALUES($LogIndex,'$LogTime','$LogEntryType','$LogInstanceId','$LogSource','$LogMessage');" -Variable $MyArray -ServerInstance ".\SQL2014" -database "TestDB1"
}