小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【PowerShell】【SQL Server】SQLの結果を条件にイベントログに書き込む

完全に自分用の備忘録です。
SQL Serverに対しストアドを作ることなく、SQLの戻り値を使ってイベントログに書き込みをするスクリプトです。

イベントログへの書き込み

事前にイベントログにソースを登録したことがなければNew-Eventlogにて作成しておきます。
今回はTemp_alertとしておきます。

New-Eventlog -LogName Application -Source "Temp_alert"

イベントログへの書き込みはWrite-Eventlogにて行います。

Write-Eventlog -Logname Application -Source Temp_alert -EntryType Information -EventId 9999 -Message "test"

f:id:utiowa:20170428002208p:plain

イベントログに書き込みができていることを確認します。
f:id:utiowa:20170427010715p:plain

PowerShellからのSQLの実行

PowerShellからSQLを実行するので、Invoke-Sqlcmdを使用します。

Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Username $User -Password $Password -InputFile $Input_count 

その他詳しいオプションは下記を参照してください。
https://msdn.microsoft.com/ja-jp/library/cc281720.aspx

今回は単純に数をカウントするだけのSQLと詳細情報を参照する以下クエリを用意しました。

Sqlquery_count.sql

SET NOCOUNT ON

SELECT 
       COUNT([JOB_ID]) AS [Count]
  FROM [dbo].[JOBS]

Sqlquery_detail.sql

SET NOCOUNT ON

SELECT 
      [JOB_ID]
      ,[JOB_TITLE]
      ,[MIN_SALARY]
      ,[MAX_SALARY]
  FROM [dbo].[JOBS]

PowerShellからSQLを実行してイベントログへ書き込み

SQLでのカウントが0の場合は問題ないことをログに書き込み、0でない場合エラーとしてイベントログに書き込むサンプルです。

以下のようなフォルダ構成で実行します。
f:id:utiowa:20170428002334p:plain


PowerShellは以下の通りです。


$Server = "YUUSUKE-VAIO\INS_NISHI2016"
$Database = "sales"
$User = "sa"
$Password = "system"

$Hostname = hostname
$Log_Level = "ERROR"

$Scriptpath = Split-Path $myInvocation.Mycommand.Path -Parent
$Scriptpath = Split-Path -Parent $Scriptpath
$Input_count = $Scriptpath + "\scripts\Sqlquery_count.sql"
$Input_detail = $Scriptpath +  "\scripts\Sqlquery_detail.sql"
$Output_detail = $Scriptpath + "\output\Sqlquery_detail.log"

# タイムスタンプはyyyy MMM dd HH:mm:ss形式
$us = New-Object system.globalization.cultureinfo("en-US")
$Date = (Get-date).ToString("yyyy MMM dd HH:mm:ss", $us)
$Message_header = "XXXXX"
$Message_body = "target_count is "

# 対象のカウント
$Resultset = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Username $User -Password $Password -InputFile $Input_count 

$Message = $Message_header + " " + $Date + " " + $Hostname + " " + $Log_Level + " " + $Message_body + [string]$Resultset['Count']

# 条件に応じてイベントログに出力
if ($Resultset['Count'] -eq 0 ){
  Write-Eventlog -Logname Application -Source Temp_alert -EntryType Information -EventId 9999 -Message $Message
}else{
  $Resultset = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Username $User -Password $Password -InputFile $Input_detail
  $Resultset | Out-File $Output_detail
  Foreach ($item in $Resultset){
    $items += ',' + $item['JOB_ID']
  }
$Message = $Message + $items
Write-Eventlog -Logname Application -Source Temp_alert -EntryType Warning -EventId 9999 -Message $Message
}

結果は以下のようにイベントログに書き込めていることがわかります。
f:id:utiowa:20170427011005p:plain

本番環境だと申請なしにストアドを作成するわけにもいかないこともありますのでストアドを使わない方法として一時しのぎに使おうかと。