Working with Microsoft.CostManagement queries (1/2)
Recently I started playing with the Microsoft.CostManagement APIs. I was looking for specific details (Partner Earned Credits in this case). I found that dealing with the Microsoft.Consumption APIs and the usageDetails API in particular was generating a lot of data and took too much time to go through looking for specific properties. Time for another approach and Microsoft.CostManagement/query was the answer for me.
The Cost Management APIs allow for querying. This is exactly what's happening when you use Cost Management through the Azure Portal. After examining some traffic and reading up on some documentation it was time for me to get started.
This post is the first one in a series of two. I will focus on the basic workings of the Cost Management query API first and follow up with a post on how to do this programmatically.
The Use Case
As I am working for a Cloud Service Provider and we switched to the Modern Commerce model (Azure Plan) we now have the ability to use Azure Cost Management and manage the billing account instead of going through Partner Center. Prior to modern commerce we had to query the APIs in Partner Center. This generated so much data that the API was not useful for querying simple things.
Cost Management provides the answer. We can query the Cost Management APIs and request just the data we need. This opens new doors, for example to automating the detection of specific properties and perform an action based on that.
What I needed was a the information to determine whether we are receiving Partner Earned Credits on customer resources or if we were missing out. But I quickly learned you can do much more!
Let's get technical
For this to work you need something to perform POST request. This can be done programmatically with any language you prefer or you can use Postman to get your queries right before you start writing code. In these examples I will be using Postman.
First I had to figure out how to write the queries. Yes you can learn that from the documentation but I quickly learned that documentation differed a bit from reality.
Please note: When you are reading the documentation on how to query, when a "dimension" is mentioned, when you're using multiple dimensions as I will in these examples you actually need to use "dimensions" or you will end up with an error similar to:
{
"error": {
"code": "BadRequest",
"message": "Invalid query definition: Invalid dataset filter; on a QueryFilter one and only one of and/or/not/dimension/tag can be set.\r\nInvalid dataset filter; on a QueryFilter one and only one of and/or/not/dimension/tag can be set.\r\nInvalid dataset filter; on a QueryFilter one and only one of and/or/not/dimension/tag can be set.\r\n\r\n (Request ID: xxxxxxxxxxxxxxx)"
}
}
Alright. First things first. I didn't really go with all the documentation but decided to just inspect what was happening when I used Azure Cost Management through the Azure Portal. When I started adding filters in Azure Cost Management I could see the exact queries being made. This helps for building your first query as you don't have to start from scratch.
What's important here is the "dataSet". This is where the magic happens. You can read up on these data sets here (QueryDataset).
In my example I wanted to look for Partner Earned Credits and being able to filter on "true" or "false" and also return the subscription information. Partner Earned Credits are calculated on a resource level but having the subscription name and ID is a good start.
This resulted in the following query:
{
"type": "ActualCost",
"timeframe": "MonthToDate",
"dataSet": {
"granularity": "Daily",
"aggregation": {
"totalCost": {
"name": "Cost",
"function": "Sum"
},
"totalCostUSD": {
"name": "CostUSD",
"function": "Sum"
}
},
"sorting": [
{
"direction": "ascending",
"name": "UsageDate"
}
],
"grouping": [
{
"type": "Dimension",
"name": "SubscriptionId"
},
{
"type": "Dimension",
"name": "SubscriptionName"
},
{
"type": "Dimension",
"name": "ChargeType"
},
{
"type": "Dimension",
"name": "PublisherType"
}
],
"filter": {
"And": [
{
"Dimensions": {
"Name": "PartnerEarnedCreditApplied",
"Operator": "In",
"Values": [
"true"
]
}
},
{
"Dimensions": {
"Name": "CustomerTenantId",
"Operator": "In",
"Values": [
"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
]
}
}
]
}
}
}
Let's break it down. The "type" property is actually the ExportType. You can choose between ActualCost, AmortizedCost and Usage.
Then we have the "timeframe". This can be a custom timeframe but for my scenario the MonthToDate works fine. Granualarity can be set to daily or "none". It really depends on what you want your output to look like whether you should set this.
We can then configure the aggregation. I want to know the totalCost and the totalCost in USD so I know what I'm missing. I think sorting speaks for itself.
Next we can set the grouping. This is no more than a "Group By" statement it you fancy SQL, just formatted differently.
Now we're getting to the interested part. The filter.
"filter": {
"And": [
{
"Dimensions": {
"Name": "PartnerEarnedCreditApplied",
"Operator": "In",
"Values": [
"true"
]
}
},
{
"Dimensions": {
"Name": "CustomerTenantId",
"Operator": "In",
"Values": [
"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
]
}
}
]
}
```The filter allows us to return or search for specific properties. We'll write those as "dimensions". I'm looking for the PartnerEarnedCreditApplied property for a specific customer. Basically what we are saying is: PartnerEarnedCreditApplied = true and CustomerTenantId = tenantId.
If you want to read up on all the features, check here.
Executing the query
The API expects a POST so we need to set this up.
As we're using Postman we can past the URI. The URI is build like so:
If you are unsure what your Billing Account Name is you can use Azure PowerShell and run Get-AzBillingAccount and copy/paste the name that is returned.
Please note that this requires the "Az.Billing" module.
Next we need a token to authenticate with management.azure.com. You can configure the whole authentication flow in Postman but you can also just configure it to use a Bearer token. You can request the token you need by running "(Get-AzAccessToken).Token". Copy and paste that token in Postman and you're good to go!
Now we can execute our query!
Executing this against the cost management query API will return a pretty detailed result.
We can see the columns returned and if we scroll down we will see the actual rows and data like so:
Yes.. Columns and rows. That means that we can translate our data to the following sample:
Of course you'd want to do this programmatically but that's for the next post.
This is just one specific example but you can write many, many different queries. The following sample will return the Month to Date costs for all Resource Groups named "UAT" or "PROD", that are in East US or West Europe and have a tag "Environment" with value "API". This is the actual example on the Microsoft Docs page but I found that didn't work and updated it accordingly.
{
"type": "ActualCost",
"timeframe": "MonthToDate",
"dataSet": {
"granularity": "Daily",
"filter": {
"And": [
{
"Dimensions": {
"Name": "ResourceGroupName",
"Operator": "In",
"Values": [
"UAT",
"PROD"
]
}
},
{
"Dimensions": {
"Name": "ResourceLocation",
"Operator": "In",
"Values": [
"East US",
"West Europe"
]
}
},
{
"Tags": {
"Name": "Environment",
"Operator": "In",
"Values": [
"API"
]
}
}
]
}
}
}
And there you have it. Your first contact with the Cost Management Query API. I will follow up with a post on how to do this programmatically and build some automation around it. As always, if you have any questions or feedback please let me know! Stay tuned for the next post where we will turn this into some automation.