概要

Azureの請求アカウントIDから請求データを取得するPythonプログラムです。このプログラムの応用編です。

請求月指定でデータを取得します
1000件以上のデータ取得に対応しました
取得データはCSVでローカルに保存します

実行環境

macOS Ventura 13.0
python 3.8.12

事前準備

この記事の「事前準備」を完了していること

実行プログラム

REST_SnowflakeByBillingAccountId.py

import json
import os
import sys
import requests
import argparse
from datetime import *
from dateutil.relativedelta import relativedelta
import time
import logging
import pandas as pd
import numpy as np


# 請求管理者権限を付与されたサービスプリンシパル情報
TENANT_ID = os.environ['APC_TENANT_ID']
CLIENT_ID = os.environ['APC_CLIENT_ID']
CLIENT_KEY = os.environ['APC_CLIENT_KEY']

# 請求アカウントID
BILLING_ACCOUNT_ID = os.environ['BILLING_ACCOUNT_ID']

# REST API 情報
API_URL = 'https://management.azure.com'
API_VERSION = '/providers/Microsoft.CostManagement/query?api-version=2022-10-01'


# Azureアクセスのためのアクセストークンの取得
def get_azure_access_token() :

    # access_token を取得するためのヘッダ情報
    headers = {
        'Accept': 'application/json',
        'Content-Type': 'application/x-www-form-urlencoded'
    }

    payload = {
        'client_id': CLIENT_ID,
        'scope': API_URL + '/.default',
        'grant_type': 'client_credentials',
        'client_secret': CLIENT_KEY
    }

    # access_token を取得するためのURLを生成
    TokenGet_URL = "https://login.microsoftonline.com/" + \
        TENANT_ID + "/oauth2/v2.0/token"
    # print(TokenGet_URL, "\n")

    # 実行
    response = requests.get(
        TokenGet_URL,
        headers=headers,
        data=payload
    )
    # requrest処理のクローズ
    response.close

    # その結果を取得
    jsonObj = json.loads(response.text)
    try :
        return jsonObj["access_token"]
    except KeyError as err :
        print(err, "Not get\n", jsonObj)
        return ''


# クエリの定義
def define_query_usage_payload(bm_fm, bm_to) :

    # the maximum allowed number of items is 15
    payload = {
        "type": "ActualCost",
        "timeframe": "Custom",
        "timePeriod": {"from": bm_fm, "to": bm_to},
        "dataset": {
            "granularity": "None",
            "aggregation": {
                "totalCost": {"name": "PreTaxCost", "function": "Sum"},
                "totalQuantity": {"name": "UsageQuantity", "function": "Sum"}
            },
            "grouping": [
                {"type": "Dimension", "name": "ResellerMPNId"},
                {"type": "Dimension", "name": "CustomerName"},
                {"type": "Dimension", "name": "CustomerTenantDomainName"},
                {"type": "Dimension", "name": "SubscriptionName"},
                {"type": "Dimension", "name": "ResourceGroup"},
                {"type": "Dimension", "name": "ResourceType"},
                {"type": "Dimension", "name": "ServiceFamily"},
                {"type": "Dimension", "name": "ServiceName"},
                {"type": "Dimension", "name": "Product"},
                {"type": "Dimension", "name": "UnitOfMeasure"},
                {"type": "Dimension", "name": "PricingModel"},
                {"type": "Dimension", "name": "Provider"},
                {"type": "Dimension", "name": "ProductOrderName"},
             ]
        }
    }
    return payload


# クエリの定義
def define_scope_url() :

    # SCOPEの定義
    SCOPE = f'/providers/Microsoft.Billing/billingAccounts/{BILLING_ACCOUNT_ID}'
    # URLを生成
    Post_URL = f'{API_URL}{SCOPE}{API_VERSION}'
    return Post_URL


# 請求情報の取得
def get_cost_by_billingid(access_token, bm_fm, bm_to, bm) :

    # 取得するためのヘッダ情報
    headers = {
        'Accept': 'application/json',
        'Content-Type': 'application/json; charset=utf-8',
        'Authorization': 'Bearer %s' % access_token
    }

    # 取得するためのURLを生成
    Post_URL = define_scope_url()

    # クエリの定義
    payload = define_query_usage_payload(bm_fm, bm_to)

    # 1000件毎（仕様）のデータ取得    
    df_cost = pd.DataFrame()
    while True :
        # 実行
        print("\n" ,Post_URL)
        response = requests.post(
            Post_URL,
            headers=headers,
            data=json.dumps(payload)
        )
        # requrest処理のクローズ
        response.close

        try :
            print(response)
            response.raise_for_status()
            next_link, df_rows = df_customer_cost(response.json())

            df_cost = pd.concat([df_cost, df_rows])

            if next_link is None :
                break
            elif len(next_link) > 0 :
                Post_URL = next_link
            else :
                break
        except requests.exceptions.RequestException as e :
            logging.exception("get_cost_by_billingid request failed. message=(%s)\n", e.response.text)
            sys.exit()
    
    # データの再インデックス化
    df_cost = df_cost.reset_index(drop=True)
    df_cost = df_cost.drop(columns=['Currency'])
    df_cost = df_cost.assign(BillingMonth=bm)
    return df_cost


def df_customer_cost(res_json):

    # カラム名を取得してから、値を取得
    df_colums = pd.DataFrame(res_json['properties']['columns'])
    df_rows = pd.DataFrame(res_json['properties']['rows'], columns=df_colums['name'])

    # 1000件を超える場合のURL(next_link)の取得
    next_link = res_json['properties']['nextLink']
    return next_link, df_rows


# 取得した全データの画面出力
def data_to_display(df_cost):

    # 取得した全データの表示
    print(df_cost.to_markdown(floatfmt=",.0f"))
    # print(df_cost.to_markdown())
    print(df_cost.dtypes)

    # 合計請求金額の表示
    total = df_cost['PreTaxCost'].sum()
    count = len(df_cost)
    print("\n", "合計請求金額 : {:,.0f}".format(total), "\tレコード件数 : {:,.0f}".format(count))


# 取得した全データの保存
def data_to_csv(df_cost, bm):

    # 取得した全データのcsvファイルへ保存
    filename = './data_customers_cost_by_billingid/cm2sf_' + bm + '.csv'
    print(filename)
    df_cost.to_csv(filename, index=False, encoding="utf-8")

    # 合計請求金額と件数の表示
    total = df_cost['PreTaxCost'].sum()
    count = len(df_cost)
    print("\n", "合計請求金額 : {:,.0f}".format(total), "\tレコード件数 : {:,.0f}".format(count))


# パラメータ年月から月初-月末の取得
def month_period(bm):
        
    try :
        # 月初
        bm = bm + '-01'
        fm_datetime = datetime.strptime(bm, '%Y-%m-%d')
        bm_fm = fm_datetime.strftime('%Y-%m-%d')
        
        # 月末 (来月の1日に合わせて1日分戻る)
        to_datetime = fm_datetime + relativedelta(months=+1,day=1,days=-1)
        bm_to = to_datetime.strftime('%Y-%m-%d')
        return bm_fm, bm_to

    except ValueError as e :
        print("ValueError = ", e)
        sys.exit()


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='パラメータに年月を渡し、月初から月末までの請求情報の取得')
    parser.add_argument('-b', '--bm', type=str, required=True, help='取得する年月(yyyy-mm)')
    parser.add_argument('--mode', type=str, default='lo', help='lo（取得データを画面出力） / csv（取得データをcsv保存）')
    args = parser.parse_args()

    # 年月から月初-月末の取得
    bm_fm, bm_to = month_period(args.bm)

    start = time.time()
    access_token = get_azure_access_token()

    if len(access_token) > 0 :
        print("\n取得アクセストークン : ")
        print(access_token, "\n")
        df_cost = get_cost_by_billingid(access_token, bm_fm, bm_to, args.bm)

        if args.mode == 'csv':
            # 取得した全データを保存
            data_to_csv(df_cost, args.bm)
        else :
            # 取得した全データを出力
            data_to_display(df_cost)

    generate_time = time.time() - start

    print("\n取得時間:{0}".format(generate_time) + " [sec]\n")

プログラムのHELP表示

$ python REST_SnowflakeByBillingAccountId.py -h                   
usage: REST_SnowflakeByBillingAccountId.py [-h] -b BM [--mode MODE]

パラメータに年月を渡し、月初から月末までの請求情報の取得

optional arguments:
  -h, --help      show this help message and exit
  -b BM, --bm BM  取得する年月(yyyy-mm)
  --mode MODE     lo（取得データを画面出力） / csv（取得データをcsv保存）

プログラムの実行

1回のリクエストで取得できるレコード数は1000件となります。
結果的に9000件以上のデータを取得しているので、リクエストは10回行われました。

## 2022年11月分の請求データを取得
$ python REST_SnowflakeByBillingAccountId.py -b 2022-12 --mode csv

取得アクセストークン : 
eyJ0eo9i ・・・省略・・・ ROGrZEsLg 


 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=AQAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=AgAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=AwAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=BAAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=BQAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=BgAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=BwAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=CAAAAA%3D%3D
<Response [200]>

 https://management.azure.com/providers/Microsoft.Billing/billingAccounts/<BILLING_ACCOUNT_ID>/providers/Microsoft.CostManagement/query?api-version=2022-10-01&$skiptoken=CQAAAA%3D%3D
<Response [200]>
./data_customers_cost_by_billingid/cm2sf_2022-11.csv

 合計請求金額 : 92,929,292 	レコード件数 : 9,292

取得時間:236.55168390274048 [sec]

付録

クエリ定義の grouping で指定できる項目一覧

    {"type": "Dimension", "name": "ResellerMPNId"},
    {"type": "Dimension", "name": "CustomerTenantId"},
    {"type": "Dimension", "name": "CustomerName"},
    {"type": "Dimension", "name": "CustomerTenantDomainName"},

    {"type": "Dimension", "name": "ServiceFamily"},
    {"type": "Dimension", "name": "ServiceName"},               # MeterCategory = ServiceName

    {"type": "Dimension", "name": "MeterCategory"},             # MeterCategory = ServiceName
    {"type": "Dimension", "name": "MeterSubcategory"},          # Product = MeterSubcategory + Meter
    {"type": "Dimension", "name": "Meter"},                     # 測定対象部

    {"type": "Dimension", "name": "Product"},                   # Product = MeterSubcategory + Meter
    {"type": "Dimension", "name": "UnitOfMeasure"},             # 測定対象単位
    {"type": "Dimension", "name": "PricingModel"},              # OnDemand / Reservation
    {"type": "Dimension", "name": "Provider"},                  # Azure / Microsoft 365 / Power Platform
    {"type": "Dimension", "name": "ProductOrderName"},
    {"type": "Dimension", "name": "ProductOrderId"},
    {"type": "Dimension", "name": "PublisherType"},             # Microsoft / Marketplace

    {"type": "Dimension", "name": "SubscriptionId"},
    {"type": "Dimension", "name": "SubscriptionName"},

    {"type": "Dimension", "name": "ResourceGroup"},             # ResourceGroup = ResourceGroupName
    {"type": "Dimension", "name": "ResourceGroupName"},         # ResourceGroup = ResourceGroupName
    {"type": "Dimension", "name": "ResourceType"},              # Resource Providor名
    {"type": "Dimension", "name": "ResourceLocation"},          # ResourceのLocation
    {"type": "Dimension", "name": "ResourceGuid"},              # ResourceのID
    {"type": "Dimension", "name": "ResourceId"},                # Resource URL

    # --------------------------------------------------------

    {"type": "Dimension", "name": "InvoiceId"},                 # MS社から当社のへの請求書ID、以下３項目も必要なし
    {"type": "Dimension", "name": "InvoiceSection"},            # InvoiceSection = InvoiceSectionId
    {"type": "Dimension", "name": "InvoiceSectionId"},          # InvoiceSection = InvoiceSectionId
    {"type": "Dimension", "name": "InvoiceSectionName"},        # InvoiceSectionName = CustomerName

    {"type": "Dimension", "name": "BillingAccountName"},        # 当社なので必要なし
    {"type": "Dimension", "name": "BillingProfileId"},          # 利用していないので必要なし
    {"type": "Dimension", "name": "BillingProfileName"},        # 利用していないので必要なし
    {"type": "Dimension", "name": "BillingMonth"},              # 2022-09-01T00:00:00 となるので必要なし

    {"type": "Dimension", "name": "Frequency"},                 # UsageBased 従量課金かどうか、必要なし
    {"type": "Dimension", "name": "PartNumber"},                # Marketplaceリソースのものだけ表示、必要なし

    {"type": "Dimension", "name": "BenefitId"},                 # 予約リソースのものだけ表示、PricingModelで対応可
    {"type": "Dimension", "name": "BenefitName"},               # 予約リソースのものだけ表示、PricingModelで対応可

    {"type": "Dimension", "name": "ReservationId"},             # 今回のDWH分析では必要なし
    {"type": "Dimension", "name": "ReservationName"},           # 今回のDWH分析では必要なし

    {"type": "Dimension", "name": "CostAllocationRuleName"},    # 利用していないので必要なし
    {"type": "Dimension", "name": "MarkupRuleName"},            # 利用していないので必要なし

    {"type": "Dimension", "name": "PartnerEarnedCreditApplied"},    # 今回のDWH分析では必要なし
    {"type": "Dimension", "name": "PartnerName"},                   # 当社なので必要なし

クエリ定義の aggregation で指定できる項目一覧

'UsageQuantity','PreTaxCost','Cost','CostUSD','PreTaxCostUSD'

まとめ

これで CostManagement REST API を利用して Azureの請求データを月指定で取得することができました。CSVで取得した請求データはDWH等で後ほど分析できればと、、、、、