DEV Community

Cover image for Azure CLI Dynaic Query Consumption Cost Data (az consumption usage list)
Wei
Wei

Posted on • Updated on

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

Youtube Video :

Yes


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 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
  }
]
Enter fullscreen mode Exit fullscreen mode

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": ""
  }
Enter fullscreen mode Exit fullscreen mode

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(@)"
Enter fullscreen mode Exit fullscreen mode

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}" 
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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([]) "
Enter fullscreen mode Exit fullscreen mode

image

Example 5: Total cost before tax

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

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)'
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

image

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

Top comments (0)