How to Create an Azure Alert using Powershell for SQL Database?

John Couch 181 Reputation points
2021-08-16T23:47:04.357+00:00

This doesn't fit for Azure SQL Database or Managed Instance.

https://learn.microsoft.com/en-us/powershell/module/az.monitor/add-azmetricalertrulev2?view=azps-6.3.0

and this method is apparently out dated and no longer valid.

https://www.mikefal.net/2016/08/23/creating-alerts-for-azure-sql-database-with-powershell/

Does anyone know how to create an alert using the new syntax for Azure SQL Database? One the specifies severity...etc. like the V2 in the first link?

Azure SQL Database
0 comments No comments
{count} vote

Accepted answer
  1. Alberto Morillo 32,981 Reputation points MVP
    2021-08-17T04:40:10.4+00:00

    The following script creates DTU alert rules that will monitor for the DTU going above 90 % for the last 15min. It will create alerts all databases within a resource group.

    #define variable for resource group name by requesting keyboard input
    
    $rg = Read-Host 'Please, input resource group name here (exactly as it is in Azure)'
    
    <#create the array containing databases where alerts are required. The value of v12.0,user corresponds to the kind of resource as to include only the SQL DBs and not the SQL servers#>
    
    $resources = Get-AzureRmResource | ?{ $_.ResourceGroupName -eq $rg -and $_.kind -eq "v12.0,user"  } | select resourcename,resourceid
    
    #loop through the array and create the alert rule for each DB
    
    foreach($resource in $resources){$alertname=$resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1);Add-AzureRMMetricAlertRule -ResourceGroup $rg -location "centralus" -targetresourceid $resource.resourceid -Name $alertname -MetricName "dtu_consumption_percent" -Operator "GreaterThan" -Threshold 90 -WindowSize $([TimeSpan]::Parse("00:15:00")) -TimeAggregationOperator "Average" -verbose -Actions $(New-AzureRmAlertRuleEmail -SendToServiceOwners -CustomEmails "Client-address@domain.com")}
    

    You can also find a complete example using Add-AzMetricAlertRuleV2 on this article.


2 additional answers

Sort by: Most helpful
  1. John Couch 181 Reputation points
    2021-08-24T16:23:55.483+00:00

    @Alberto Morillo - I got it working. Here is the iteration I am on. I say that because I still have adjustments to make, but this creates an alert with multiple Action Groups.126124-set-azsqldb-alerts.txt

    1 person found this answer helpful.

  2. John Couch 181 Reputation points
    2021-08-19T00:27:21.29+00:00

    OK, so I managed to create the alert! Thank you! Now I have another question. If the action group and the alert do not exist, this works fine, but if it exists, it fails saying the ENgActGrp value is NULL or Empty. How is that even remotely possible when it says it found it and populated the variable?

     $ResourceType = "Microsoft.Sql/servers/databases"
    
                <#
                    Action Group "Database Engineering Team"
                #>
                $EngActGrp = Get-AzActionGroup -ResourceGroupName $ResourceGroup | Select-Object Name | Where-Object {$_.Name -eq "Database Engineering Team"}
    
                if (!$EngActGrp)
                {
                    Write-Host "Not Found"
                    $EmailReciever = New-AzActionGroupReceiver -Name "Database Engineering Team" -EmailReceiver -EmailAddress "MyEMail@MyWork.com"
    
                    $EngActGrp = Set-AzActionGroup -Name "Database Engineering Team" -ShortName "Db Eng Team" -ResourceGroupName $ResourceGroup -Receiver $EmailReciever
                }
                else
                {
                    Write-Host "Found"
                }
    
                $EngActGrpId = New-AzActionGroup -ActionGroupId $EngActGrp.Id
    
    
                <#
                    Alert Rule "DTU > 90%"
                #>
                $MarDTUPct = Get-AzMetricAlertRuleV2 -ResourceGroupName $ResourceGroup -Name "DBA-MAR-DTU-GT90"
    
                if (!$MarDTUPct)
                {
                    $DTUPctCondition = New-AzMetricAlertRuleV2Criteria -MetricName "dtu_consumption_percent" -MetricNameSpace $ResourceType -TimeAggregation Average `
                                                                       -Operator GreaterThan -Threshold 90
    
                    $TargetResourceId = (Get-AzResource -ResourceGroupName $ResourceGroup -ResourceType $ResourceType -Name $Database).ResourceId
    
                    Add-AzMetricAlertRuleV2 -Name "DBA-MAR-DTU-GT90" -ResourceGroupName $ResourceGroup -WindowSize 00:15:00 -Frequency 00:01:00 -TargetResourceScope $TargetResourceId `
                                            -TargetResourceType $ResourceType -TargetResourceRegion $Region -Description "DBA Avg DTU > 90%" -Severity 2 -Condition $DTUPctCondition `
                                            -ActionGroup $EngActGrpId
                }