OCI Cost Report Automation with Python

Managing Oracle Cloud Infrastructure Costs with Automated Reports

Managing cloud infrastructure costs is an essential part of maintaining healthy cloud operations. This article outlines the steps and provides the necessary Python scripts for automating the generation of an Oracle Cloud Infrastructure (OCI) cost report and sending it via email using the Microsoft Graph API or SMTP.

To ensure optimal security, the automation leverages OCI’s Instance Principal authentication for secure, keyless access to the Usage API, removing the need to store sensitive IAM credentials locally.

1. Prerequisites and Setup

Python Environment Setup

The script requires Python 3 and several external libraries. It is best practice to set up a virtual environment to isolate dependencies:

Create Virtual Environment: python3 -m venv venv (Creates a virtual environment named venv)
Activate Virtual Environment: source venv/bin/activate (Activates the virtual environment)
Install Dependencies: pip install requests pandas openpyxl oci-cli (Installs packages for Graph API, Excel processing, and OCI interaction)

OCI Instance Principal Setup

To allow the OCI instance where the script runs to fetch usage data without storing user credentials, set up an Instance Principal:

Dynamic Group: Any {instance.id = 'ocid1.instance.oc1.me-dubai-1.anshqljrxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'}
Policy: Allow dynamic-group 'Default'/'cost-dg' to manage usage-report in tenancy

Microsoft 365 (O365) Authentication (If Required)

The script uses OAuth 2.0 with Client Credentials Flow to send emails securely via the Microsoft Graph API:

Step 1: Create Entra App — Create a new Application Registration in Microsoft Entra ID (formerly Azure AD). Note the Application (client) ID (OAUTH_CLIENT_ID) and Directory (tenant) ID (OAUTH_TENANT_ID). Create a Client Secret and note its value (OAUTH_CLIENT_SECRET).
Step 2: Assign Permissions — Go to API Permissions for the app, assign the Mail.Send application permission under Microsoft Graph, and grant admin consent.
Step 3: Configure Script — Update OAUTH_TENANT_ID, OAUTH_CLIENT_ID, OAUTH_CLIENT_SECRET, FROM_EMAIL, TO_EMAILS, and TENANT_OCID variables in the script.

2. Python Automation Script

This script fetches OCI usage data, converts it into a structured Excel report (grouped by Compartment and Service), and emails the report using the Microsoft Graph API.

Report Automation Script (O365 – Graph API)

Save the following code as oci_cost_report.py:

#!/usr/bin/env python3
import subprocess
import json
import csv
import pandas as pd
import requests
import os
import base64
from datetime import datetime, date, timedelta
from email.mime.base import MIMEBase
from email import encoders

# ---------------- CONFIG ----------------
TENANT_OCID = "ocid1.tenancy.oc1..aaaaaaaaocxvqaamvzj4uxsmy6oofphb2ryf7iwmetnwiazzgsw2h4roycda"

JSON_COMPARTMENT = "compartment_usage.json"
CSV_COMPARTMENT = "compartment_usage.csv"

JSON_SERVICE = "service_usage.json"
CSV_SERVICE = "service_usage.csv"

EXCEL_FILE = "oci_cost_report.xlsx"

# Email (Microsoft 365 OAuth2) settings
OAUTH_TENANT_ID = "your-id"
OAUTH_CLIENT_ID = "your-id"
OAUTH_CLIENT_SECRET = "your-secret"

FROM_EMAIL = "your-from-email"
TO_EMAILS = ["your-to-email"]
SUBJECT = "Oracle Cloud Monthly Cost Report"
# -----------------------------------------

def fetch_oci_usage(mode, start_date, end_date):
    group_by = '["compartmentName"]' if mode == "compartment" else '["service"]'
    output_file = JSON_COMPARTMENT if mode == "compartment" else JSON_SERVICE
    print(f"Fetching usage data grouped by {group_by} from {start_date} to {end_date} ...")

    try:
        subprocess.run([
            "oci", "--auth", "instance_principal",
            "usage-api", "usage-summary", "request-summarized-usages",
            "--tenant-id", TENANT_OCID,
            "--granularity", "MONTHLY",
            "--time-usage-started", start_date,
            "--time-usage-ended", end_date,
            "--query-type", "COST",
            "--group-by", group_by,
            "--compartment-depth", "1",
            "--output", "json"
        ], check=True, stdout=open(output_file, "w"))
        print(f"JSON data saved to {output_file}")
    except subprocess.CalledProcessError:
        print(f"OCI Usage API call failed for {mode}.")
        exit(1)

def convert_json_to_csv(mode):
    json_file = JSON_COMPARTMENT if mode == "compartment" else JSON_SERVICE
    csv_file = CSV_COMPARTMENT if mode == "compartment" else CSV_SERVICE
    total_amount = 0.0
    print(f"Converting {json_file} -> {csv_file} ...")
    try:
        with open(json_file, "r") as f:
            data = json.load(f)

        with open(csv_file, "w", newline="") as csvfile:
            writer = csv.writer(csvfile)
            if mode == "compartment":
                writer.writerow(["Usage Started", "Usage Ended", "Compartment Name", "Amount", "Currency"])
            else:
                writer.writerow(["Usage Started", "Usage Ended", "Service Name", "Amount", "Currency"])

            currency_for_total = "USD"

            for item in data.get("data", {}).get("items", []):
                usage_started = item.get("time-usage-started", "").split("T")[0] if item.get("time-usage-started") else ""
                usage_ended = item.get("time-usage-ended", "").split("T")[0] if item.get("time-usage-ended") else ""
                if mode == "compartment":
                    name_field = item.get("compartment-name") or ""
                else:
                    name_field = item.get("service") or ""

                currency = item.get("currency")
                if not currency or currency.strip() == "":
                    currency = "USD"
                currency_for_total = currency

                computed_amount_raw = item.get("computed-amount")
                computed_amount = round(float(computed_amount_raw), 2) if computed_amount_raw not in (None, "") else 0.00
                total_amount += computed_amount

                writer.writerow([usage_started, usage_ended, name_field, computed_amount, currency])

            writer.writerow(["", "", "Total Cost", round(total_amount, 2), currency_for_total or "USD"])
        print(f"CSV created: {csv_file}")
    except Exception as e:
        print(f"Failed to create CSV for {mode}: {e}")
        exit(1)

def combine_csv_to_excel():
    print(f"Combining CSVs into {EXCEL_FILE} ...")
    try:
        with pd.ExcelWriter(EXCEL_FILE) as writer:
            pd.read_csv(CSV_COMPARTMENT).to_excel(writer, sheet_name="Compartment Usage", index=False)
            pd.read_csv(CSV_SERVICE).to_excel(writer, sheet_name="Service Usage", index=False)
        print(f"Excel file created: {EXCEL_FILE}")
    except Exception as e:
        print(f"Failed to create Excel file: {e}")
        exit(1)

def send_email():
    print("Sending email using Microsoft Graph OAuth...")
    today = datetime.today().strftime("%Y-%m-%d")

    body_text = f"""
Hello,

Please find attached the OCI cost report for the current month as of {today}.

Regards,
OCI Cost Automation Script
"""

    token_url = f"https://login.microsoftonline.com/{OAUTH_TENANT_ID}/oauth2/v2.0/token"
    payload = {
        "client_id": OAUTH_CLIENT_ID,
        "scope": "https://graph.microsoft.com/.default",
        "client_secret": OAUTH_CLIENT_SECRET,
        "grant_type": "client_credentials"
    }
    r = requests.post(token_url, data=payload)
    token_data = r.json()
    access_token = token_data.get("access_token")

    if not access_token:
        print("Failed to get access token")
        print(token_data)
        cleanup_files()
        return

    email_msg = {
        "message": {
            "subject": f"{SUBJECT} - {today}",
            "body": {"contentType": "Text", "content": body_text},
            "toRecipients": [{"emailAddress": {"address": TO_EMAILS[0]}}],
            "from": {"emailAddress": {"address": FROM_EMAIL}}
        },
        "saveToSentItems": "false"
    }

    if os.path.exists(EXCEL_FILE):
        with open(EXCEL_FILE, "rb") as f:
            attachment_content = f.read()
        attachment = {
            "@odata.type": "#microsoft.graph.fileAttachment",
            "name": os.path.basename(EXCEL_FILE),
            "contentBytes": base64.b64encode(attachment_content).decode("utf-8")
        }
        email_msg["message"]["attachments"] = [attachment]
    else:
        print(f"File not found: {EXCEL_FILE}")
        cleanup_files()
        return

    headers = {
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json"
    }
    send_url = f"https://graph.microsoft.com/v1.0/users/{FROM_EMAIL}/sendMail"

    response = requests.post(send_url, headers=headers, json=email_msg)

    if response.status_code == 202:
        print("Email sent successfully via Microsoft Graph!")
    else:
        print(f"Failed to send email: {response.status_code}, {response.text}")

    cleanup_files()

def cleanup_files():
    print("Cleaning up temporary files...")
    for f in [JSON_COMPARTMENT, CSV_COMPARTMENT, JSON_SERVICE, CSV_SERVICE]:
        if os.path.exists(f):
            os.remove(f)
            print(f"Removed {f}")

if __name__ == "__main__":
    today = date.today()
    first_day_this_month = today.replace(day=1)
    last_day_previous_month = first_day_this_month - timedelta(days=1)
    first_day_previous_month = last_day_previous_month.replace(day=1)

    START_DATE = first_day_previous_month.strftime("%Y-%m-%dT00:00:00Z")
    END_DATE = last_day_previous_month.strftime("%Y-%m-%dT00:00:00Z")

    fetch_oci_usage("compartment", START_DATE, END_DATE)
    convert_json_to_csv("compartment")

    fetch_oci_usage("service", START_DATE, END_DATE)
    convert_json_to_csv("service")

    combine_csv_to_excel()
    send_email()

    print("Done. Final report:", EXCEL_FILE)

Report Automation Script (SMTP)

If your setup uses traditional SMTP credentials instead:

#!/usr/bin/env python3
import subprocess
import json
import csv
import pandas as pd
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
import os
from datetime import datetime, date, timedelta

# ---------------- CONFIG ----------------
TENANT_OCID = "ocid1.tenancy.oc1..aaaaaaaaocxvqaamvzj4uxsmy6oofphb2ryf7iwmetnwiazzgsw2h4roycda"

JSON_COMPARTMENT = "compartment_usage.json"
CSV_COMPARTMENT = "compartment_usage.csv"

JSON_SERVICE = "service_usage.json"
CSV_SERVICE = "service_usage.csv"

EXCEL_FILE = "oci_cost_report.xlsx"

# Email settings
SMTP_SERVER = "your-smtp-host"
SMTP_PORT = 587
SMTP_USER = "your-smtp-user"
SMTP_PASS = "your-smtp-password"

FROM_EMAIL = "your-from-email"
TO_EMAILS = ["your-to-email"]
SUBJECT = "Oracle Cloud Monthly Cost Report"
# -----------------------------------------

def fetch_oci_usage(mode, start_date, end_date):
    group_by = '["compartmentName"]' if mode == "compartment" else '["service"]'
    output_file = JSON_COMPARTMENT if mode == "compartment" else JSON_SERVICE
    print(f"Fetching usage data grouped by {group_by} from {start_date} to {end_date} ...")
    try:
        subprocess.run([
            "oci", "usage-api", "usage-summary", "request-summarized-usages",
            "--auth", "instance_principal",
            "--tenant-id", TENANT_OCID,
            "--granularity", "MONTHLY",
            "--time-usage-started", start_date,
            "--time-usage-ended", end_date,
            "--query-type", "COST",
            "--group-by", group_by,
            "--compartment-depth", "1",
            "--output", "json"
        ], check=True, stdout=open(output_file, "w"))
        print(f"JSON data saved to {output_file}")
    except subprocess.CalledProcessError:
        print(f"OCI Usage API call failed for {mode}.")
        exit(1)

def convert_json_to_csv(mode):
    json_file = JSON_COMPARTMENT if mode == "compartment" else JSON_SERVICE
    csv_file = CSV_COMPARTMENT if mode == "compartment" else CSV_SERVICE
    total_amount = 0.0
    print(f"Converting {json_file} -> {csv_file} ...")
    try:
        with open(json_file, "r") as f:
            data = json.load(f)

        with open(csv_file, "w", newline="") as csvfile:
            writer = csv.writer(csvfile)
            if mode == "compartment":
                writer.writerow(["Usage Started", "Usage Ended", "Compartment Name", "Amount", "Currency"])
            else:
                writer.writerow(["Usage Started", "Usage Ended", "Service Name", "Amount", "Currency"])

            currency_for_total = "USD"

            for item in data.get("data", {}).get("items", []):
                usage_started = item.get("time-usage-started", "").split("T")[0] if item.get("time-usage-started") else ""
                usage_ended = item.get("time-usage-ended", "").split("T")[0] if item.get("time-usage-ended") else ""
                name_field = item.get("compartment-name") if mode == "compartment" else item.get("service")
                name_field = name_field or ""

                currency = item.get("currency") or "USD"
                currency_for_total = currency

                computed_amount_raw = item.get("computed-amount")
                computed_amount = round(float(computed_amount_raw), 2) if computed_amount_raw not in (None, "") else 0.00
                total_amount += computed_amount

                writer.writerow([usage_started, usage_ended, name_field, computed_amount, currency])

            writer.writerow(["", "", "Total Cost", round(total_amount, 2), currency_for_total])
        print(f"CSV created: {csv_file}")
    except Exception as e:
        print(f"Failed to create CSV for {mode}: {e}")
        exit(1)

def combine_csv_to_excel():
    print(f"Combining CSVs into {EXCEL_FILE} ...")
    try:
        with pd.ExcelWriter(EXCEL_FILE) as writer:
            pd.read_csv(CSV_COMPARTMENT).to_excel(writer, sheet_name="Compartment Usage", index=False)
            pd.read_csv(CSV_SERVICE).to_excel(writer, sheet_name="Service Usage", index=False)
        print(f"Excel file created: {EXCEL_FILE}")
    except Exception as e:
        print(f"Failed to create Excel file: {e}")
        exit(1)

def send_email():
    print("Sending email via SMTP...")
    today = datetime.today().strftime("%Y-%m-%d")

    msg = MIMEMultipart()
    msg["From"] = FROM_EMAIL
    msg["To"] = ", ".join(TO_EMAILS)
    msg["Subject"] = f"{SUBJECT} - {today}"

    body = f"Hello,\n\nPlease find attached the OCI monthly cost report as of {today}.\n\nRegards,\nOCI Cost Automation"
    msg.attach(MIMEText(body, "plain"))

    if os.path.exists(EXCEL_FILE):
        with open(EXCEL_FILE, "rb") as attachment:
            part = MIMEBase("application", "octet-stream")
            part.set_payload(attachment.read())
        encoders.encode_base64(part)
        part.add_header("Content-Disposition", f"attachment; filename={os.path.basename(EXCEL_FILE)}")
        msg.attach(part)
    else:
        print(f"File not found: {EXCEL_FILE}")
        cleanup_files()
        return

    try:
        server = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
        server.starttls()
        server.login(SMTP_USER, SMTP_PASS)
        server.sendmail(FROM_EMAIL, TO_EMAILS, msg.as_string())
        server.quit()
        print("Email sent successfully via SMTP!")
    except Exception as e:
        print(f"Failed to send SMTP email: {e}")

    cleanup_files()

def cleanup_files():
    print("Cleaning up temporary files...")
    for f in [JSON_COMPARTMENT, CSV_COMPARTMENT, JSON_SERVICE, CSV_SERVICE]:
        if os.path.exists(f):
            os.remove(f)
            print(f"Removed {f}")

if __name__ == "__main__":
    today = date.today()
    first_day_this_month = today.replace(day=1)
    last_day_previous_month = first_day_this_month - timedelta(days=1)
    first_day_previous_month = last_day_previous_month.replace(day=1)

    START_DATE = first_day_previous_month.strftime("%Y-%m-%dT00:00:00Z")
    END_DATE = last_day_previous_month.strftime("%Y-%m-%dT00:00:00Z")

    fetch_oci_usage("compartment", START_DATE, END_DATE)
    convert_json_to_csv("compartment")

    fetch_oci_usage("service", START_DATE, END_DATE)
    convert_json_to_csv("service")

    combine_csv_to_excel()
    send_email()

    print("Done. Final report generated:", EXCEL_FILE)

Post Views: 61

OCI Cost Report Automation with Python

Managing Oracle Cloud Infrastructure Costs with Automated Reports

1. Prerequisites and Setup

Python Environment Setup

OCI Instance Principal Setup

Microsoft 365 (O365) Authentication (If Required)

2. Python Automation Script

Report Automation Script (O365 – Graph API)

Report Automation Script (SMTP)

By Lalantha Madushan