Blog Post

ITOps Talk Blog
4 MIN READ

Customize cost data visualizations with Azure Workbooks and Azure Cost Management

Billy York's avatar
Billy York
Iron Contributor
Jun 02, 2021

Azure Workbooks have become a standard data visualization tool inside Azure. After starting in Azure Monitor, they now are also in Azure Sentinel, and Azure Security Center. Workbooks are incredibly dynamic, allowing you to create parameters that can be used throughout the workbook to both control what is seen by a user as well as inside queries. There are many things you can query from logs and metrics to Azure Resource Graph to Azure Resource Manager(ARM). This post will show you how to query Cost Management by using the ARM data source. Then we will make our query to ARM dynamic, because that is what sets Workbooks apart from a regular old dashboard.

 

Why might you want to query Cost Management from Workbooks? Well first because we can do parameters we can set a top level Subscription parameter and quickly get costs for all subscriptions. But we can also use tags as I describe in the dynamic monitoring post linked to above, we can show costs for infrastructure based on tag, or resource type right along side with operational monitoring data.

 

Query Azure Cost Management

Like pretty much everything now, it all comes back to an API. Naturally, Cost Management has an API that we can query. To query Cost Management in Workbooks we'll need the URL example from the docs, and a subscription id.

 

In this screen grab I've already setup a Subscription parameter, we'll want to do a new query, then select Azure Resource Manager as our data source. The HTTP Method is POST, and finally the path is /subscriptions/{Subscription:id}/providers/Microsoft.CostManagement/query?

 

Where {Subscription:id} is from our subscription parameter.

 

 You'll also need to declare the api version you are using, in this case 2019-11-01 is what we're using.

Now that we have this setup, we need a JSON body to POST to the API to tell Cost Management what information we want back. There are a number of examples on the API docs linked to above, I also recently wrote about JSON body examples here, with some more straight forward examples than what is given on the docs.

 

I'll be using a simple body that brings me back my month to date cost, by subscription.

 

JSON Path

The result you'll get back is JSON, so if we want this in a more readable format we'll need to use JSON Path. Under Results Settings, change Result Format to JSON Path. Then put $.properties in JSON Path Table.

  Now this will bring back our data in a slightly prettier format than JSON.

 

Make it Dynamic!

Now that we've successfully queried Cost Management and formatted the results. Its time to put the power of Workbooks to use. We'll add two parameters, one for our Environment tag, IE Prod, Non Prod, QA etc. And one for Aggregation type, where we specify how we want the cost to be aggregated. 

 

This Environment parameter uses Azure Resource Graph to query all resources that have been tagged with Demo_Environment, where we have Dev, QA and Prod as options.

 

 

 

Aggregation is a simple JSON drop down where we manually specify the options we want to be available in the drop down.

  

Once we have our parameters setup, we can then add them into the JSON body. For Demo Environment we have added a Filter for Tags. We have placed Aggregation at the bottom under Grouping.

 {
   "type": "Usage",
   "timeframe": "MonthToDate",
   "dataset": {
     "granularity": "None",
     "filter": {
                 "tags" : {
                     "name" : "Demo_Environment",
                     "operator" : "In",
                     "values" : [
                        {Environment}
                     ]
                 }
     },
     "aggregation": {
       "totalCost": {
         "name": "PreTaxCost",
         "function": "Sum"
       }
     },
     "grouping": [
       {
         "type": "Dimension",
         "name": "{Aggregation}"
       }
     ]
   }
 }
This allows us to aggregate based on Resource Group, Subscription or even Resource Type, while selecting any individual tag or all 3 tags at the same time. This will then be translated by the workbook and sent to the Cost Management API.
 
Cost by subscription for Prod, QA and Dev
 
 Cost by resource type for Prod, QA and Dev
 
Cost by Resource Group for Prod
 
You can add more filters for other tags or even resource types in the JSON Body. Almost everything in the body can be parameterized. For instance, you can make a TimeFrame parameter to bring back cost for a specific cycle, like Week to date, the Last Billing cycle etc.
 
 
The only thing we can't make truly dynamic is querying subscription, because were sending a request to the API it can only be for one subscription at a time.
 
This workbook is on my github, so you don't have to recreate the wheel.
 
 

Summary

With the ability to query a number of different sources, Workbooks allow you to truly customize your data visualization across your Azure environment.  The ability to show Cost Management along side your resources and slice and dice it anyway you want or need is what Azure Workbooks are all about!

Updated May 31, 2021
Version 1.0
  • Hi Bill,

    Unfortunately something doesn't work with the Tags. I've added "Demo_Environment" Tag with PROD and DEV values but still no effect.

    Could you check this JSON code above? JSON Editor throws me an error with this Environment value there.

  • MSch85's avatar
    MSch85
    Copper Contributor

    Omar09  did you create a Tag for the Resources?  

     

    This Environment parameter uses Azure Resource Graph to query all resources that have been tagged with Demo_Environment, where we have Dev, QA and Prod as option

    --> you must set a Tag "Demo_Environment" with the value Dev, QA or Prod

  • Omar09's avatar
    Omar09
    Copper Contributor

    Thanks for the above! that looks great!  I can't seem to get the Environment field to work for me. Any ideas why? It either asks to select "Any One" or "All".

     

    Thanks

  • Logan-IN's avatar
    Logan-IN
    Copper Contributor

    Hi Billy York 

     

    This is really cool and it's absolutely what i wanted. Can we summarize the pretaxcost and display it as Total Cost for the Complete resource group in a subscription? I tried but not able to do that.