Azure Solutions

Data Acquisition: Azure Cost Management

It is not possible to simply query the Azure cost details dataset with your favorite data tools. It is possible, however, to get the data.

Microsoft Cost Management + Billing documentation abstracts multiple options: 1) Azure Portal, 2) Power BI, 3) API, and 4) Exports.

This documentation details step-by-step instructions for some of these options.

Option	Pros	Cons
Azure Portal	- Easy-to-Use - No additional tools or coding required	- Targets “small cost detail” datasets (<2GB)
Power BI	???	- Requires permissions most people lack
API	- Can pull historical data greater than 1 year	- No bulk query - Limited to 15k read requests per hour (more)
…via Data Factory	- Familiar orchestration engine	- Nested iteration impossible
…via Logic App	- Nested iteration possible	- Challenging interface - Slow processing - Difficult to monitor progress
…via Function App	- Nested iteration possible - Visual Studio {i.e., IntelliSense, NuGet, and GitHub}	- Requires coding expertise
Exports	- “Most scalable solution”	- No historical data (pull begins day you instantiate)

This list of Pros and Cons is based on my research, experience and perception ONLY.

Your answer to “why use Option X?” may be as simple as the fact that you favor that option.

API, via Data Factory (or Synapse Pipeline)

This use case considers the following requirement statements:

“We want to analyze Azure resource costs across many subscriptions”
“An understanding of resources and costs would really help us get a handle on inherited subscriptions”
“Power BI, Cost Management connector requires billing account permissions we cannot get”

Step 1: Prepare Infrastructure

In addition to the items listed at the beginning of this documentation, this solution requires the following resources:

Application Registration with “Cost Management Reader” role assignment (granted at Subscription or Resource Group)
Data Explorer Cluster and Database
Synapse with a Data Explorer Integration Dataset and Data Explorer, “AllDatabasesAdmin” permissions for the Synapse, System-Assigned Managed Identity

Step 2: Create Destination Table

In this step, we will create the Data Explorer table which will serve as destination for our Synapse Pipeline

Navigate to Data Explorer and then select Query from the navigation

Run the following KQL:

.create table CostManagement (
    Currency: string
    , PreTaxCost: real
    , ResourceType: string
    , UsageDate: string
    )

Note: This initial version of the table includes only a few of the columns that might be included as you evolve your query logic

Step 3: Create Linked Service

In this step, we will create the Linked Service we will use to get our Bearer Token.

Open Synapse Studio and click the Manage navigation icon
Click “Linked services” from the “External connections” grouping in the resulting navigation
Click “+ New”
Search for and select “REST” on the “New linked service” pop-out and then click Continue

Complete the resulting “New linked service” pop-out, including:

Prompt	Entry
Base URL	Modify and enter: `https://management.azure.com/subscriptions/{Subscription Id}/providers/Microsoft.CostManagement/query?api-version=2021-10-01`
Authentication Type	Select Anonymous

Click “Test connection” to confirm successful connection and then click Create

Note: Consider including the Subscription Name in your Linked Service and Dataset names

Step 4: Create Dataset

In this step, we will create the Dataset we will use to get our Bearer Token.

Open Synapse Studio and click the Data navigation icon
Click + and then select “Integration dataset” from the Linked grouping in the resulting navigation
Search for and select REST on the “New linked service” pop-out and then click Continue
Complete the “Set properties” pop-out form and then click OK
Click “Test connection” to confirm successful connection
Click “Publish all”, review changes on the resulting “Publish all” pop-out and then click Publish

Step 5: Create Pipeline

In this step, we will create a Pipeline and add Activities.

Open “Synapse Studio” and click the Integrate navigation icon
Click + and select Pipeline from the resulting dropdown menu

Step 5a: Create Activity 1, Get Token

This activity will make a REST API call and get a bearer token.

Expand General in the Activities bar
Drag-and-drop a Web component into the main window

Complete the form on the Settings tab

Snipped: September 2, 2022

Prompt	Entry
URL	Modify and enter:`https://login.microsoftonline.com/{TenantId}/oauth2/token`
Method	Select POST
Headers	Click “+ Add” and enter key-value pair: `content-type` :: `application/x-www-form-urlencoded`
Body	Modify and enter: `grant_type=client_credentials&client_id={Client Identifier}&client_secret={Client Secret}& resource=https://api.loganalytics.io/`

Click Debug and confirm success
On the resulting Output, mouse over the “Get Token” row and click on the Output icon

Copy the “access token” value for later use… in the example below, everything from eyJ0... to ...1mqg

"access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSIsImtpZCI6IjJaUXBKM1VwYmpBWVhZR2FYRUpsOGxWMFRPSSJ9.eyJhdWQiOiJodHRwczovL21hbmFnZW1lbnQuYXp1cmUuY29tLyIsImlzcyI6Imh0dHBzOi8vc3RzLndpbmRvd3MubmV0LzE2YjNjMDEzLWQzMDAtNDY4ZC1hYzY0LTdlZGEwODIwYjZkMy8iLCJpYXQiOjE2NjIxMzU3MzgsIm5iZiI6MTY2MjEzNTczOCwiZXhwIjoxNjYyMTM5NjM4LCJhaW8iOiJFMlpnWURnZXE4L2x6ZGlTWmFNdmNQTDVtUXZyQUE9PSIsImFwcGlkIjoiNzVhZmM4ZTktZjI5Ny00YmE0LThiNWItNWNlMzQ5NTI1OGExIiwiYXBwaWRhY3IiOiIxIiwiaWRwIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvMTZiM2MwMTMtZDMwMC00NjhkLWFjNjQtN2VkYTA4MjBiNmQzLyIsImlkdHlwIjoiYXBwIiwib2lkIjoiZmY1Njc1NzQtMzY2Ny00ZDJiLWIxNmUtMmMyOTJjOTZkYzBkIiwicmgiOiIwLkFVWUFFOEN6RmdEVGpVYXNaSDdhQ0NDMjAwWklmM2tBdXRkUHVrUGF3ZmoyTUJPQUFBQS4iLCJzdWIiOiJmZjU2NzU3NC0zNjY3LTRkMmItYjE2ZS0yYzI5MmM5NmRjMGQiLCJ0aWQiOiIxNmIzYzAxMy1kMzAwLTQ2OGQtYWM2NC03ZWRhMDgyMGI2ZDMiLCJ1dGkiOiJ3QklycG9nYzIwZThxdXZ0NjhLT0FBIiwidmVyIjoiMS4wIiwieG1zX3RjZHQiOjE2NDUxMzcyMjh9.JSSyRQzqkcDatL9d8Vlbd8pr6_FXPmYJdf5fiZI1zWbQalH48pIVF57l4ZiWEwQLGdzsfZEwZQN9-ujSUWPSXGIUw3iTGqtAl8sI48G4hD8rPB9YwCt8sSKWVd1vx30_f7Cm4CyDiY7qNqkOfNdADzfpBj-CwB2U4zhmrVzbFk57qIpAuXUDDlAVen6JKXokC931mmUpZ_fYDBHiE14tXgPalPklP2bxaJKTlahObvfuLworr-A70zJi2Pdp5ckmMR3GqySM34Hz9uMbKtnEs8fJNJRYaeVGSHIjbY0Zp0wXgwmcaK31TXsNOl1_HXoMwvwLPicqR1y28Mq0ef1mqg"

Step 5b: Create Activity 2, Copy Data

This activity will make a REST API call, parse the response and write the data to Data Explorer.

Expand “Move & Transform” in the Activities bar
Drag-and-drop a “Copy Data” component into the activity window
Create a dependency from the “Get Token” component to the “Copy Data” component

“Source” tab

Enter values on the Source tab

Prompt	Entry
Source dataset	Select your REST dataset
Request method	Select POST
Additional headers	Click + Add and enter key-value pairs: `content-type` :: `application/json;charset=utf-8` `authorization` :: `@concat('Bearer ',activity('Get Token').output.access_token)`

Finally, paste the following JSON into ‘Request body’:

{
      "type": "Usage",
      "timeframe": "Custom",
      "timePeriod": {
          "from": "@{formatDateTime(adddays(utcnow(),-2),'yyyy-MM-ddT00:00:00')}",
          "to": "@{formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss')}"
      },
      "dataset": {
          "granularity": "Daily",
          "aggregation": {
              "totalCost": {
                  "name": "PreTaxCost",
                  "function": "Sum"
              }
          },
          "grouping": [
              {
                  "type": "Dimension",
                  "name": "ResourceType"
              }
          ]
      }
  }

Notes:

Time period values {e.g., 2022-01-01T00:00:00} are parameterized for use in a dynamic expression in the example above
The API limits how many dimensions can be included to 15
See links in Reference (below) for additional help with preparation of Request Body JSON {i.e., the query}

Click “Preview data”
On the resulting “Please provide actual value of the parameters to preview data” pop-out, paste the previously-copied Bearer Token value into the Value textbox and then click OK

Note: If your “Preview Data” fails and you get an “The access token is invalid” error, it is likely that the token expired… simply Debug to get a new token

Review the response and then close the “Preview data” window

“Sink” tab

Click on the Sink tab
Complete the form by selecting your Data Explorer dataset

“Mapping” tab

Click on the Mapping tab
Click on “Import schemas”
The resulting “Please provide…” pop-out should still have the Bearer Token value in the Value textbox from when we completed the “Preview Data” step… update as needed and then click OK
Turn on “Advanced editor”
Select “Collection reference” dropdown value, “$[‘properties’][‘rows’]”

Click “+ New mapping” and enter values for each of the four columns:

Name	Column Name
[0]	PreTaxCost
[1]	UsageDate
[2]	ResourceType
[3]	Currency

Click “Publish all”, review changes on the resulting “Publish all” pop-out and then click Publish

Step 6: Confirm Success

In this step, we will process the pipeline and review the resulting data.

Click Debug and confirm successful pipeline processing
Navigate to your Data Explorer Database, and then Query in left-hand navigation
Run the following KQL:
```
CostManagement
| take 10
```
Confirm results

API, via Logic App

This use case considers the following requirement statements:

“We need to capture and analyze cost data from many subscriptions”
“Our subscriptions have more than 1,000 resources and are hitting the Cost Management API’s per-request limitation”
“We want to pull historical data… 730 days {i.e., 2 years}”

The solution described in this documentation will:

Leverage Logic Apps’ nested iteration capability with input parameters for Subscriptions and Start / End Dates
Request data from the Cost Management API at the Resource Group level rather than Subscription level

Notes:

Historical Resource Groups {i.e., have been deleted or renamed} will not surface via Fluent, and will surface as empty strings in the Cost Management API (if queried at the subscription level)
If you have Resource Groups with more than 1,000 resources, you might have to choose a more granular scope or other strategy

Step 1: Prepare Resources

In addition to the items listed at the beginning of this documentation, this solution requires the following resources:

Application Registration with “Cost Management Reader” role assignment (granted at Subscription or Resource Group)
Data Explorer Cluster and Database
Logic App

Step 2: Prepare Workflow

In this step, we will create a workflow, initialize variables, and add parameters.

Navigate to your Logic App

Workflow

Click on “Create workflow >” in the “Create a workflow in Designer” rectangle
On the resulting page click “+ Add”
Complete the resulting “New workflow” pop-out form and then click Create

Note: Choice of stateful or stateless will depend on your use case and environment… reference: https://docs.microsoft.com/en-us/azure/logic-apps/single-tenant-overview-compare#stateful-stateless
Click on the link for your new workflow
Navigate to Designer
On the resulting screen and “Add a trigger” pop-out, search for and then select “Schedule” (aka “Recurrence”)
Complete the resulting pop-out form and then click Save

Note: I chose daily because I believe that that is how often Cost Management data updates

Initialize Variable, Date

Click the + icon and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Initialize variable”

Complete the resulting “Initialize variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Enter “Date”
Type	Select “String”
Value	{null}

Note: Logic Apps does not have a data type for DateTime, so we use string and handle usage in expressions

Initialize Variable, Scope

Snipped: September 27, 2022

Repeat for string variable Scope

Initialize Variable, KQL

Snipped: September 27, 2022

Repeat for string variable KQL

Parameters

Click Parameters in the menu bar
On the resulting Parameters pop-out form, click “+ Create parameter”

Complete the pop-out form, including:

Prompt	Entry
Name	Enter “Subscriptions”
Type	Select “Array”
Value	Your SubscriptionId values in form: `[ "{Subscription1_Id}","{Subscription2_Id}" ]`

Snipped: September 27, 2022

Repeat for parameter StartDate

Prompt	Entry
Name	Enter “StartDate”
Type	Select “String”
Value	Enter `2022-01-01` (or a date value that is meaningful for you)

Note: Date values will be required in ISO-8601 formatted strings {e.g., 2022-09-15T00:00:00.0000000}; abbreviated versions {e.g., 2022-09-15} work fine

Repeat for parameter EndDate

Prompt	Entry
Name	Enter “EndDate”
Type	Select “String”
Value	Enter `2022-08-31` (or a date value that is meaningful for you)

Note: Parameters will be alphabetized regardless of the order in which you create them

Click X to close the pop-out form and then click Save

Step 3: Get Bearer Token

In this step, we will request an access token from the Client Credentials Token URL and initialize a Token variable.

Navigate to Designer

HTTP, Get Token

Click the + icon underneath “Recurrence”
Click “Add a parallel branch” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “HTTP”

Complete the resulting “HTTP” pop-out form, Parameters tab, including:

Prompt	Entry
Method	Select “POST”
URI	Enter `https://login.microsoftonline.com/16b3c013-d300-468d-ac64-7eda0820b6d3/oauth2/token`
Headers	Add `content-type` :: `application/x-www-form-urlencoded`
Body	Enter `grant_type=client_credentials&client_id={Client Identifier}&client_secret={Client Secret}&resource=https://management.azure.com/`

Note: If you expect processing to take longer than an hour {i.e., the lifespan of a token}, you might consider moving token handling to the nested iteration

Initialize Variable, Token

Click the + icon under HTTP and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Initialize variable”

Complete the resulting “Initialize variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Enter “Token”
Type	Select “String”
Value	Enter expression: `concat('Bearer ',body('HTTP,_Get_Token').access_token)` …and then click OK

Note: The parenthetic “body” value {i.e., body('HTTP,_Get_Token')} may need to change depending on what you named the component

Click Save

Confirm Success

Navigate to Overview, click “Run Trigger”, and then click Run in the resulting dropdown menu
Confirm that all actions succeed and click on those you would like to understand better

Step 4: Prepare Dates Array

In this step, we will iterate through dates between StartDate and EndDate and append to the Dates array.

Navigate to Designer

Initialize Variable, Counter

Click the + icon underneath “Recurrence” and then “Add a parallel branch” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Initialize variable”

Complete the resulting “Initialize variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Enter “Counter”
Type	Select “Integer”
Value	Enter 0

Initialize Variable, Dates

Snipped: September 27, 2022

Repeat for array variable Dates (with no initial value)

Do..Until

Click the + icon and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Until”

Complete the resulting Until pop-out form, Parameters tab, including:

Prompt	Entry
Choose a value	Enter expression: `addDays(parameters('StartDate'), variables('Counter'))`
Type	Select “is greater than”
Choose a value	Enter expression: `addDays(parameters('EndDate'), 0)`

Append to Array Variable, Date

Click the + icon inside the “Do..Until” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Append to array variable”

Complete the resulting “Append to array variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Select “Dates”
Value	Enter expression: `addDays(parameters('StartDate'),variables('Counter'))`

Increment Variable, Counter

Click the + icon inside the “Do..Until” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Increment variable”

Complete the resulting “Append to array variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Select “Counter”
Value	Enter “1”

Click Save

Confirm Success

Navigate to Overview, click “Run Trigger”, and then click Run in the resulting dropdown menu
Click on the new “Running” item in the “Run History” list
Confirm that all actions succeed and click on those you would like to understand better

Step 5: Iterate through Subscriptions

In this step, we will create a “For Each” action for Subscriptions.

Navigate to Designer

For Each, Subscription

Click the + icon at the bottom of the page and then “Add an action” on the resulting pop-up menu

Note: Dependencies from all parallel branches will be added to the new action
On the resulting “Add an action” pop-out, search for and then select “For each”
Complete the resulting “For each” pop-out form, Parameters tab, including:

Prompt Entry

Select an output from previous steps Select “Subscriptions”

Prompt	Entry
Select an output from previous steps	Select “Subscriptions”

HTTP, Get Resource Groups

Click the + icon inside the “For Each, Subscription” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “HTTP”

Complete the resulting “HTTP” pop-out form, Parameters tab, including:

Prompt	Entry
Method	Select “GET”
URI	Enter expression: `concat('https://management.azure.com/subscriptions/',item(),'/resourcegroups?api-version=2021-04-01')`
Headers	Add headers: `Authorization` :: `variables('Token')` `content-type` :: `application/json;charset=utf-8`

Note: Capitalization of the header key “Authorization” appears to be important to this API

Click Save

Confirm Success

Navigate to Overview, click “Run Trigger”, and then click Run in the resulting dropdown menu
Click on the new “Running” item in the “Run History” list
Confirm that all actions succeed and click on those you would like to understand better

Step 6: Iterate through Resource Groups

In this step, we will create a “For Each” action for Resource Groups {aka Scopes}.

Navigate to Designer

For Each, Resource Group

Click the + icon inside the “For Each, Subscription” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “For each”

Complete the resulting “For each” pop-out form, Parameters tab, including:

Prompt	Entry
Select an output from previous steps	Enter expression: `body('HTTP,_Get_Resource_Groups').value`

Set Variable, Scope

Click the + icon inside the “For Each, Resource Group” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Set variable”

Complete the resulting “Set variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Select “Scope”
Value	Enter expression: `item().id`

Click Save

Confirm Success

Navigate to Overview, click “Run Trigger”, and then click Run in the resulting dropdown menu
Click on the new “Running” item in the “Run History” list
Confirm that all actions succeed and click on those you would like to understand better

Step 7: Iterate through Dates

In this step, we will nest “For Each” actions for Dates.

Navigate to Designer

For Each, Date

Click the + icon inside the “For Each, Resource Group” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “For each”
Complete the resulting “For each” pop-out form, Parameters tab, including:

Prompt Entry

Select an output from previous steps Enter expression: variables('Dates')

Prompt	Entry
Select an output from previous steps	Enter expression: `variables('Dates')`

Set Variable, Date

Click the + icon inside the “For Each, Date” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Set variable”

Complete the resulting “Set variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Select “Date”
Value	Enter expression: `item()`

Click Save

Confirm Success

Navigate to Overview, click “Run Trigger”, and then click Run in the resulting dropdown menu
Click on the new “Running” item in the “Run History” list
Confirm that all actions succeed and click on those you would like to understand better

Step 8: Get Cost Data

In this step, we will request and process data from the Cost Management API.

Navigate to Designer

HTTP, Get Costs

Click the + icon inside the “For Each, Date” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “HTTP”

Complete the resulting “HTTP” pop-out form, Parameters tab, including:

Prompt	Entry
Method	Select “POST”
URI	Enter expression: `https://management.azure.com/@{variables('Scope')}/providers/Microsoft.CostManagement/query?api-version=2021-10-01`
Headers	Add headers: `authorization` :: `variables('Token')` `content-type` :: `application/json;charset=utf-8`

Finally, paste the following in Body:

{
  "dataset": {
    "aggregation": {
      "totalCost": {
        "function": "Sum",
        "name": "PreTaxCost"
      }
    },
    "granularity": "Daily",
    "grouping": [
      {
        "name": "ResourceGroupName",
        "type": "Dimension"
      },
      {
        "name": "ResourceType",
        "type": "Dimension"
      },
      {
        "name": "ResourceId",
        "type": "Dimension"
      },
      {
        "name": "ResourceLocation",
        "type": "Dimension"
      },
      {
        "name": "MeterCategory",
        "type": "Dimension"
      },
      {
        "name": "MeterSubCategory",
        "type": "Dimension"
      },
      {
        "name": "Meter",
        "type": "Dimension"
      },
      {
        "name": "ServiceName",
        "type": "Dimension"
      },
      {
        "name": "PartNumber",
        "type": "Dimension"
      },
      {
        "name": "PricingModel",
        "type": "Dimension"
      },
      {
        "name": "ChargeType",
        "type": "Dimension"
      },
      {
        "name": "ReservationName",
        "type": "Dimension"
      },
      {
        "name": "Frequency",
        "type": "Dimension"
      }
    ]
  },
  "timePeriod": {
    "from": "@{variables('Date')}",
    "to": "@{variables('Date')}"
  },
  "timeframe": "Custom",
  "type": "Usage"
}

Note: Scope ResourceGroup does not allow use of BillingPeriod and ServiceTier columns

Parse JSON

Click the + icon inside the “For Each, Date” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Parse JSON”

On the resulting “Parse JSON” pop-out form, Parameters tab, Content textbox, select dynamic content “Body” from the “HTTP, Get Costs” grouping and then, paste the following in Schema:

{
    "properties": {
        "eTag": {},
        "id": {
            "type": "string"
        },
        "location": {},
        "name": {
            "type": "string"
        },
        "properties": {
            "properties": {
                "columns": {
                    "items": {
                        "properties": {
                            "name": {
                                "type": "string"
                            },
                            "type": {
                                "type": "string"
                            }
                        },
                        "required": [
                            "name",
                            "type"
                        ],
                        "type": "object"
                    },
                    "type": "array"
                },
                "nextLink": {},
                "rows": {
                    "items": {
                        "type": "array"
                    },
                    "type": "array"
                }
            },
            "type": "object"
        },
        "sku": {},
        "type": {
            "type": "string"
        }
    },
    "type": "object"
}

Click Save

Confirm Success

Navigate to Overview, click “Run Trigger”, and then click Run in the resulting dropdown menu
Click on the new “Running” item in the “Run History” list
Confirm that all actions succeed and click on those you would like to understand better

Step 9: Ingest to Data Explorer

In this step, we will send the Cost Management API response to Data Explorer using an .ingest inline command.

Navigate to Designer

For Each, Response Row

Click the + icon inside the “For Each, Date” action and below the “Parse JSON” action
Then click “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “For each”
On the resulting “For each” pop-out form, Parameters tab, “Select an output from previous steps” textbox, select dynamic content “rows” from the “Parse JSON” grouping

Set Variable, KQL

Click the + icon inside the “For Each, Resource Group” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, search for and then select “Set variable”

Complete the resulting “Set variable” pop-out form, Parameters tab, including:

Prompt	Entry
Name	Select “KQL”
Value	Enter expression: `concat('.ingest inline into table CostManagement <\| ', replace(replace(string(item()),'[',''),']',''))`

Click Save

ADX Command, Ingest

Click the + icon inside the “For Each, Response Row” action and then “Add an action” on the resulting pop-up menu
On the resulting “Add an action” pop-out, click the Azure tab, search for and then select “Run control command and render a chart”

Complete the resulting “Run control command and render a chart” pop-out form, Parameters tab, including:

Prompt	Entry
Control Command	Select variable KQL
Chart Type	Select “Html Table”

Note: the selected “Chart Type” value does not matter; it is required by the Operation, but the result will not be used

Click Save

Confirm Success

Navigate to Overview, click “Run Trigger”, and then click Run in the resulting dropdown menu
Click on the new “Running” item in the “Run History” list
Confirm that all actions succeed and click on those you would like to understand better

Step 10: Query Data

Navigate to Data Explorer Database, then Query in the Data grouping of the left-hand navigation

Run the following KQL:

CostManagement
| extend IngestionTime = ingestion_time()
| sort by IngestionTime desc

API, via Function App

This use case considers the following requirement statements:

“We want to pull data from many subscriptions with many resource groups each”
“We want to pull historical data and ingest daily”
“Synapse Pipelines couldn’t iterate as needed”
“Logic Apps didn’t give us the desired visibility at run-time”

Step 1: Prepare Resources

In addition to the items listed at the beginning of this documentation, this solution requires the following resources:

Application Registration with “Cost Management Reader” role assignment (granted at Subscription or Resource Group)
Data Explorer Cluster and Database
Event Hub
Function App … this, however, will be created during publish, so no need to create in advance
Storage Account
Visual Studio with Azure development workload

Step 2: Create Visual Studio Project

Open Visual Studio
Click “Create a new project”
Search for and select “Azure Functions” on the “Create a new project” page and then click Next
Complete the “Configure your new project” form and then click Next

Complete the “Additional information” form, including:

Prompt	Entry
Functions worker	Select “.NET 6.0 (Long Term Support)”
Function	Select “Timer trigger”
Use Azurite…	Checked
Schedule	Enter “/1 * * *” (the CRON expression for every one minute)

Click Create

Step 3: “Function1.cs” Logic, Update Method

Visual Studio will open a “Function1.cs” tab
Replace the default code public static void Run([TimerTrigger("*/1 * * * *")]TimerInfo myTimer, TraceWriter log) with:
```
public async Task Run(
  [TimerTrigger("*/1 * * * *")] TimerInfo theTimer,
  [EventHub("dest", Connection = "EventHubConnectionAppSetting")] IAsyncCollector<string> theEventHub,
  ILogger theLogger)
```
Logic Explained:
- async Task … provides for use of asynchronous calls
- [EventHub... … provides for output to Event Hub (for later Data Explorer ingestion)

Step 4: Install NuGet, Microsoft.Azure.WebJobs.Extensions.EventHubs

Notice error “The type or namespace ‘EventHub’ could not be found…”; this must be resolved by adding a NuGet Package
Click Tools in the menu bar, expand “NuGet Package Manager” in the resulting menu and then click “Manage NuGet Packages for Solution…”
Search for and select “Microsoft.Azure.WebJobs.Extensions.EventHubs” on the Browse tab of the “NuGet - Solution” page, then click Next
On the resulting popout, check project CostManagement and then click Install
When prompted, click “I Accept” on the “License Acceptance” pop-up

Step 5: “Function1.cs” Logic, Add Variables and Parameters

Return to the “Function1.cs” tab
Replace the default code log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}"); with:
```
string clientid = "{CLIENT ID}",
  clientsecret = "{CLIENT SECRET}",
  tenantid = "{TENANT ID}",
  startDate = "11/4/2022",
  endDate = "11/5/2022";
  
string[] subscriptions = new string[] { "{SUBSCRIPTION ID 1}", "{SUBSCRIPTION ID 2}", "{SUBSCRIPTION ID N}" };
```
Logic Explained:
- {CLIENT ID} and {CLIENT SECRET} … re: your Application Registration (with “Cost Management Reader” role assignment)
- startDate, endDate. and subscriptions … parameterized values necessary for iteration
Note: I am not investing the time for this demonstration, but Key Vault should be used for, at least {CLIENT SECRET}

Step 6: “Function1.cs” Logic, Iterate Subscriptions

Continue on the “Function1.cs” tab

Add the following code at the top:

using Microsoft.Azure.Management.ResourceManager.Fluent;

Add the following code at the bottom:

foreach (string subscription in subscriptions)
{
    var credentials = SdkContext.AzureCredentialsFactory.FromServicePrincipal(
        clientId: clientId,
        clientSecret: clientsecret,
        tenantId: tenantid,
        environment: AzureEnvironment.AzureGlobalCloud
    );
  
    var azure = Microsoft.Azure.Management.Fluent.Azure
        .Authenticate(credentials)
        .WithSubscription(subscriptionId: subscription)
        ;
}

Logic Explained:

foreach (... … will iterate through the previously-created string array of Subscription Id values
var credentials = SdkContext.AzureCredentialsFactory.FromServicePrincipal … generates credentials for use with Azure SDK
var azure = Microsoft.Azure.Management.Fluent.Azure … connects to the current subscription using generated credentials

Step 7: Install NuGet, Microsoft.Azure.Management…

Click Tools in the menu bar, expand “NuGet Package Manager” in the resulting menu and then click “Manage NuGet Packages for Solution…”
Search for and select “Microsoft.Azure.Management.ResourceManager.Fluent” on the Browse tab of the “NuGet - Solution” page, then click Next
On the resulting popout, check project CostManagement and then click Install
When prompted, click “I Accept” on the “License Acceptance” pop-up
Repeat for “Microsoft.Azure.Management.Fluent”

Step 8: “Function1.cs” Logic, Iterate Resource Groups

Continue on the “Function1.cs” tab

Snipped: November 14, 2022

Add the following code at the top:

using System.Net.Http.Headers;
using System.Text.Json;

Add the following code at the bottom:

public class OAuth2
{
    public string? access_token { get; set; }
}

Add the following code to end of the method:

foreach (var resourceGroup in azure.ResourceGroups.List())
{
    /* ************************* Get Token */
  
    string token;
  
    using (HttpClient client = new HttpClient())
    {
        FormUrlEncodedContent content = new FormUrlEncodedContent(new[]{
        new KeyValuePair<string, string>("grant_type", "client_credentials"),
        new KeyValuePair<string, string>("client_id", clientId),
        new KeyValuePair<string, string>("client_secret", clientsecret),
        new KeyValuePair<string, string>("resource", "https://management.azure.com/")
    });
  
        content.Headers.ContentType = new MediaTypeHeaderValue("application/x-www-form-urlencoded");
  
        HttpResponseMessage response = await client.PostAsync(
            requestUri: new Uri("https://login.microsoftonline.com/16b3c013-d300-468d-ac64-7eda0820b6d3/oauth2/token"),
            content: content);
  
        OAuth2? oauth2 = JsonSerializer.Deserialize<OAuth2>(response.Content.ReadAsStringAsync().Result);
  
        token = oauth2.access_token;
    }

Logic Explained:

foreach (... … will iterate through Azure Fluent-derived list of Resource Groups
The remainder, from using (HttpClient... to token = oauth2.access_token;, makes an API call to get an access token for the Cost Management call

Note: Tokens expire after an hour; placement of the API call at this level should provide for reasonably frequent token refresh

Step 9: “Function1.cs” Logic, Iterate Dates and Request Cost Management Data

Continue on the “Function1.cs” tab

Add the following code to the method:

for (var date = DateTime.Parse(startDate); date <= DateTime.Parse(endDate); date = date.AddDays(1))
{
    Console.WriteLine(Environment.NewLine + resourceGroup.Id + " >> " + date.ToString("MMMM dd yyyy"));
  
    /* ************************* Request Cost Management Data */
  
    using (HttpClient client = new HttpClient())
    {
        client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);
  
        HttpRequestMessage request = new HttpRequestMessage(
            method: new HttpMethod("POST"),
            requestUri: "https://management.azure.com/" + resourceGroup.Id + "/providers/Microsoft.CostManagement/query?api-version=2021-10-01");
  
        request.Content = new StringContent(
            content: "{'dataset':{'aggregation':{'totalCost':{'function':'Sum','name':'PreTaxCost'}},'granularity':'Daily','grouping':[{'name':'ResourceGroupName','type':'Dimension'},{'name':'ResourceType','type':'Dimension'},{'name':'ResourceId','type':'Dimension'},{'name':'ResourceLocation','type':'Dimension'},{'name':'MeterCategory','type':'Dimension'},{'name':'MeterSubCategory','type':'Dimension'},{'name':'Meter','type':'Dimension'},{'name':'ServiceName','type':'Dimension'},{'name':'PartNumber','type':'Dimension'},{'name':'PricingModel','type':'Dimension'},{'name':'ChargeType','type':'Dimension'},{'name':'ReservationName','type':'Dimension'},{'name':'Frequency','type':'Dimension'}]},'timePeriod':{'from':'" + date + "','to':'" + date + "'},'timeframe':'Custom','type':'Usage'}",
            encoding: Encoding.UTF8,
            mediaType: "application/json");
  
        HttpResponseMessage response = client.Send(request);
    }
}

Logic Explained:

foreach (... … will iterate through dates based on startDate and endDate variables
The remainder, from using (HttpClient... to HttpResponseMessage response = client.Send(request);, makes an API call to get Cost Management data

Step 10: “Function1.cs” Logic, Send to Event Hub

Continue on the “Function1.cs” tab

Add the following code to the method:

string output = response.Content.ReadAsStringAsync().GetAwaiter().GetResult();
  
theLogger.LogInformation(output);
  
await theEventHub.AddAsync(response.Content.ReadAsStringAsync().GetAwaiter().GetResult());

Function1.cs

The final end-to-end code in Function1.cs:

using Microsoft.Azure.Management.ResourceManager.Fluent;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;


namespace CostManagement
{
    public class Function1
    {
        [FunctionName("Function1")]
        public async Task Run(
            [TimerTrigger("*/1 * * * *")] TimerInfo theTimer,
            [EventHub("dest", Connection = "EventHubConnectionAppSetting")] IAsyncCollector<string> theEventHub,
            ILogger theLogger)
        {
            string clientId = "CLIENT_ID",
                clientsecret = "CLIENT_SECRET",
                tenantid = "TENANT_ID",
                startDate = "11/1/2022",
                endDate = "11/5/2022";

            string[] subscriptions = new string[] { "SUBSCRIPTION_ID1", "SUBSCRIPTION_ID2", "SUBSCRIPTION_ID3" };

            /* ************************* Iterate Subscriptions */

            foreach (string subscription in subscriptions)
            {
                var credentials = SdkContext.AzureCredentialsFactory.FromServicePrincipal(
                    clientId: clientId,
                    clientSecret: clientsecret,
                    tenantId: tenantid,
                    environment: AzureEnvironment.AzureGlobalCloud
                );

                var azure = Microsoft.Azure.Management.Fluent.Azure
                    .Authenticate(credentials)
                    .WithSubscription(subscriptionId: subscription)
                    ;

                /* ************************* Iterate Resource Groups */

                foreach (var resourceGroup in azure.ResourceGroups.List())
                {
                    /* ************************* Get Token */

                    string token;

                    using (HttpClient client = new HttpClient())
                    {
                        FormUrlEncodedContent content = new FormUrlEncodedContent(new[]{
                        new KeyValuePair<string, string>("grant_type", "client_credentials"),
                        new KeyValuePair<string, string>("client_id", clientId),
                        new KeyValuePair<string, string>("client_secret", clientsecret),
                        new KeyValuePair<string, string>("resource", "https://management.azure.com/")
                    });

                        content.Headers.ContentType = new MediaTypeHeaderValue("application/x-www-form-urlencoded");

                        HttpResponseMessage response = await client.PostAsync(
                            requestUri: new Uri("https://login.microsoftonline.com/" + tenantid + "/oauth2/token"),
                            content: content);

                        OAuth2? oauth2 = JsonSerializer.Deserialize<OAuth2>(response.Content.ReadAsStringAsync().Result);

                        token = oauth2.access_token;
                    }

                    /* ************************* Iterate Dates */

                    for (var date = DateTime.Parse(startDate); date <= DateTime.Parse(endDate); date = date.AddDays(1))
                    {
                        Console.WriteLine(Environment.NewLine + resourceGroup.Id + " >> " + date.ToString("MMMM dd yyyy"));

                        /* ************************* Request Cost Management Data */

                        using (HttpClient client = new HttpClient())
                        {
                            client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);

                            HttpRequestMessage request = new HttpRequestMessage(
                                method: new HttpMethod("POST"),
                                requestUri: "https://management.azure.com/" + resourceGroup.Id + "/providers/Microsoft.CostManagement/query?api-version=2021-10-01");

                            request.Content = new StringContent(
                                content: "{'dataset':{'aggregation':{'totalCost':{'function':'Sum','name':'PreTaxCost'}},'granularity':'Daily','grouping':[{'name':'ResourceGroupName','type':'Dimension'},{'name':'ResourceType','type':'Dimension'},{'name':'ResourceId','type':'Dimension'},{'name':'ResourceLocation','type':'Dimension'},{'name':'MeterCategory','type':'Dimension'},{'name':'MeterSubCategory','type':'Dimension'},{'name':'Meter','type':'Dimension'},{'name':'ServiceName','type':'Dimension'},{'name':'PartNumber','type':'Dimension'},{'name':'PricingModel','type':'Dimension'},{'name':'ChargeType','type':'Dimension'},{'name':'ReservationName','type':'Dimension'},{'name':'Frequency','type':'Dimension'}]},'timePeriod':{'from':'" + date + "','to':'" + date + "'},'timeframe':'Custom','type':'Usage'}",
                                encoding: Encoding.UTF8,
                                mediaType: "application/json");

                            HttpResponseMessage response = client.Send(request);

                            string output = response.Content.ReadAsStringAsync().GetAwaiter().GetResult();

                            theLogger.LogInformation(output);

                            await theEventHub.AddAsync(response.Content.ReadAsStringAsync().GetAwaiter().GetResult());
                        }
                    }
                }
            }
        }
        public class OAuth2
        {
            public string? access_token { get; set; }
        }
    }
}

Step 11: Update “local.settings.json”

Open “local.settings.json”

Replace default JSON with the following JSON:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName=STORAGE_ACCOUNT_NAME;AccountKey=STORAGE_ACCOUNT_KEY;EndpointSuffix=core.windows.net",
    "EventHubConnectionAppSetting": "Endpoint=sb://EVENTHUB_NAMESPACE_NAME.servicebus.windows.net/;SharedAccessKeyName=EVENTHUB_SHAREDACCESSPOLICY_NAME;SharedAccessKey=EVENTHUB_SHAREDACCESSPOLICY_KEY;EntityPath=EVENTHUB_NAME",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet"
  }
}

Note: When you publish to an Azure Function App, remember that “local.settings.json” will not be published directly… you will need to update the function app with environment settings directly.

Prepare Data Explorer

Navigate to Data Explorer Database, then Query in the Data grouping of the left-hand navigation

Run the following KQL:

.create table CostManagement (
    PreTaxCost: decimal
    , UsageDate: int
    , ResourceGroupName: string
    , ResourceType: string
    , ResourceId: string
    , ResourceLocation: string
    , MeterCategory: string
    , MeterSubCategory: string
    , Meter: string
    , ServiceName: string
    , PartNumber: string
    , PricingModel: string
    , ChargeType: string
    , ReservationName: string
    , Frequency: string
    , Currency: string
    )

Reference

https://learn.microsoft.com/en-us/azure/cost-management-billing/automate/usage-details-best-practices

This site is open source. Improve this page.