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

This article is contributed. See the original author and article here.

This blog focuses on how you can 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 key has expired the server goes in inaccessible state. This is by design to avoid security violations. To detect these conditions and get alerted when the server goes in inaccessible state, you can 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: Make your Environment ready for Azure Automation runbook

1. Create a 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 sendgrid account (How to link)
   
   
   
   1. Click on Manage and get the account name for the alias you used while creating the SendGrid account
   
   
   2. Go to Settings , Select Accounts details and make a note of the username
      
      
      
      
   
   
   
   

Step 2: Create a Runbook in Azure Automation account

1. From the portal check the Azure automation account you have created.


2. Click on Runbooks and 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

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.