question

JohnCouch-7779 avatar image
1 Vote"
JohnCouch-7779 asked AlbertoMorillo commented

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

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

https://docs.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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

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.



· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Alberto for replying. A quick question, I thought Add-AzureRMMetricAlertRule was being deprecated for the Add-AzMetricAlertRuleV2 which added stuff like Severity and changed the naming to AZ from Azure?

I assume, and maybe incorrectly, that this is specific to the way you name your resource groups? $.ResourceGroupName -eq $rg -and $.kind -eq "v12.0,user"

0 Votes 0 ·

I checked the $_.kind piece and see now why you did that. Thank you again.

If you have a second to reply back about the V2 question I would appreciate it. Thank you in advance.

0 Votes 0 ·

One of the resource group names had a slash that was problematic. That is the reason behind how the resource group name has been handled.

0 Votes 0 ·

II agree with you that parameters of Add-AzureRmMetricAlertRule] are deprecated and you may get the following warning when debugging:
WARNING: [Add-AzureRmMetricAlertRule] Parameter name change: The parameter plural names for the parameters will be deprecated in a future breaking change

0 Votes 0 ·

I added an article with an example using Add-AzMetricAlertRuleV2 to my original answer.

0 Votes 0 ·
Show more comments
JohnCouch-7779 avatar image
0 Votes"
JohnCouch-7779 answered AlbertoMorillo commented

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
             }


· 11
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Later today I will run it to see what you mean.

0 Votes 0 ·

I realize this is breaching the point of this question, but I believe I got it working, however, I cannot apparently add multiple Action Group IDs to an alert. Do you happen to know how to do that? I tried if you look at the attached code.

0 Votes 0 ·

Exactly. You can define only one action group per alert. However, you can create an action group that is used by many alerts.

0 Votes 0 ·
Show more comments

When you test, so we are on the same page, I am uploading this script to a Azure Container File Share and running it from the Shell.

0 Votes 0 ·
Show more comments
JohnCouch-7779 avatar image
1 Vote"
JohnCouch-7779 answered AlbertoMorillo commented

@AlbertoMorillo - 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That's a masterpiece. Excellent job done, You don't give up.

0 Votes 0 ·