Building a Weekly Azure Cost Report with Logic App Standard

The Problem

If you manage Azure subscriptions for development teams, sandbox environments, or lab workloads, you've probably had this conversation: "Why is our Azure bill so high this month?"

The Azure portal has great cost analysis tools, but they require someone to actively log in and look. What teams really need is a cost report that lands in their inbox every week, showing exactly what they spent, which services are driving cost, and which resources are the most expensive.

Most organizations reach for Power BI, third-party SaaS tools, or custom applications. But there's a simpler way: a single Logic App Standard workflow that does everything natively inside Azure.

The Solution

Here's what the workflow does every week:

1. Auto-discovers all target subscriptions matching a naming pattern (e.g., sandbox-*)
2. Pulls 7-day cost data from the Azure Cost Management Query API
3. Builds a stacked bar chart in pure HTML/CSS (no images, no JavaScript) grouped by Meter Category
4. Generates tables showing daily breakdown, top 5 most expensive resources, and cost by service category
5. Emails the report to the subscription owner via an email tag on the subscription

The email renders perfectly in Outlook, Gmail, and Apple Mail because it's built entirely with HTML tables and inline CSS. No embedded images, no external dependencies.

Workflow Architecture

Trigger: Every Tuesday 8:00 PM (configurable)
    |
    v
[Key Vault] Get service principal credentials
    |-- YOUR-TENANT-ID
    |-- YOUR-APP-ID
    |-- YOUR-CLIENT-SECRET
    |
    v
Calculate 7-day date range (UTC)
    |
    v
[ARM API] List all subscriptions
    |
    v
Filter: displayName starts with "sandbox-" AND state = Enabled
    |
    v
For each subscription (SEQUENTIAL):
    |
    |-- Reset all variables (HTML builders, totals, colors)
    |-- [ARM API] Get subscription tags
    |-- Extract owner-email tag
    |
    +-- If owner-email exists:
    |   |
    |   |-- [4x PARALLEL HTTP calls]:
    |   |   |-- Daily cost (no grouping) --> table + totals
    |   |   |-- Daily cost by MeterCategory --> stacked chart
    |   |   |-- Top 5 resources by ResourceId --> resource table
    |   |   |-- Total by MeterCategory --> category table + colors
    |   |
    |   +-- [Scope: Build Chart]
    |   |   |-- Assign colors to each meter category
    |   |   |-- Compute daily totals + find max for scaling
    |   |   |-- Build stacked bar segments per day
    |   |   |-- Build legend HTML
    |   |
    |   |-- Build top 5 resource table rows
    |   |-- Build meter category table rows
    |   |-- Compose full HTML email body
    |   |-- Send via Office 365
    |
    +-- If no owner-email: skip

Prerequisites

1. Service Principal

Create an App Registration in Entra ID (Azure AD) and generate a client secret. This service principal needs two RBAC roles on the target subscriptions (or at the management group level):

2. Key Vault Secrets

Store the service principal credentials in an Azure Key Vault. The Logic App will use a Key Vault Service Provider connection (built-in connector) to retrieve them at runtime.

3. Subscription Tags

Each target subscription needs a tag that tells the workflow who to email:

Subscriptions without this tag are silently skipped.

4. Office 365 Connection

The workflow sends emails via the Office 365 Outlook managed API connection. Configure this in the Logic App's Connections blade using a service account or shared mailbox.

5. Logic App Standard

Create a Logic App Standard (not Consumption) resource. Standard gives you stateful workflows, Key Vault built-in connector, and the ability to run multiple workflows in a single app.

Step 1: Trigger & Credentials

The workflow fires on a weekly recurrence trigger. First, it retrieves the service principal credentials from Key Vault:

{
  "Recurrence_-_Weekly_Cost_Report": {
    "type": "Recurrence",
    "recurrence": {
      "frequency": "Week",
      "interval": 1,
      "schedule": {
        "weekDays": ["Tuesday"],
        "hours": [20],
        "minutes": [0]
      },
      "timeZone": "Eastern Standard Time"
    }
  }
}

The three Key Vault actions run sequentially, each secured with runtimeConfiguration.secureData so credentials never appear in run history:

{
  "Get_secret_-_tenantId": {
    "type": "ServiceProvider",
    "inputs": {
      "parameters": {
        "secretName": "sp-tenant-id"
      },
      "serviceProviderConfiguration": {
        "connectionName": "keyVault-1",
        "operationId": "getSecret",
        "serviceProviderId": "/serviceProviders/keyVault"
      }
    },
    "runtimeConfiguration": {
      "secureData": {
        "properties": ["inputs", "outputs"]
      }
    }
  }
}

Step 2: Discover Subscriptions

Instead of hardcoding subscription IDs, the workflow dynamically discovers all matching subscriptions using the ARM API:

GET https://management.azure.com/subscriptions?api-version=2022-12-01

Authentication: ActiveDirectoryOAuth
  Tenant: @{body('Get_secret_-_tenantId')?['value']}
  ClientId: @{body('Get_secret_-_appId')?['value']}
  Secret: @{body('Get_secret_-_secret')?['value']}
  Audience: https://management.azure.com

Then a Filter Array action narrows it down:

@and(
  startsWith(item()?['displayName'], 'sandbox-'),
  equals(item()?['state'], 'Enabled')
)

Step 3: Query the Cost Management API

For each subscription, the workflow makes 4 parallel API calls to the Cost Management Query API. Running them in parallel cuts execution time significantly:

Query 1: Daily Cost (for the table)

POST https://management.azure.com/subscriptions/{id}/providers/
  Microsoft.CostManagement/query?api-version=2023-11-01

{
  "type": "ActualCost",
  "timeframe": "Custom",
  "timePeriod": {
    "from": "@{variables('StartDate')}",
    "to": "@{variables('EndDate')}"
  },
  "dataset": {
    "granularity": "Daily",
    "aggregation": {
      "totalCost": {
        "name": "Cost",
        "function": "Sum"
      }
    }
  }
}

Query 2: Daily Cost by Meter Category (for the stacked chart)

{
  "type": "ActualCost",
  "timeframe": "Custom",
  "timePeriod": {
    "from": "@{variables('StartDate')}",
    "to": "@{variables('EndDate')}"
  },
  "dataset": {
    "granularity": "Daily",
    "aggregation": {
      "totalCost": { "name": "Cost", "function": "Sum" }
    },
    "grouping": [
      { "type": "Dimension", "name": "MeterCategory" }
    ]
  }
}

Query 3: Top 5 Resources by Cost

{
  "type": "ActualCost",
  "timeframe": "Custom",
  "timePeriod": {
    "from": "@{variables('StartDate')}",
    "to": "@{variables('EndDate')}"
  },
  "dataset": {
    "granularity": "None",
    "aggregation": {
      "totalCost": { "name": "Cost", "function": "Sum" }
    },
    "grouping": [
      { "type": "Dimension", "name": "ResourceId" }
    ],
    "sorting": [
      { "direction": "descending", "name": "Cost" }
    ],
    "top": 5
  }
}

Query 4: Total by Meter Category (for category table + color assignment)

{
  "type": "ActualCost",
  "timeframe": "Custom",
  "timePeriod": {
    "from": "@{variables('StartDate')}",
    "to": "@{variables('EndDate')}"
  },
  "dataset": {
    "granularity": "None",
    "aggregation": {
      "totalCost": { "name": "Cost", "function": "Sum" }
    },
    "grouping": [
      { "type": "Dimension", "name": "MeterCategory" }
    ],
    "sorting": [
      { "direction": "descending", "name": "Cost" }
    ]
  }
}

Step 4: Build the Stacked Chart

This is the most complex part. Since Logic Apps can't render images, we build the chart using pure HTML/CSS that works in email clients:

The approach:

1. Assign colors to each meter category from a 10-color palette
2. Find the max daily total to scale bar heights (max = 160px)
3. For each date, filter the category-grouped rows, then build stacked <div> segments inside a table cell
4. Build a legend mapping colors to category names

#4FC3F7  (light blue)     #1565C0  (dark blue)
#FF8A65  (orange)         #66BB6A  (green)
#AB47BC  (purple)         #FFA726  (amber)
#EF5350  (red)            #26A69A  (teal)
#78909C  (blue-grey)      #FFCA28  (yellow)

Each bar in the chart is a <td> cell containing vertically stacked <div> elements. The height of each segment is calculated as:

height = (categoryCost / maxDailyTotal) * 160px

Using <table> layout instead of CSS flexbox or grid ensures the chart renders correctly in Outlook (which uses the Word rendering engine and ignores most modern CSS).

Step 5: Compose & Send the Email

The final Compose action assembles all the HTML fragments into a single email body:

1. Header banner with gradient background showing total 7-day cost
2. Summary bar with subscription name and date range
3. Stacked bar chart (built in Step 4)
4. Daily cost breakdown table
5. Top 5 resources table
6. Meter category table with color dots matching the chart legend
7. Footer with your team's signature

The email is sent using the Office 365 connector's SendEmailV2 action with isHtml: true.

Complete Workflow JSON

Here's a summary of the key variables the workflow initializes:

Deployment Guide

1. Create a Logic App Standard resource in the Azure Portal
2. Create a new Stateful workflow named weekly-cost-report
3. Switch to Code View
4. Paste the contents of workflow.json
5. Save
6. Configure the keyVault-1 and office365 connections in the Connections blade
7. Test: Run Trigger > Recurrence from the workflow Overview

Customization Ideas

This workflow is a foundation you can build on:

• Change frequency to daily or monthly by adjusting the recurrence trigger
• Add budget thresholds with conditional formatting (red highlight when cost exceeds a limit)
• Include month-over-month comparison by adding a second set of API calls for the previous period
• Post to Teams instead of (or in addition to) email using the Teams connector
• Filter by resource group using the Cost Management API's filter parameter
• Add anomaly detection by comparing current week's cost to a rolling average
• Send to Slack using a webhook action instead of Office 365

The stacked chart approach also works for grouping by ResourceGroup, ServiceName, or any other dimension the Cost Management API supports.