やりたかった事
毎日1回（もしくは数時間に1回）SQL DatabaseにアクセスしてSQL文を実行し、結果を取得する。その結果が条件を満たす場合のみ特定の宛先にEメールを送信する。
1. Automationアカウントを作成する。
2. Runbookを作成する。
3. Runbookで実行するスクリプトを入力する。
スクリプトはPowerShellで作成する必要がある様です。
JavaScriptとかにも対応してくれると大変嬉しいのですが。^^)
今回作ったPowerShellスクリプトはこんな感じになりました。私の場合PowerShellを触る事はめったにないのですが、.NET Frameworkの機能が呼び出せるのでDBにアクセスしたりメールを送ったり、かなり色んな事が出来そうですね。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| .SYNOPSIS | |
| Outputs the result of a SQL command. | |
| .DESCRIPTION | |
| In order for this runbook to work, the SQL Server must be accessible from the runbook worker | |
| running this runbook. Make sure the SQL Server allows incoming connections from Azure services | |
| by selecting 'Allow Windows Azure Services' on the SQL Server configuration page in Azure. | |
| This runbook also requires an Automation Credential asset be created before the runbook is | |
| run, which stores the username and password of an account with access to the SQL Server. | |
| That credential should be referenced for the SqlCredential parameter of this runbook. | |
| .PARAMETER SqlServer | |
| String name of the SQL Server to connect to. | |
| .PARAMETER SqlServerPort | |
| Integer port to connect to the SQL Server on. | |
| .PARAMETER Database | |
| String name of the SQL Server database to connect to. | |
| .PARAMETER sql | |
| SQL string to execute. | |
| .PARAMETER $SqlCredName | |
| String name of the credential asset containing a username and password for the SQL Server | |
| .PARAMETER $SmtpCredName | |
| String name of the credential asset containing a username and password for the SMTP server. | |
| #> | |
| workflow CheckDatabaseAndSendEmail | |
| { | |
| param( | |
| [parameter(Mandatory=$True)] | |
| [string] $SqlServer = "xxxxxxxxxx.database.windows.net", | |
| [parameter(Mandatory=$False)] | |
| [int] $SqlServerPort = 1433, | |
| [parameter(Mandatory=$True)] | |
| [string] $Database = "MyDatabaseName", | |
| [parameter(Mandatory=$True)] | |
| [string] $sql = "EXECUTE [dbo].[BuildEmailBody] 10", | |
| [parameter(Mandatory=$True)] | |
| [string] $SqlCredName = "sqldb", | |
| [parameter(Mandatory=$True)] | |
| [string] $SmtpCredName = "smtp" | |
| ) | |
| # Get the username and password from the SQL Credential | |
| $SqlCredential = Get-AutomationPSCredential -Name $SqlCredName | |
| $SqlUsername = $SqlCredential.UserName | |
| $SqlPass = $SqlCredential.GetNetworkCredential().Password | |
| $SmtpCred = Get-AutomationPSCredential -Name $SmtpCredName | |
| inlinescript { | |
| $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;") | |
| $Conn.Open() | |
| $Cmd=new-object System.Data.SqlClient.SqlCommand($using:sql, $Conn) | |
| $Cmd.CommandTimeout=120 | |
| $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] | |
| $Conn.Close() | |
| if ($html.Length -eq 0) { | |
| Write-Output "No data was found." | |
| } else { | |
| Write-Output $html | |
| $To = "me@example.com" | |
| $From = "auto@example.com" | |
| $Subject = "Email from Azure Automation" | |
| Send-MailMessage ` | |
| -To $To ` | |
| -Subject $Subject ` | |
| -Body $html ` | |
| -UseSsl ` | |
| -Port 587 ` | |
| -SmtpServer 'smtp.sendgrid.net' ` | |
| -From $From ` | |
| -BodyAsHtml ` | |
| -Encoding ([System.Text.Encoding]::UTF8) ` | |
| -Credential $using:SmtpCred | |
| Write-Output "Email was sent." | |
| } | |
| } | |
| } | |
4. SQLサーバーとSMTPサーバーの認証情報を「資産」として登録する。
管理画面で「資産」として登録しておいた認証情報はスクリプトから
$SmtpCred = Get-AutomationPSCredential -Name $SmtpCredName
のように呼び出して使う事が出来ます。上手い仕組みになっていると思いました。
5. Runbookをテスト実行する。
新ポータルのテスト実行用UIはかなり使いづらかったので、まだ現時点ではテスト実行には旧ポータルを使った方が良いのかも知れません。
6. Runbookを「発行」する。
7. Runbookが定期的に自動実行される様にスケジュールを設定する。
必要であればWebHookをトリガーにしてジョブが走る様に設定する事も出来る様です。
以上、大体こんな感じでやりたかった事が出来ました！
やってみた後で言うのもアレですが、Azure Automationはどうもどちらかと言うとAzure内のVM(や各種サービス)の起動・停止・設定変更やバックアップなどのプロビジョニングの自動化のために設計されたもののような気がして来ました。実際、提供されているテンプレートもそういうものが大半でした。そう考えるとスクリプトがPowerShell前提なのもうなずけます。
もしかすると今回の様なアプリケーション寄りのバッチ処理にはAzure App Serviceの「Web Jobs」の方を使った方が良いのかも知れません。Web Jobsの方はJavaScript（C#, BATなども）の実行も出来るみたいです。
参考URL：
PowerShellでSQL文の実行結果件数を取得してみました - Qiita
http://qiita.com/kurukurupapa@github/items/7f455b879e7c47372e3d
もりもりゲームブログ: Windows Power Shellから、ストアドプロシージャを使用する方法 http://morimorigameblogg.blogspot.com/2009/12/windows-power-shell.html
ワークフローでの Windows PowerShell コマンドの実行
https://technet.microsoft.com/ja-jp/library/jj574197.aspx
社内SEの徒然なる日記 powerShellでメールを送ってみた
http://harikofu.blog.fc2.com/blog-entry-2.html
Powershellでメールを送信する最も簡単な方法 | いろんなサーバー設定研究所
http://tech.lecume.net/power-shell/send-mail