PowerShell 读取 Goldengate 进程转为数组插入到sql server中

在使用 oracle Goldengate 同步时，有时会忽略了ogg进程的启动。若安装 Goldengate monitor 只监控着一两个同步又没必要，所以使用脚本来进行监控查看。为便于可视化，先将 ogg 的进程运行情况存储到数据库中，再读取数据库中的数据查看。当前脚本只是把监控状态存储到数据库中，在windows 系统中使用powershell 编写。

先在数据库创建基本表结构：

create table Ogginfo(
  [Program] varchar(20) null,
  [Status] varchar(20) null,
  [Group] varchar(50) null,
  [LagAtChkpt] varchar(20) null,
  [TimeSinceChkpt] varchar(20) null
)
go

将状态信息插入数据库的 powershell 脚本：

function Get-OggInfo
{
  $string = "CMD /c echo Status All | E:\Gg\GGSCI"
  $result = Invoke-Expression $String
  $raw = $result -match "Program|MANAGER|EXTRACT|REPLICAT"
  $raw = $raw -replace "Lag at Chkpt","LagatChkpt" -replace "Time Since Chkpt","TimeSinceChkpt" -replace "^[\s>]" , "" -replace "\s+" , ","
  $raw | ConvertFrom-Csv
  #$raw | ConvertFrom-Csv | select Program,Status,Group,LagatChkpt,TimeSinceChkpt
}


function IsNull([String]$x) {
  if ($x) { "'"+$x+"'"} else { "null" }
}

$server = "kk-pc"
$database = "demodb"
$oi = Get-OggInfo
$connection = New-Object System.Data.SQLClient.SQLConnection
#$connection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password" 
$connection.ConnectionString = "Data Source=$Server;Initial Catalog=$database;Integrated Security=SSPI;"
$connection.Open()
$command = New-Object System.Data.SQLClient.SQLCommand
$command.Connection = $connection
foreach($i in 0..($ogginfo.length-1)) {
  $sql = "INSERT INTO Ogginfo([Program],[Status],[Group],[LagatChkpt],[TimeSinceChkpt])
  VALUES($(IsNull($oi[$i].Program)),$(IsNull($oi[$i].Status)),$(IsNull($oi[$i].Group)),$(IsNull($oi[$i].LagatChkpt)),$(IsNull($oi[$i].TimeSinceChkpt)))" 
  $command.CommandText = $sql
  $command.ExecuteNonQuery()
}
$connection.Close()

完成示例：

更进一步完善：添加时间字段，windows创建定时任务执行，判断非 “running” 状态尝试重启进程等。