Azure cmdlet: How to group Results with Invoke-AzCostManagementQuery?

Microsoft has introduced a powerful cmdlet called Invoke-AzCostManagementQuery, which is part of the Az.CostManagement module. This makes it possible to generate customized cost reports in all possible dimensions.

The parameters are (actually) quite simple:

 1. The scope to be surveyed
 2. the desired time period
 3. the aggregation of the costs
 4. the granularity
 5. if necessary, grouping according to specifications

Unfortunately, the documentation is so poor that even the support team at Microsoft has its problems issuing correct commands.

I was able to successfully create the following query until now:

First, I set the desired Scope:

$scope = "/subscriptions/xxxxxx"

Second, I define the aggregation of my costs:

$aggregation = @{
  
  totalCost = @{
    name     = "Cost"
    function = "SUM"
  }
}

Third, I run this command:

Invoke-AzCostManagementQuery `
  -Scope $scope `
  -Timeframe 'DateToMonth' `
  -Type 'Usage' `
  -DatasetGranularity "Daily" `
  -DatasetAggregation $aggregation

I receive the daily costs of my defined scope:

Column                      Row
------                      ---
{Cost, UsageDate, Currency} {922,7007638124661 20230301 EUR, 944,695760723976 20230302 EUR, 870,8345694362154 20230303 EUR, 731,7461005886661 20230304 EUR…}

When I add the Grouping Filter:

$grouping = @{
  name      = "azcosts"
  type      = "TagKey"
}

I receive the desired Output in the Debug Log, but the query ends up with an error Invoke-AzCostManagementQuery: You cannot call a method on a null-valued expression.

To group it by a single tag already defined on the resources you could do something like this:

  $grouping = @{
    name      = "owner"
    type      = "TagKey"
  }

  
$test = Invoke-AzCostManagementQuery `
    -Scope $scope `
    -Timeframe 'DateToMonth' `
    -Type 'Usage' `
    -DatasetGranularity "Daily" `
    -DatasetAggregation $aggregation `
    -DatasetGrouping $grouping
Return $test

On my ressources it has a tag called owner

So in my instance it returns something like this:

Column   : {Cost, UsageDate, TagKey, TagValue…}
ETag     : 
Id       : 
Location : 
Name     : 
NextLink : 
Row      : {64,45890538046535 20230301 owner mynster DKK, 62,92332096662609 20230302 owner mynster DKK, 63,05880866997392 20230303 owner mynster DKK, 63,04782384544536 20230304 owner mynster DKK…}
Sku      : 
Tag      : Microsoft.Azure.PowerShell.Cmdlets.CostManagement.Models.Api20211001.ResourceTags
Type     : 
Property : Microsoft.Azure.PowerShell.Cmdlets.CostManagement.Models.Api20211001.QueryProperties

I do not exactly have an easy way to explain how i found out but can try.

If you go to the azure portal and go to cost management and configure your query once you are ready to go ahead with your query press <kbd>F12</kbd> and go to Network.

In there you will find all communication from your PC to the website and the query it runs.

It should look something like this:

Press on it and go to Payload in here you can see how the browser constructed the payload for your result like shown here:

In this example i am looking at the Tag named "costcenter"

And just to add to this if you want more you can just put the grouping in an array like this and then add as many as you would like

$grouping = @()
$grouping += @{
    name = "owner"
    type = "TagKey"
}
$grouping += @{
    name = "ResourceGroupName"
    type = "Dimension"
}