Azure CLI Dynaic Query Consumption Cost Data (az consumption usage list)
Tags: azure
Published: 2020-10-08 02:14:54

<!--Azure CLI Dynaic Query Consumption Cost Data (az consumption usage list)-->

Youtube Video :  
[![Yes](https://img.youtube.com/vi/teEVMPcux78/0.jpg)](https://www.youtube.com/watch?v=teEVMPcux78)

---

It looks like that no one wrote detail about Azure CLI consumption usage query, so I provides personal ways for readers.

Regarding the cost, it can be said that the boss `cares most`,
the cost analysis function of Azure Portal has actually been done very well, which can `solve most of the needs`,but sometimes it's necessary to `customize` or `search unexpected expense details`, then you can use Azure CLI [az consumption usage](https://docs.microsoft.com/en-us/cli/azure/consumption/usage?view=azure-cli-latest#az_consumption_usage_list) at this time.

the advantages are :  
1. powerful dynamic query
2. get detailed information directly about wasteful services
3. it can get ID value and join other service data

First use [ az consumption usage -h ](https://gist.github.com/shps951023/1bf591ce2f810c966227ef1681d649a4) to check the supported functions, because it's the `preview version`, only `list` query function is currently supported.

```bash
sa@DESKTOP-WEI:~$ az consumption usage list - [tab][tab]
--billing-period-name            --query                          -h
--debug                          --query-examples                 -m
--end-date                       --start-date                     -o
--help                           --subscription                   -p
--include-additional-properties  --top                            -s
--include-meter-details          --verbose                        -t
--only-show-errors               -a
--output                         -e
```

The options I will use :  
1. `--query` support JMESPath JSON query language
2. `--includ-meter-details -m` show meter data
3. `--start-date -s` , `--end-date -e` filter date


For example :  
```bash
$ az consumption usage list
```
Azure will return `JSON array` data [ UsageDetail ](https://docs.microsoft.com/en-us/dotnet/api/microsoft.azure.management.consumption.models.usagedetail?view=azure-dotnet)
```json
[
  {
    "accountName": null,
    "billableQuantity": "None",
    "billingPeriodId": "/subscriptions/c905f1a3-b790-47cb-98f5-eb7b89d7d768/providers/Microsoft.Billing/billingPeriods/202010",
    "consumedService": "Microsoft.Network",
    "costCenter": null,
    "currency": "USD",
    "departmentName": null,
    "id": "/subscriptions/c905f1a3-b790-47cb-98f5-eb7b89d7d768/providers/Microsoft.Billing/billingPeriods/202010/providers/Microsoft.Consumption/usageDetails/85b8a5ed-ccf7-1870-4ad2-dadd26be9f30",
    "instanceId": "/subscriptions/c905f1a3-b790-47cb-98f5-eb7b89d7d768/resourceGroups/demo/providers/Microsoft.Network/publicIPAddresses/demoVMPublicIP",
    "instanceLocation": "AP Southeast",
    "instanceName": "demoVMPublicIP",
    "invoiceId": null,
    "isEstimated": true,
    "meterId": "ae56b367-2708-4454-a3d9-2be7b2364ea1",
    "name": "85b8a5ed-ccf7-1870-4ad2-dadd26be9f30",
    "pretaxCost": "0",
    "product": "",
    "subscriptionGuid": "C905F1A3-B790-47CB-98F5-EB7B89D7D768",
    "subscriptionName": "Free Trial",
    "tags": null,
    "type": "Microsoft.Consumption/usageDetails",
    "usageEnd": "2020-09-30T23:59:59Z",
    "usageQuantity": "8.6966666666000005392334060161374509334564208984375",
    "usageStart": "2020-09-30T00:00:00Z"
  }
]
```

Add `-m`, there will be more `meterDetails attributes`, you can know which `service type` belongs to by meterCategory
  
```json
  "meterDetails": {
    "meterCategory": "Virtual Network",
    "meterLocation": "AP Southeast",
    "meterName": "Dynamic Public IP - Free",
    "meterSubCategory": "IP Addresses",
    "pretaxStandardRate": "None",
    "serviceName": "Virtual Network",
    "serviceTier": "IP Addresses",
    "totalIncludedQuantity": "None",
    "unit": ""
  }
```

Then we have to deal with the problem of `too much data and attributes`.e.g, only few days since I tested, and there're already 204 items. 

So we need to use `--query` to select the data we want.

```bash
az consumption usage list --query "[] | length(@)"
```
![image](https://user-images.githubusercontent.com/12729184/95090981-7700f500-0758-11eb-9191-76077774444e.png)



Example 1 : List the details and required fields: name, service type, pre-tax fee, date
```bash
az consumption usage list -m --query "[0].{name:instanceName,ser:consumedService,cat:meterDetails.meterCategory,pretaxCost:pretaxCost.to_number(@),date:usageStart}" 
```
![image](https://user-images.githubusercontent.com/12729184/95181864-669f5780-07f6-11eb-8c5b-3d8d240172cc.png)



Example 2: Query the top 5 resources that spend the most on 10/2
logic : 
- Because `JMESPath only has sort_by positive order` and not reverse order, so use sort_by `[-5:]` to get last 5 data of positive order to simulate reverse order function.
- pretaxCost is `string type` by default, so it needs to be converted to number using `to_number(@)` function.


```bash
# Recommend to use -s -e to filter dates
az consumption usage list -m -s "2020-10-02" -e "2020-10-02" --query "sort_by([],&pretaxCost.to_number(@))[-5:].{name:instanceName,ser:consumedService,cat:meterDetails.meterCategory,pretaxCost:pretaxCost.to_number(@),sDate:usageStart}" -o table

# The way of JMESPath to filter dates
az consumption usage list -m --query "[?usageStart=='2020-10-02T00:00:00Z'] | sort_by([],&pretaxCost.to_number(@))[-5:].{name:instanceName,ser:consumedService,cat:meterDetails.meterCategory,pretaxCost:pretaxCost.to_number(@),sDate:usageStart}" -o table
```
![image](https://user-images.githubusercontent.com/12729184/95181935-833b8f80-07f6-11eb-8240-c48a86954cc7.png)

Example 3: Filter app service spending details and sort by date consumption amount
```bash
az consumption usage list -m --query "[?meterDetails.meterCategory == 'Azure App Service'].{name:instanceName,ser:consumedService,cat:meterDetails.meterCategory,pretaxCost:pretaxCost.to_number(@),sDate:usageStart} | sort_by([],&pretaxCost.to_number(@))" -o table
```
![image](https://user-images.githubusercontent.com/12729184/95182207-e2010900-07f6-11eb-84de-1fb681261ba8.png)


Example 4: Filter the total cost of app service
```bash
az consumption usage list -m --query "[?meterDetails.meterCategory == 'Azure App Service'].pretaxCost.to_number(@) | sum([]) "
```
![image](https://user-images.githubusercontent.com/12729184/95183033-fbef1b80-07f7-11eb-9299-47afe6514f18.png)


Example 5: Total cost before tax
```bash
az consumption usage list -m --query "[].pretaxCost.to_number(@) | sum([])"
```
![image](https://user-images.githubusercontent.com/12729184/95182364-15439800-07f7-11eb-8da3-2451b03a3b83.png)



Example 6: Filter out the cost data of 0 $
Note: Remember to use "\'", otherwise the system will not recognize it, and return the error message:
```bash
ArgumentParseError: argument --query: invalid jmespath_type value: '[?pretaxCost.to_number(@) > ].{name:instanceName,ser:consumedService,cat:meterDetails.meterCategory,pretaxCost:pretaxCost.to_number(@),sDate:usageStart} | sort_by([],&pretaxCost)'
```
```bash
az consumption usage list -m --query "[?pretaxCost.to_number(@) > \`0\`].{name:instanceName,ser:consumedService,cat:meterDetails.meterCategory,pretaxCost:pretaxCost.to_number(@),sDate:usageStart} | sort_by([],&pretaxCost)" -o table
```
![image](https://user-images.githubusercontent.com/12729184/95588206-b2abff80-0a75-11eb-9f77-8f8ef1098edc.png)


Note: If `do not specify -s and -e date`, default to filter data within `15 days`  
For example: 2020-10-05 query will only bring out data from 2020-09-20 ~ 2020-10-05