Azure Workbook - Query/ Json Script to show the cost data of the result of a different query

Question

I am creating an Azure workbook that will show all the storage accounts that are set to GRS and then the monthly cost to date of those storage accounts. The first bit works fine where the KQL query is returning the correct results i.e. storage accounts that are set to GRS. I am just lost on how to create a second query or json script that shows the cost data for the result of this first query.

First query that runs fine is as follows (this query returns the list of all storage accounts) :

`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
`

Please guide on the second query or json script that i should write that shows cost data of the result of this first query. Many thanks in advance...

i tried copying this json script from somewhere else i know this would not work but could be a starting point :

`{
  "type": "Usage",
  "timeframe": "MonthToDate",
  "dataset": {
    "granularity": "Monthly",
    "aggregation": {
      "totalCost": {
        "name": "PreTaxCost",
        "function": "Sum"
      }
    },
    "filter": {
      "and": [
        {
          "dimensions": {
            "name": "ResourceGroup",
            "operator": "In",
            "values": [
              
              "StorageAccounts"
            ]
          }
        },
        {
          "dimensions": {
            "name": "ServiceName",
            "operator": "In",
            "values": [
              "Geo-Replicated Storage"
            ]
          }
        }
      ]
    },
    "grouping": [
      {
        "type": "Dimension",
        "name": "ResourceGroup"
      },
      {
        "type": "Dimension",
        "name": "ResourceId"
      }
    ]
}
  }
`

Answer 1

In general you'll have to probably do 2 queries, and then a third query using the Merge datasource to join them.

1. query 1: probably datasource = Azure Resource Graph to find the appropriate resources?

2. query 2: probably datasource = ARM, to the cost management APIs to get the appropriate details, using JSONPath to filter down the results to just the data you want/need for your resource type(s)

3. query 3: datasource = merge, doing a join between the 2 above queries. see https://learn.microsoft.com/en-us/azure/azure-monitor/visualize/workbooks-data-sources#merge for more details, examples about merge