main.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# =============================================================================
# Created By  : Gabriel Geise
# Created Date: Tue Nov 12 12:06:00 EST 2021
# =============================================================================

'''
Cloud function triggered by HTTPS call with two parmeters in th body (enrollment #, and load_type). Load types accepted are daily, and monthly. 
Daily loads filter for data from yesterday, monthly collects from the previous billing cycle.
Data is extracted from the Azure Consumption API via a service principal. See https://docs.microsoft.com/en-us/azure/cost-management-billing/manage/assign-roles-azure-service-principals
for details on assigning a service principal to the enrollment.
The data is then inserted into a Bigquery table for later querying. 
'''
import json
import datetime
import os
from decimal import Decimal
from config import config
from google.cloud import bigquery
    
bq_client = bigquery.Client(project="up-eit-ce-production")
table_ref = bq_client.dataset(config['dataset']).table(config['table_name'])
token = ""

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
first = today.replace(day=1)
lastMonth = first - datetime.timedelta(days=1)
billing_start = lastMonth.replace(day=1)


## Helper function to encode datetime objects
def default(o):
    if isinstance(o, (datetime.date, datetime.datetime)):
        return o.isoformat()

########################################
# CloudFunction
# Triggered via Cloud Scheduler 
# Queries Azure Consumption API for detailed usage data
########################################
def azure_daily_billing_export (request):

    from azure.mgmt.consumption import ConsumptionManagementClient
    from azure.identity import ClientSecretCredential

    request_json = request.get_json()

    scope="/providers/Microsoft.Billing/billingAccounts/"
    
    usage_json = []
    print(request_json)
    enrollment = request_json.get('enrollment')
    load_type = request_json.get('load_type')
    path = scope + str(enrollment) + "/providers/Microsoft.Billing/billingPeriods/"

    if load_type == "daily" :
        path += first.strftime("%Y%m")
        usages = gather_usage_details(path)
        billing_period=first.strftime("%Y-%m-%d")
        usages = list(filter(lambda x: (x.date.date() == yesterday),usages))

    elif load_type == "monthly":
        path += billing_start.strftime("%Y%m")
        usages = gather_usage_details(path)
        billing_period=billing_start.strftime("%Y-%m-%d")

    elif load_type == "this_month":
        path += first.strftime("%Y%m")
        usages = gather_usage_details(path)
        billing_period=first.strftime("%Y-%m-%d")

    else:
        raise ValueError('Requested load type ('+load_type+') not valid')

    print(billing_period)
    usage_json.extend(map(lambda x: dict(
        id=x.id,
        name=x.name,
        type=str(x.type),
        kind=x.kind,
        billing_account_id=x.billing_account_id,
        billing_account_name=x.billing_account_name,
        billing_period=billing_period,
        billing_period_start_date=x.billing_period_start_date.isoformat(),
        billing_period_end_date=x.billing_period_end_date.isoformat(),
        billing_profile_id=x.billing_profile_id,
        billing_profile_name=x.billing_profile_name,
        account_owner_id=x.account_owner_id,
        account_name=x.account_name,
        subscription_id=x.subscription_id,
        subscription_name=x.subscription_name,
        date=x.date.isoformat(),
        product=x.product,
        part_number=x.part_number,
        meter_id=x.meter_id,
        meter_details=x.meter_details,
        quantity=x.quantity,
        effective_price=x.effective_price,
        cost=x.cost,
        unit_price=x.unit_price,
        billing_currency=x.billing_currency,
        resource_location=x.resource_location,
        consumed_service=x.consumed_service,
        resource_id=x.resource_id,
        resource_name=x.resource_name,
        service_info1=x.service_info1,
        service_info2=x.service_info2,
        additional_info=x.additional_info,
        invoice_section=x.invoice_section,
        cost_center=x.cost_center,
        resource_group=x.resource_group,
        reservation_id=x.reservation_id,
        reservation_name=x.reservation_name,
        product_order_id=x.product_order_id,
        offer_id=x.offer_id,
        is_azure_credit_eligible=x.is_azure_credit_eligible,
        term=int(x.term) if x.term else None,
        publisher_name=x.publisher_name,
        publisher_type=x.publisher_type,
        plan_name=x.plan_name,
        charge_type=x.charge_type,
        frequency=x.frequency
        ),usages))
    if load_type == "monthly" or load_type == "this_month":
        purge_daily(billing_period, enrollment)
    insert_to_bq(usage_json)


def gather_usage_details(path):
    from azure.mgmt.consumption import ConsumptionManagementClient
    from azure.identity import ClientSecretCredential
    cscredential = ClientSecretCredential(config['tenant'],config['client_id'], config['CLIENT_SECRET_KEY'])
    consumption_client = ConsumptionManagementClient(
        credential=cscredential,
        subscription_id=''
    )
    print(path)

    return consumption_client.usage_details.list(path)

def insert_to_bq(data):
    schema=[
        bigquery.SchemaField("charge_type", "STRING", "NULLABLE"),	
        bigquery.SchemaField("plan_name", "STRING", "NULLABLE"),
        bigquery.SchemaField("publisher_name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("resource_group", "STRING", "NULLABLE"),	
        bigquery.SchemaField("invoice_section", "STRING", "NULLABLE"),	
        bigquery.SchemaField("service_info2", "STRING", "NULLABLE"),	
        bigquery.SchemaField("frequency", "STRING", "NULLABLE"),	
        bigquery.SchemaField("service_info1", "STRING", "NULLABLE"),	
        bigquery.SchemaField("reservation_id", "STRING", "NULLABLE"),	
        bigquery.SchemaField("resource_id", "STRING", "NULLABLE"),	
        bigquery.SchemaField("consumed_service", "STRING", "NULLABLE"),	
        bigquery.SchemaField("reservation_name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("additional_info", "STRING", "NULLABLE"),	
        bigquery.SchemaField("resource_location", "STRING", "NULLABLE"),	
        bigquery.SchemaField("is_azure_credit_eligible", "BOOLEAN", "NULLABLE"),	
        bigquery.SchemaField("billing_currency", "STRING", "NULLABLE"),	
        bigquery.SchemaField("unit_price", "FLOAT", "NULLABLE"),	
        bigquery.SchemaField("cost", "FLOAT", "NULLABLE"),	
        bigquery.SchemaField("effective_price", "FLOAT", "NULLABLE"),	
        bigquery.SchemaField("quantity", "FLOAT", "NULLABLE"),	
        bigquery.SchemaField("meter_details", "STRING", "NULLABLE"),	
        bigquery.SchemaField("product_order_id", "STRING", "NULLABLE"),	
        bigquery.SchemaField("cost_center", "STRING", "NULLABLE"),	
        bigquery.SchemaField("kind", "STRING", "NULLABLE"),	
        bigquery.SchemaField("meter_id", "STRING", "NULLABLE"),	
        bigquery.SchemaField("date", "TIMESTAMP", "NULLABLE", "bq-datetime"),	
        bigquery.SchemaField("product", "STRING", "NULLABLE"),	
        bigquery.SchemaField("part_number", "STRING", "NULLABLE"),	
        bigquery.SchemaField("publisher_type", "STRING", "NULLABLE"),	
        bigquery.SchemaField("subscription_name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("account_name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("billing_period_end_date", "TIMESTAMP", "NULLABLE", "bq-datetime"),	
        bigquery.SchemaField("offer_id", "STRING", "NULLABLE"),	
        bigquery.SchemaField("billing_account_name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("account_owner_id", "STRING", "NULLABLE"),	
        bigquery.SchemaField("billing_profile_id", "INTEGER", "NULLABLE"),	
        bigquery.SchemaField("subscription_id", "STRING", "NULLABLE"),	
        bigquery.SchemaField("resource_name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("billing_profile_name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("name", "STRING", "NULLABLE"),	
        bigquery.SchemaField("billing_account_id", "INTEGER", "NULLABLE"),	
        bigquery.SchemaField("billing_period_start_date", "TIMESTAMP", "NULLABLE", "bq-datetime"),
        bigquery.SchemaField("billing_period", "DATE","NULLABLE"),
        bigquery.SchemaField("type", "STRING", "NULLABLE"),	
        bigquery.SchemaField("term", "INTEGER", "NULLABLE"),	
        bigquery.SchemaField("id", "STRING", "NULLABLE")
    ]

    job_config = bigquery.LoadJobConfig()
    job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
    job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
    job_config.schema = schema
    load_job = bq_client.load_table_from_json(
        data, table_ref, job_config=job_config
    )  # API request

    load_job.result()  # Waits for table load to complete.
    print("{} Usage Table finished.".format("Azure"))

def purge_daily(period, enrollment):
    sql = "Delete FROM {dataset}.{table} where billing_period='{period}' and billing_profile_id={enrollment}".format(dataset=config['dataset'], table=config['table_name'], period=period, enrollment=enrollment)
    query_job = bq_client.query(sql)  # Make an API request.
    output = list(query_job)  # Wait for the job to complete.

if __name__ == '__main__':
    from unittest.mock import Mock
    data = {"enrollment":64988483, "load_type":"monthly"}
    req = Mock(get_json=Mock(return_value=data), args=data)
    
    azure_daily_billing_export(req)