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

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

# SendGridアカウント作成

* 以下の記事が分かりやすかったので引用させて頂きました。
[Azureのメール送信はSendGrid](https://qiita.com/miwato/items/af684bdcba49a2e2293e)

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

# Automationアカウント作成

* 以下の記事が分かりやすかったので引用させて頂きました。
[Azure Automation で VM を自動停止する](https://blogs.technet.microsoft.com/jpaztech/2017/09/01/azure-automation-%E3%81%A7-vm-%E3%82%92%E8%87%AA%E5%8B%95%E5%81%9C%E6%AD%A2%E3%81%99%E3%82%8B/)

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

# Automation資格情報追加

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

### SendGrid資格情報を追加する

* Automationアカウントを選択し、[資格情報の追加]をクリックします。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/2ea5028b-7c47-db35-3c66-29883af042d8.png)

* 名前には、任意の値を入力します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/622a23a7-c793-5ff5-3d74-83cec9bd4a03.png)

* ユーザ名には、SendGridアカウント情報より取得したUSERNAMEを入力します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/a1dbe2d9-fd58-23be-7aa8-1cdc7e76c1d8.png)

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

### SQL Database資格情報を追加する

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

# Email通知用Runbook作成

* Automationアカウントより、[Runbook]→[Runbookの追加]を選択します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/5fa20117-c6af-33bf-cc29-f8d58f3ba8d8.png)

* 名前には、任意の値を入力します。Runbookの種類には、[PowerShell ワークフロー]を選択します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/688b41f6-9edf-d236-2bb3-d37a0f7a78e3.png)

* Runbook編集が画面が表示されます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/ddbd59c2-892f-9031-cd59-4e5d59dfd368.png)

* 以下のコードを貼り付けます。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
    }
}
```

* 入力したら[保存]を選択し、[テストウィンドウ]を選択する。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/a819445e-fe1e-f3ec-63a3-1ca0f74965ec.png)

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

　![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/548a816c-d4e5-43fc-e7ba-d53abeb00e4e.png)


* [開始]を選択する。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/df38dfaf-a903-b9d0-5e30-dbda788e0e7e.png)
<br>

* メールがくれば成功
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/f212361d-59d1-e866-2be1-ce3ecf4e1cf4.png)

# Runbookスケジュール設定

* Runbook概要ページより、[スケジュール]を選択する。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/71ffc080-f085-8478-529c-265fbdc9a274.png)

* [スケジュール]→[新しいスケジュールを作成します]の順に選択し、任意の値を入力する。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/9616ca7d-9381-ec67-a236-2669d0768b8a.png)

* [パラメータと実行設定]を選択し、以下のように入力する。
 * SQLSERVER : SQL Databaseサーバー名
 * SQLSERVERPORT : 1433
 * DATABASE : データベース名
 * TABLE : テーブル名
 * SQLCREDNAME : 前手順で任意の名前で作成した資格情報名を入力する
 * SMTPCREDNAME : 前手順で任意の名前で作成した資格情報名を入力する
 * MAILTO : メール送付先アドレス
![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/0d7896ff-eb9c-8697-bee7-beed166937de.png)

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

# 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に通知されれば成功

![image.png](https://qiita-image-store.s3.amazonaws.com/0/180335/431ebff5-469a-f7d6-b01e-bba22f32ca93.png)

# 補足
### メールの件名に日本語が使えない？
通常は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からのお知らせ"))
```

以上