How to list SQL Database Alert Rules using Powershell

Question

How to list SQL Database Alert Rules using Powershell

John Couch 181

I am trying to generate a report output that shows

Az SQL Database / Alert Rule

I have tried several things, but nothing really outputs. For example, I have tried:

Get-AzResource - ResourceGroupName "" -ResourceType microsoft.insights/metricAlerts

But it does not return ALL rules defined. I have 3 rules, and Data IO % is the one NOT returned. The two rules for Max Worker Threads do return. And the above command does not return the name of the database associated to the Alert Rule.

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Alberto Morillo 35,506 MVP Volunteer Moderator

What happen if you run the following:

$names = (Get-AzResource -ResourceGroupName yourresourcegroup-ResourceType microsoft.insights/metricAlerts).Name  
foreach($name in $names){  
    Get-AzResource -ResourceGroupName yourresourcegroup -Name $name -ResourceType microsoft.insights/metricAlerts | ConvertTo-Json  
}

What about using Azure CLI as explained here and as shown below:

az monitor alert list

John Couch 181 Reputation points

2021-12-21T22:28:02.037+00:00

The result is a warning that states the command is deprecated. Nothing else happens and the command doesn't run.
Alberto Morillo 35,506 Reputation points MVP Volunteer Moderator

2021-12-22T03:15:54.677+00:00

What abou the Axure CLI command?
John Couch 181 Reputation points

2021-12-22T15:44:56.217+00:00

Thats the one that says it's deprecated, however, I noticed the message was cut off, and it says to use az monitor metrics alert list now. The below command works for what I need. It's not totally clean output, as the scopes is the only way to get the database its aligned too, but it works.

az monitor metrics alert list --resource-group MyResourceGroupName --query "[].{resource:resourceGroup, name:name, scopes:scopes[0]}" --output Table

Thanks for the assist.
Saurabh Sharma 23,866 Reputation points Microsoft Employee Moderator

2021-12-31T00:07:24.053+00:00

@John Couch , Thanks for the confirmation and sharing your findings as well. This will help other community members as well.
If you find the above answer useful then please accept @Alberto Morillo 's reply as an answer. This will help other community members facing similar query to refer to this solution.

Thanks
Saurabh

Share via

How to list SQL Database Alert Rules using Powershell

0 additional answers

Your answer