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

      1.jpg





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

    2.jpg


  3. Give the Name  and Runbook Type  (PowerShell)

    3.jpg


  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.
    4.jpg




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

    5.jpg


  2. Select Create a Webhook

    6.jpg



  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.

    7.jpg



  4. Click on Create

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

    8.jpg



  6. Give the details and click on Review and Create

    9.jpg




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

    10.jpg




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

    11.jpg




  9. Select the HTTP webhook

    12.jpg



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

    13.jpg


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.


 


14.jpg


 


 


Hope this helps !!!


 


Sudheesh Narayanaswamy

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

%d bloggers like this: