Get alerted when your Azure Database for MySQL server is not accessible!!!

[Sudheesh_N]

This blog post focuses on how to use the combination of Azure Automation, Logic App, Sendgrid, and Webhook Azure services to send email notification alerts when your Azure Database for MySQL server status changes. 

If you are using data encryption with customer managed key in Azure Database for MySQL, if there is an issue with reading from the Azure Key vault, any permission issues, or if the key has expired, then the server goes into an inaccessible state. This is by design to avoid security violations. To detect these conditions and receive alerts when the server goes into an inaccessible state, run following command:

az mysql server show -g <ResourceGroupName>-n <servername>--query [fullyQualifiedDomainName,userVisibleState] -o json

The below solution uses 

• Azure Automation runbook to run and check the server status with the az modules
• Sendgrid to send the mail
• Webhook and Logic App to make the schedule to run every 15 mins

Step 1: Prepare your environment for Azure Automation runbook

1. Create an Azure automation account  (How to link)
2. Import the following module (How to link)
   1. Az.Accounts
   2. Az.Automation
   3. Az.MySql
3. Create a SendGrid account (How to link)
   1. Select Manage and get the account name for the alias you used while creating the SendGrid account
   2. Go to Settings, select Accounts details, and then make a note of the Username.
      
      
      
      

Step 2: Create a Runbook in the Azure Automation account

1. From the portal, check the Azure automation account you have created.
2. Select Runbooks and then select Create a Runbook.
   
   
   
   
3. Give the Name  and Runbook Type  (PowerShell)
   
   
   
   
4. Click on Create below
5. Once created then select the runbook "Mysql_Server_Status" and click on Edit.
6. Copy paste the following script and make the following changes with the data we have received. Click Save and Publish.
   

   Import-Module Az.Accounts
   Import-Module Az.Automation
   import-Module Az.MySql
   $connectionName = "AzureRunAsConnection"
   $EmailTo = "<Alias>@domain.com"
   $smtpServer = "smtp.sendgrid.net"
   $smtpFrom = "No-reply@azureadmin.com"
   $messageSubject = "The Azure Database for MySQL is not Available"
   try
   {
       # Get the connection "AzureRunAsConnection "
       $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName       
       "Logging in to Azure..."
       Connect-AzAccount `
           -ServicePrincipal `
           -TenantId $servicePrincipalConnection.TenantId `
           -ApplicationId $servicePrincipalConnection.ApplicationId `
           -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
   }
   catch {
       if (!$servicePrincipalConnection)
       {
           $ErrorMessage = "Connection $connectionName not found."
           throw $ErrorMessage
       } else{
           Write-Error -Message $_.Exception
           throw $_.Exception
       }
   }
   #Get all Mysql resources status which are not in ready state
   $mysqlservers = Get-AzMysqlServer | Where-Object {$_.UserVisibleState -ne "Ready"}
   if ($mysqlservers.count -gt 0)
   {
   foreach ($mysqlserver in $mysqlservers)
       {    
           #Write-Output ($mysqlserver.Name + " Current state is : " +  $mysqlserver.UserVisibleState)
           $Body = $mysqlserver.Name + " Current state is : " +  $mysqlserver.UserVisibleState
           $message = New-Object System.Net.Mail.MailMessage
           $message.From="No-reply@azureadmin.com"
           $message.to.add($EmailTo)
           $message.Subject = $messageSubject
           $message.Body = $Body
           $message.IsBodyHTML = $false
           $smtp = New-Object Net.Mail.SmtpClient($smtpServer,"587")
           #Add your Sendgridusername and sendgridpassword here:
           $credentials=new-object system.net.networkcredential("username_xxxxxxxxxx@azure.com","P@ssw0rd")
           $smtp.credentials=$credentials.getcredential($smtpServer,587,"basic")
           $smtp.Send($message)
       } 
   }​

7. If you are ok to do this check once in hour you can directly got to schedule for runbooks and skip Step 3 below.
   

Step 3: Scheduling the runbook

Since the frequency we require is lesser than one hour which is now not available we will use webhooks and logic app to do it to achieve monitoring at the minutes granularity.

1. On the overview please click on Add webhook
   
   
   
   
2. Select Create a Webhook
   
   
   
   

3. Give the Name and Make sure you copy the URL from the below and keep it . Expire date also you can set as per you need.
   
   
   
   

4. Click on Create
5. Now go to Logic App from portal and click on ADD
   
   
   
   

6. Give the details and click on Review and Create
   
   
   
   

7. Once you go to the Logic app created you will see Logic Apps Designer , Select Recurrence
   
   

8. Select the interval as 15 Frequency in Minute and click on New step
   
   
   
   

9. Select the HTTP webhook
   
   
   
   

10. Select  Subscribe Method as POST and Subscribe  URI copy the URI you got while creating the webhook  (Step 3 , Section 3)
    
    

Once you do this the Logic app will trigger the webhook and that will in-turn trigger the script to run every 15 mins and if there is any of the MySQL servers which are not in ready state, an e-mail notification will be triggered as shown below.

Hope this helps !!!

Sudheesh Narayanaswamy