powershell - 如何通过 foreach 循环从 azure 订阅检索所有数据库并堆栈到变量中

Question

我有以下内容，可以很好地提取基于非资源组的所有数据库的列表，并将其存储到名为 $resources 的变量中

$resources = Get-AzureRmResource | ?{ $_.ResourceGroupName -eq "resourceGROUPnameHERE" -and $_.kind -eq "v12.0,user"  } | select resourcename,resourceid

输出如下所示(每个资源名称和资源 ID 分为两列)

ResourceName              ResourceId
------------              ----------
georgidbserver1/georgiDB1 /subscriptions/aaaaaa-bbbb-486a-96cf-6c68361d8000/resourceGroups/georgigroup/providers/Microsoft.Sql/servers/georgidbserver1/databases/georgiDB1
georgidbserver1/georgiDB2 /subscriptions/aaaaaa-bbbb-486a-96cf-6c68361d8000/resourceGroups/georgigroup/providers/Microsoft.Sql/servers/georgidbserver1/databases/georgiDB2

我希望实现的是获得相同的输出并以相同的方式将其存储到变量中，但不是针对单个资源组，而是我希望它检查我的订阅中的所有资源组并从中提取所有数据库他们在列表中。

我创建了以下代码，它在 powershell 窗口中准确输出我需要的内容

代码是

$rg = Get-AzureRmResourceGroup | ?{ $_.ProvisioningState -eq "succeeded" } | select -expandproperty resourcegroupname
foreach ($resourcegroup in $rg) {Get-AzureRmResource | ?{ $_.ResourceGroupName -eq $resourcegroup -and $_.kind -eq "v12.0,user"  } | select resourcename,resourceid}

输出看起来像这样(正是我想要的)

PS C:\Users> .\small.ps1

ResourceName              ResourceId
------------              ----------
georgidbserver2/georgiDB3 /subscriptions/17e1cc9e-4eb3-486a-96cf-6c68361d8000/resourceGroups/dbgroup/providers/Microsoft.Sql/servers/georgidbserver2/databases/georgiDB3
georgidbserver2/georgiDB4 /subscriptions/17e1cc9e-4eb3-486a-96cf-6c68361d8000/resourceGroups/dbgroup/providers/Microsoft.Sql/servers/georgidbserver2/databases/georgiDB4
georgidbserver1/georgiDB1 /subscriptions/17e1cc9e-4eb3-486a-96cf-6c68361d8000/resourceGroups/georgigroup/providers/Microsoft.Sql/servers/georgidbserver1/databases/georgiDB1
georgidbserver1/georgiDB2 /subscriptions/17e1cc9e-4eb3-486a-96cf-6c68361d8000/resourceGroups/georgigroup/providers/Microsoft.Sql/servers/georgidbserver1/databases/georgiDB2

我的问题是如何将产生 4 行的第二个代码的输出存储到变量中，就像在 $resources 的第一个示例中一样。

我希望能够稍后循环它并为订阅中的所有数据库创建数据库警报。我已经有了为每个资源组的所有数据库创建警报的代码。在这里，我希望实现相同的目标，但对于所有数据库订阅中的数据库

下面是我用于为每个资源组创建数据库警报的完整代码，仅供引用

<#create CPU,DTU and Storage alerts for as many databases as you have in the resource group provided by keyboard input#>

#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 DTU alert rule for each DB

foreach($resource in $resources){$alertname=$resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1)+"-DTU-Alert";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-email@here")}

#loop through the array and create the CPU alert rule for each DB

foreach($resource in $resources){$alertname=$resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1)+"-CPU-Alert";Add-AzureRMMetricAlertRule -ResourceGroup $rg -location "centralus" -targetresourceid $resource.resourceid -Name $alertname -MetricName "cpu_percent" -Operator "GreaterThan" -Threshold 90 -WindowSize $([TimeSpan]::Parse("00:15:00")) -TimeAggregationOperator "Average" -verbose -Actions $(New-AzureRmAlertRuleEmail -SendToServiceOwners -CustomEmails "Client-email@here")}

#loop through the array and create the STORAGE alert rule for each DB

foreach($resource in $resources){$alertname=$resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1)+"-STORAGE-Alert";Add-AzureRMMetricAlertRule -ResourceGroup $rg -location "centralus" -targetresourceid $resource.resourceid -Name $alertname -MetricName "storage_percent" -Operator "GreaterThan" -Threshold 90 -WindowSize $([TimeSpan]::Parse("00:15:00")) -TimeAggregationOperator "Average" -verbose -Actions $(New-AzureRmAlertRuleEmail -SendToServiceOwners -CustomEmails "Client-email@here")}

Answer 1

您可以使用嵌套循环。以下脚本对我有用。

$rg = Get-AzureRmResourceGroup | ?{ $_.ProvisioningState -eq "succeeded" } | select -expandproperty resourcegroupname
foreach ($resourcegroup in $rg) 
{
    $resources = Get-AzureRmResource | ?{ $_.ResourceGroupName -eq $resourcegroup -and $_.kind -eq "v12.0,user"  } | select resourcename,resourceid,location
    foreach ($resource in $resources)
    {
        ##create the DTU alert rule for each DB
        $alertname = $resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1)+"-DTU-Alert"
        Add-AzureRMMetricAlertRule -ResourceGroupName $resourcegroup  -Location $resource.location -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 "<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="89fdecfafdc9e1e6fde4e8e0e5a7eae6e4" rel="noreferrer noopener nofollow">[email protected]</a>")

        ##create the CPU alert rule for each DB
        $alertname=$resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1)+"-CPU-Alert"
        Add-AzureRMMetricAlertRule -ResourceGroupName $resourcegroup  -Location $resource.location -targetresourceid $resource.resourceid -Name $alertname -MetricName "cpu_percent" -Operator "GreaterThan" -Threshold 90 -WindowSize $([TimeSpan]::Parse("00:15:00")) -TimeAggregationOperator "Average" -verbose -Actions $(New-AzureRmAlertRuleEmail -SendToServiceOwners -CustomEmails "<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a8dccddbdce8c0c7dcc5c9c1c486cbc7c5" rel="noreferrer noopener nofollow">[email protected]</a>")

        ##create the STORAGE alert rule for each DB
        $alertname=$resource.resourcename.Substring($resource.resourcename.IndexOf('/')+1)+"-STORAGE-Alert"
        Add-AzureRMMetricAlertRule -ResourceGroupName $resourcegroup  -Location $resource.location -targetresourceid $resource.resourceid -Name $alertname -MetricName "storage_percent" -Operator "GreaterThan" -Threshold 90 -WindowSize $([TimeSpan]::Parse("00:15:00")) -TimeAggregationOperator "Average" -verbose -Actions $(New-AzureRmAlertRuleEmail -SendToServiceOwners -CustomEmails "<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0f7b6a7c7b4f67607b626e6663216c6062" rel="noreferrer noopener nofollow">[email protected]</a>")
    }
}