Click here to Skip to main content
14,732,665 members
Articles » Cloud Computing » Azure » General
Tip/Trick
Posted 9 Oct 2020

Tagged as

Stats

1.6K views
1 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
9 Oct 2020CPOL
Azure CLI Dynamic Query Consumption Cost Data

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(@)"

image

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

image

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

image

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

image

Example 4

Filter the total cost of app service:

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

image

Example 5

Total cost before tax:

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

image

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

Image 7

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

  • 9th October, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ITWei
Taiwan Taiwan
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --