Azure CLI Dynamic Query Consumption Cost Data (az consumption usage list)

Introduction

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 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 to check the supported functions, because it's the preview version, only list query function is currently supported.

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:

$ az consumption usage list

Azure will return JSON array data UsageDetail:

[
  {
    "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.

"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.

az consumption usage list --query "[] | length(@)"

Example 1

List the details and required fields: name, service type, pre-tax fee, date.

az consumption usage list -m --query "[0].{name:instanceName,ser:consumedService,
cat:meterDetails.meterCategory,pretaxCost:pretaxCost.to_number(@),date:usageStart}" 

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.

# 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

Example 3

Filter app service spending details and sort by date consumption amount:

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

Example 4

Filter the total cost of app service:

az consumption usage list -m --query "[?meterDetails.meterCategory ==
'Azure App Service'].pretaxCost.to_number(@) | sum([]) "

Example 5

Total cost before tax:

az consumption usage list -m --query "[].pretaxCost.to_number(@) | sum([])"

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:

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)'

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

Note: If you 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.

Youtube Video

History

• 9^th October, 2020: Initial version