受阿Q(神Q超人)影響 :
跨出國界-寫出照樣照句的英文文章-實用度極高的 7 個句型分享
想嘗試走出中文舒適圈，嘗試在 Dev 平台寫英文文章 :
Azure CLI Dynaic Query Consumption Cost Data (az consumption usage list) - DEV Community

Youtube Video :

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

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