Azure AutomationでSQL DatabaseのSELECT件数をメール(Slack)通知する

SQL Databaseへの定期的なクエリ発行を、Azure Automationで実現しました。
指定したテーブルの件数を取得して、メール or Slack に通知する手順です。
メール通知はSendGridを使用しています。

SendGridアカウント作成

• 以下の記事が分かりやすかったので引用させて頂きました。
  Azureのメール送信はSendGrid

• 今回の手順ではAPIキーの発行は不要です。

Automationアカウント作成

• 以下の記事が分かりやすかったので引用させて頂きました。
  Azure Automation で VM を自動停止する

• アカウントの作成と、Azureモジュールのアップデートまで実施しましょう。

Automation資格情報追加

Runbookの中でSendGridとSQL Databaseの認証にAutomation資格情報を利用します。

SendGrid資格情報を追加する

• Automationアカウントを選択し、[資格情報の追加]をクリックします。
  

• 名前には、任意の値を入力します。
  

• ユーザ名には、SendGridアカウント情報より取得したUSERNAMEを入力します。
  

• パスワードには、SendGridアカウント作成時に指定したパスワードを入力します。

SQL Database資格情報を追加する

• 上記手順と同様にSQL Databaseの資格情報を追加します。ユーザ名とパスワードには、SQL Databaseのユーザ情報を入力してください。

Email通知用Runbook作成

• Automationアカウントより、[Runbook]→[Runbookの追加]を選択します。
  

• 名前には、任意の値を入力します。Runbookの種類には、[PowerShell ワークフロー]を選択します。
  

• Runbook編集が画面が表示されます。
  

• 以下のコードを貼り付けます。workflow名はRunbookの名称と合わせるようにして下さい。

workflow kwhrtest
{

    param(
        [parameter(Mandatory=$True)]
        [string] $SqlServer,

        [parameter(Mandatory=$False)]
        [int] $SqlServerPort = 1433,

        [parameter(Mandatory=$True)]
        [string] $Database,

        [parameter(Mandatory=$True)]
        [string] $Table,

        [parameter(Mandatory=$True)]
        [PSCredential] $SqlCredName,

        [parameter(Mandatory=$True)]
        [String] $SmtpCredName,

        [parameter(Mandatory=$True)]
        [string] $MailTo
    )

    # Get the username and password from the SQL Credential
    $SqlUsername = $SqlCredName.UserName
    $SqlPass = $SqlCredName.GetNetworkCredential().Password

    # Get the username and password from the SMTP Credential
    $SmtpCredential = Get-AutomationPSCredential -Name $SmtpCredName

    inlinescript {
        # Define the connection to the SQL Database
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

        # Open the SQL connection
        $Conn.Open()

        # Define the SQL command to run. In this case we are getting the number of rows in the table
        $Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT COUNT(*) from dbo.$using:Table", $Conn)
        $Cmd.CommandTimeout=120

        # Execute the SQL command
        $Ds=New-Object system.Data.DataSet
        $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
        [void]$Da.fill($Ds)
        $html = $Ds.Tables[0].Rows[0][0]

        # Close the SQL connection
        $Conn.Close()

        function EncodeSubject($s) {
            $enc = [Text.Encoding]::GetEncoding("csISO2022JP")
            $s64 = [Convert]::ToBase64String($enc.GetBytes($s), [Base64FormattingOptions]::None)
            return [String]::Format("=?{0}?B?{1}?=", $enc.HeaderName, $s64)  
        }

        $From = "[任意の送信元メールアドレス]"
        $Subject = EncodeSubject(EncodeSubject("[メール件名]"))

        Send-MailMessage `
            -To $using:MailTo  `
            -Subject $Subject  `
            -Body "$html 件 <br /> です" `
            -UseSsl `
            -Port 587 `
            -SmtpServer 'smtp.sendgrid.net' `
            -From $From `
            -BodyAsHtml `
            -Encoding ([System.Text.Encoding]::UTF8) `
            -Credential $using:SmtpCredential
    }
}

• 入力したら[保存]を選択し、[テストウィンドウ]を選択する。
  

• パラメータ入力画面を以下の内容で入力します。

  • SQLSERVER : SQL Databaseサーバー名
  • SQLSERVERPORT : 1433
  • DATABASE : データベース名
  • TABLE : テーブル名
  • SQLCREDNAME : 前手順で任意の名前で作成した資格情報名を入力する
  • SMTPCREDNAME : 前手順で任意の名前で作成した資格情報名を入力する
  • MAILTO : メール送付先アドレス

　

• [開始]を選択する。
  
  
  

• メールがくれば成功
  

Runbookスケジュール設定

• Runbook概要ページより、[スケジュール]を選択する。
  

• [スケジュール]→[新しいスケジュールを作成します]の順に選択し、任意の値を入力する。
  

• [パラメータと実行設定]を選択し、以下のように入力する。

  • SQLSERVER : SQL Databaseサーバー名
  • SQLSERVERPORT : 1433
  • DATABASE : データベース名
  • TABLE : テーブル名
  • SQLCREDNAME : 前手順で任意の名前で作成した資格情報名を入力する
  • SMTPCREDNAME : 前手順で任意の名前で作成した資格情報名を入力する
  • MAILTO : メール送付先アドレス

• 指定した時間に実行されていれば成功

Slack通知用Runbook作成

• E-Mail通知部分をSlack通知に置き換えてみます。

• SlackのIncoming WebHooksを使っていますので事前にSlackでURLを取得しておいてください。

workflow kwhrtest-slack
{

    param(
        [parameter(Mandatory=$True)]
        [string] $SqlServer,

        [parameter(Mandatory=$False)]
        [int] $SqlServerPort = 1433,

        [parameter(Mandatory=$True)]
        [string] $Database,

        [parameter(Mandatory=$True)]
        [string] $Table,

        [parameter(Mandatory=$True)]
        [PSCredential] $SqlCredName
    )

    # Get the username and password from the SQL Credential
    $SqlUsername = $SqlCredName.UserName
    $SqlPass = $SqlCredName.GetNetworkCredential().Password

    inlinescript {
        # Define the connection to the SQL Database
        $Conn = New-Object System.Data.SqlClient.SqlConnection("Server=tcp:$using:SqlServer,$using:SqlServerPort;Database=$using:Database;User ID=$using:SqlUsername;Password=$using:SqlPass;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;")

        # Open the SQL connection
        $Conn.Open()

        # Define the SQL command to run. In this case we are getting the number of rows in the table
        $Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT COUNT(*) from dbo.$using:Table", $Conn)
        $Cmd.CommandTimeout=120

        # Execute the SQL command
        $Ds=New-Object system.Data.DataSet
        $Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd)
        [void]$Da.fill($Ds)
        $html = $Ds.Tables[0].Rows[0][0]

        # Close the SQL connection
        $Conn.Close()

        $msg = "$html 件です"
        $enc = [System.Text.Encoding]::GetEncoding('ISO-8859-1')
        $utf8Bytes = [System.Text.Encoding]::UTF8.GetBytes($msg)
        $payload = @{
                text = $enc.GetString($utf8Bytes);
                username = "Azure Automation";
                icon_emoji = ":azure:"
        }
        Invoke-RestMethod -Uri "[Slack WebHook URL]" -Method Post -Body (ConvertTo-Json $payload)
    }
}

• Slackに通知されれば成功

補足

メールの件名に日本語が使えない？

通常はSend-MailMessageで-Encodingを付与すると、日本語で件名・本文を指定することが可能ですが、Automationで実行すると以下のエラーを吐きます。

Send-MailMessage : String contains invalid Unicode code points.
Parameter name: strInput


Automation側の問題のようで以下のように2重エンコードすると回避できました。

function EncodeSubject($s) {
$enc = [Text.Encoding]::GetEncoding("csISO2022JP")
$s64 = [Convert]::ToBase64String($enc.GetBytes($s), [Base64FormattingOptions]::None)
return [String]::Format("=?{0}?B?{1}?=", $enc.HeaderName, $s64)
}
$Subject = EncodeSubject(EncodeSubject("Automationからのお知らせ"))


以上