A Practical FinOps Pipeline for Azure SQL: From Azure CLI to Power BI Without Log Analytics

Azure SQL Metrics Export – A FinOps Pipeline Without Log Analytics

As a FinOps consultant working with mid-sized and large organizations, I often land in complex environments where access to production systems, logs, or advanced monitoring tools is limited ; especially for external contractors. Despite that, I’m expected to produce actionable insights fast.

This article explains how I built a self-contained metrics pipeline — using only Azure CLI and Python ; to collect, transform, and prepare Azure SQL performance data for analysis, without relying on premium monitoring features or costly third-party tools.

This method is part of a broader effort to evaluate whether DTU-based databases could be migrated to more cost-effective vCore serverless models : a scenario many organizations face when scaling cloud usage.

In this article, I also share my hands-on experience with performance rationalization projects, where these metrics have been used to justify migrating SQL databases from DTU pricing to the vCore serverless model. The specific choice of metrics and aggregation levels discussed throughout reflects real-world scenarios where cost optimization decisions are based on a blend of average usage patterns and performance peaks.

This guide will show you how to:

• Collect Azure SQL performance metrics via the Azure Monitor API using Bash scripts.
• Transform and structure this data using Python into a CSV aligned with Azure billing reports.
• Build a cost-aware monitoring strategy integrated with tools like Power BI, even without Log Analytics.

You can find the full source code on GitHub:

sidmechant/azure-sql-metrics-finops

Table of Contents

• 1. Why This Approach
• 2. Architecture Overview
• 3. Bash Script: Exporting Metrics
• 4. Sample Output (Raw JSON)
• 5. Python Script: Transforming Metrics
• 6. Practical Extensions
• 7. Conclusion

1. Why This Approach

Why These Metrics Matter in FinOps

The selection of specific metrics in this guide is driven by real-world FinOps objectives:

CPU Percent helps evaluate compute saturation and identify chronic underutilization or periodic spikes.

DTU Consumption Percent provides a normalized measure of the overall capacity consumption in DTU-based databases, useful when comparing across different service tiers.

Storage Percent tracks how close databases are to their storage limits, which can influence decisions on scaling or changing service tiers.

Log Write Percent and Workers Percent are valuable for detecting backend bottlenecks and query concurrency issues, often overlooked in cost reports.

Deadlock Count serves as a performance health indicator, signaling contention issues that may require scaling up or query optimization.

This carefully selected set of metrics supports actionable FinOps decisions such as:

• Justifying a shift from DTU to vCore or serverless models based on consistent underutilization.
• Detecting high-peak workloads that may be better suited for scaling up or elasticity mechanisms like serverless compute.
• Aligning technical performance data with billing reports for transparent reporting to stakeholders.

No additional costs: It uses native Azure CLI APIs, avoiding Log Analytics and other premium services.

Multi-subscription support: The script collects data across multiple Azure subscriptions in a single run.

Customizable output: Flexible configuration for metrics selection, aggregation type, and data retention period.

Integration-ready: The data output format aligns with Azure billing reports, simplifying cross-analysis between usage and costs.

This is not just a cost-avoidance strategy , it's also about autonomy. In large organizations, access requests can take days or weeks to be approved. By building my own collection pipeline through native CLI APIs, I removed dependency on internal platform teams while ensuring full reproducibility of the data used in decision-making.

This approach is ideal for FinOps practitioners who want to monitor real usage patterns to guide cost optimization efforts.

2. Architecture Overview

Here is the lightweight FinOps pipeline I used for this project:

This structure allows for:

• Platform-agnostic data collection (Bash / Azure CLI)

• Reproducible transformation via Python

• Output that matches billing data structure

• Easy integration into tools like Power BI

3. Bash Script: Exporting Metrics

View the Bash script on GitHub:

scripts/export_metrics.sh

This script collects Azure SQL Database metrics across multiple subscriptions while handling API rate limits and excluding irrelevant databases (e.g., vCore or 'master'). It is designed to be flexible, adaptable, and efficient for large-scale environments.

Key Features

Multi-subscription handling: Automatically iterates through all active Azure subscriptions using the Azure CLI, ensuring visibility across environments (e.g., development, staging, production). This is crucial in FinOps to avoid isolated analysis limited to a single subscription.

Targeted database selection: Filters out irrelevant databases like master and optionally excludes databases running on vCore models. This prevents wasting API calls on databases where DTU-specific metrics are not applicable.

Custom metric selection: Allows users to define exactly which metrics to collect via the METRICS variable. For my own analyses, I specifically select cpu_percent, dtu_consumption_percent, and storage_percent because they provide a holistic view of compute and storage pressure. I also include log_write_percent, workers_percent, and deadlock to assess query workload saturation and contention issues. This approach is tailored to justify decisions like shifting from DTU to vCore (especially serverless), based on actual workload patterns.

Granularity flexibility: Supports different time intervals via the INTERVAL parameter. Common choices are PT5M (5 minutes), PT30M (30 minutes), PT1H (1 hour), or P1D (1 day). The choice of interval is driven by the expected variability of workload. For highly transactional databases, I recommend PT5M for peak detection; for cost baseline reports, PT1H or P1D are sufficient.

Aggregation customization: Enables retrieval of multiple aggregation types such as Average, Maximum, and Minimum to provide a more comprehensive view of performance trends. This helps distinguish between chronic underutilization (average) and risk peaks (maximum). For example, databases averaging 25% CPU but peaking at 90% may require different scaling decisions.

API error handling and rate-limiting protection: Incorporates checks to exclude databases with no available metrics and introduces pauses between API calls to respect Azure Monitor rate limits. For environments exceeding 500 databases, I recommend extending sleep intervals and optionally batching requests per resource group.

Core configuration example:

--aggregation "Average" "Maximum" "Minimum"

It is important to explicitly define aggregation types; otherwise, Azure defaults to Average only, potentially masking performance outliers.

Retention and date flexibility:

START_DATE="$(date -u -d '-7 days' +"%Y-%m-%dT%H:%M:%SZ")"
END_DATE="$(date -u +"%Y-%m-%dT%H:%M:%SZ")"

Dynamic date calculation enables rolling window reporting, useful for weekly optimization reviews.

This Bash script represents the first component of a lightweight monitoring pipeline, facilitating data-driven FinOps analysis without additional Azure monitoring costs.

4. Sample Output (Raw JSON)

5. Python Script: Transforming Metrics

View the Python script on GitHub:

scripts/transform_metrics.py

Once metrics are collected, a Python script converts the raw JSON into a structured CSV, transforming scattered time series data into a Power BI-compatible format.

Key elements

• Parses resource identifiers into structured fields (subscription, server, database).
• Pivots metrics so each metric becomes a dedicated column.
• Aligns column names to match Azure billing exports.

Example transformation step:

This pipeline produces a structured output easily consumable in Power BI for reporting and cost analysis.

6. Practical Extensions

• Azure Automation: Schedule recurring data collection weekly or monthly.
• Blob Storage Integration: Archive historical performance data for trend analysis.
• Power BI Reports: Create dashboards aligning technical performance with financial costs.
• Custom Metrics: Modify the Bash script to include specific metrics relevant to your workloads.

Example customization:

METRICS="cpu_percent,storage_percent,workers_percent"
INTERVAL="PT1H"

7. Conclusion

This article focused on a core building block of any effective FinOps practice: establishing independent, cost-aware access to usage data. Without reliable metrics — and without being tied to expensive tools or internal platform dependencies — optimization efforts often remain superficial.

By using a lightweight combination of Azure CLI and Python, I was able to implement a solution that scales across subscriptions, respects enterprise constraints, and delivers visibility where it’s most needed — at the intersection of performance and cost.

This approach is not just about collecting data; it's about regaining control. It allowed me to identify low-usage DTU-based databases, highlight peak performance risks, and prepare clear migration paths to vCore serverless — all backed by evidence.

In the next article, I’ll show how this data connects to Azure billing exports, how I created actionable KPIs, and how it all comes together in Power BI to support FinOps decision-making with clarity and impact.

References

• Azure Monitor Metrics API Documentation
• Azure Cost Management and Billing Documentation
• GitHub Repository – azure-sql-metrics-finops

Author: Sid Ahmed Redjini · FinOps Consultant