LoginSignup
2
2

More than 1 year has passed since last update.

@turupon(Issei Tsuruzono)in株式会社ネットワールド

Azureの請求情報分析のためのデータを Python で取得してみました

Posted at

概要

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等で後ほど分析できればと、、、、、

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2