Azure Workbook：如何查询GRS存储账户的本月累计成本

一、已实现的GRS存储账户筛选查询 #

已成功筛选出dev、test、train、uat资源组中配置为GRS的存储账户，KQL查询如下：

resources
| where type == "microsoft.storage/storageaccounts"
| where resourceGroup has "dev" or resourceGroup has "test" or resourceGroup has "train" or resourceGroup has "uat"
| where sku has "GRS"
| extend skuName=tostring(sku.name)
| extend accountType=case(skuName =~ 'Standard_LRS', 'Standard HDD LRS',
                          skuName =~ 'StandardSSD_LRS', 'Standard SSD LRS',
                          skuName =~ 'UltraSSD_LRS', 'Ultra disk LRS',
                          skuName =~ 'Premium_LRS', 'Premium SSD LRS',
                          skuName =~ 'Standard_ZRS', 'Zone-redundant',
                          skuName =~ 'Premium_ZRS', 'Premium SSD ZRS',
                          skuName =~ 'StandardSSD_ZRS', 'Standard SSD ZRS',
                          skuName)
| extend securityTypeString=tostring(properties.securityProfile.securityType)
| extend securityType=case(securityTypeString =~ 'Standard', 'Standard',
                          securityTypeString =~ 'TrustedLaunch', 'Trusted launch',
                          securityTypeString startswith 'ConfidentialVm', 'Confidential',
                          securityTypeString == '', '-',
                          '-')
| extend architecture=iff(tostring(properties.supportedCapabilities.architecture) =~ 'Arm64', 'Arm64', 'x64')
| extend timeCreated=tostring(properties.creationTime)
| extend size=tostring(properties.diskSizeGB)
| extend iops=strcat(tostring(properties.diskIOPSReadWrite), '/', tostring(properties.diskMBpsReadWrite))
| extend owner=coalesce(split(managedBy, '/')[(-1)], '-')
| extend diskStateProperty=tostring(properties.provisioningState)
| extend diskState=case(diskStateProperty =~ 'Succeeded', 'Attached',
                        diskStateProperty =~ 'Creating', 'Creating',
                        diskStateProperty =~ 'Resolving', 'Resolving',
                        diskStateProperty =~ 'Updating', 'Updating',
                        diskStateProperty =~ 'Deleting', 'Deleting',
                        diskStateProperty =~ 'Failed', 'Failed',
                        diskStateProperty =~ 'Canceled', 'Canceled',
                        diskStateProperty == '', '-',
                        coalesce(diskStateProperty, '-'))
| extend osType=coalesce(properties.osType, '-')
| extend provisioningState=coalesce(properties.provisioningState, '-')
| extend sourceId=tostring(coalesce(properties.creationData.imageReference.id, properties.creationData.sourceUri, properties.creationData.sourceResourceId))
| parse kind=regex sourceId with '/Publishers/' publisher '/ArtifactTypes/(.*)/Offers/' offer '/Skus/' sku '/Versions/' version
| extend createOption=tostring(properties.creationData.createOption)
| extend source=case(createOption =~ 'empty', '-',
                     createOption =~ 'copy', split(sourceId, '/')[(-1)],
                     createOption =~ 'import', sourceId,
                     createOption =~ 'FromImage', strcat(publisher, ' / ', offer, ' / ', sku, ' / ', version),
                     '-')
| extend shareCapacity = iff(properties.shareCapacityInBytes > 0, strcat(tostring(properties.shareCapacityInBytes), ' GiB'), 'N/A')
| project 
    name, 
    resourceGroup, 
    location,
    kind,
    accountType

二、目标存储账户本月累计成本获取方案 #

方案1：KQL关联查询（推荐） #

直接关联resources与CostManagement数据集，一次性输出存储账户信息及对应成本：

// 筛选目标GRS存储账户
let targetStorageAccounts = resources
| where type == "microsoft.storage/storageaccounts"
| where resourceGroup has "dev" or resourceGroup has "test" or resourceGroup has "train" or resourceGroup has "uat"
| where sku has "GRS"
| extend skuName=tostring(sku.name)
| extend accountType=case(skuName =~ 'Standard_LRS', 'Standard HDD LRS',
                          skuName =~ 'StandardSSD_LRS', 'Standard SSD LRS',
                          skuName =~ 'UltraSSD_LRS', 'Ultra disk LRS',
                          skuName =~ 'Premium_LRS', 'Premium SSD LRS',
                          skuName =~ 'Standard_ZRS', 'Zone-redundant',
                          skuName =~ 'Premium_ZRS', 'Premium SSD ZRS',
                          skuName =~ 'StandardSSD_ZRS', 'Standard SSD ZRS',
                          skuName)
| project 
    name, 
    resourceGroup, 
    location,
    accountType,
    resourceId;

// 关联成本数据，计算本月累计成本
CostManagementResources
| where Timeframe == "MonthToDate"
| where ResourceId in (targetStorageAccounts | project resourceId)
| summarize totalMonthlyCost = sum(PreTaxCost) by resourceId
| join kind=inner targetStorageAccounts on resourceId
| project 
    name,
    resourceGroup,
    location,
    accountType,
    totalMonthlyCost

方案2：修正后的JSON查询脚本 #

原JSON脚本存在ResourceGroup值错误、未精准筛选GRS账户等问题，以下是修正后的版本，可直接用于Azure Workbook的"Usage"类型数据源：

{
  "type": "Usage",
  "timeframe": "MonthToDate",
  "dataset": {
    "granularity": "Monthly",
    "aggregation": {
      "totalCost": {
        "name": "PreTaxCost",
        "function": "Sum"
      }
    },
    "filter": {
      "and": [
        {
          "dimensions": {
            "name": "ResourceGroup",
            "operator": "In",
            "values": [
              "dev",
              "test",
              "train",
              "uat"
            ]
          }
        },
        {
          "dimensions": {
            "name": "ServiceName",
            "operator": "In",
            "values": [
              "Storage"
            ]
          }
        },
        {
          "dimensions": {
            "name": "SKUName",
            "operator": "Contains",
            "values": [
              "GRS"
            ]
          }
        }
      ]
    },
    "grouping": [
      {
        "type": "Dimension",
        "name": "ResourceGroup"
      },
      {
        "type": "Dimension",
        "name": "ResourceId"
      },
      {
        "type": "Dimension",
        "name": "ResourceName"
      }
    ]
  }
}

修正要点：

• 将ResourceGroup的values替换为目标资源组名称（dev/test/train/uat）
• 调整ServiceName为Azure成本数据标准值"Storage"
• 添加SKUName筛选条件，确保仅包含GRS类型存储账户
• 新增ResourceName分组，便于直接关联存储账户名称

内容的提问来源于stack exchange，提问作者Ali