How to call multiple subscriptions in a single API

Question

Hi Team,

I'm looking to create a costing dashboard in Power Bi for an AVD environment (Azure). So, I was asked to use an API method to call the cost related information of that particular Azure account. But that Azure account has multiple subscriptions which are related to costing. For the API I have, I'm able to call one Host pool data at once. So, it'll be a huge burden to call all the Host pools and appending them.

So, I'm looking for if there is any API, where I could all the subscriptions in a single API which would get all the data of that Azure account in it? or any other API which would all the data at one or two go.

Regards,

Haveesh

Answer 1

Hello Perla Haveesh,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Problem

Based on your explanation and information provided, I understand that you need to aggregate cost-related information across all these subscriptions but the current API available to you only allows for fetching data for one host pool at a time, making it difficult to gather comprehensive data across all subscriptions and host pools. You seek an efficient API solution that can fetch data for all subscriptions in one or two calls.

Solution

I will give you here best practice advice and solution for API usage and consumption with two options to aggregate cost data from multiple subscriptions. Most of all, Azure offers several APIs and tools that can help streamline this process.

Best practices are to have two APIs with different data, then combine them for your Power BI.

How to do this:

Firstly

Use Azure Resource Graph API.

While it doesn't provide cost data directly, it can be combined with cost management data to enhance your dashboard.

• By having the necessary permissions to use the Azure Resource Graph API.
• Use Azure AD to authenticate and obtain an access token.
• Use the Resource Graph API to query resources across all subscriptions.

This is an example query to get all resources using ARG API:

POST https://management.azure.com/providers/Microsoft.ResourceGraph/resources?api-version=2020-04-01
Authorization: Bearer {access-token}
Content-Type: application/json
{
  "subscriptions": [
    "{subscription-id-1}",
    "{subscription-id-2}",
    "{subscription-id-3}"
  ],
  "query": "Resources | project name, type, location"
}

Secondly

Use Azure Cost Management API.

The Azure Cost Management + Billing APIs allow you to retrieve cost and usage data for multiple subscriptions. The UsageDetails and AggregatedCost endpoints can be particularly useful.

Likewise,

• By having the appropriate permissions and register the Cost Management API in your Azure Active Directory.
• Use Azure AD to authenticate and obtain an access token.
• Use the UsageDetails and AggregatedCost endpoints to get detailed cost information. These endpoints support querying across multiple subscriptions.

Also, this is example of how you might use the UsageDetails API:

GET https://management.azure.com/providers/Microsoft.Billing/billingAccounts/{billingAccountId}/billingProfiles/{billingProfileId}/providers/Microsoft.CostManagement/usageDetails?api-version=2021-10-01
Authorization: Bearer {access-token}

Finally

Combining Data for Power BI.

After the above, to aggregate data from multiple subscriptions into a single dashboard you will need to combine the data for Power BI.

• By using scripts such as PowerShell, Python or tools (e.g., Logic Apps, Azure Functions) to call the APIs and gather the data.
• Store the retrieved data in a data store such as Azure SQL Database, Azure Data Lake. Use Azure Data Factory or Power BI dataflows to transform and combine the data.
• Connect Power BI to your data store and create visualizations to display the aggregated cost data.

Like two above, I am familiar with Python here and this is an example script to get cost data from the Azure Cost Management API:

import requests
# Replace with your own values
tenant_id = 'your-tenant-id'
client_id = 'your-client-id'
client_secret = 'your-client-secret'
subscription_id = 'your-subscription-id'
# Get access token
auth_url = f'https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token'
auth_data = {
    'grant_type': 'client_credentials',
    'client_id': client_id,
    'client_secret': client_secret,
    'scope': 'https://management.azure.com/.default'
}
response = requests.post(auth_url, data=auth_data)
access_token = response.json().get('access_token')
# Get cost data
cost_url = f'https://management.azure.com/subscriptions/{subscription_id}/providers/Microsoft.CostManagement/query?api-version=2021-10-01'
headers = {
    'Authorization': f'Bearer {access_token}',
    'Content-Type': 'application/json'
}
query = {
    "type": "Usage",
    "timeframe": "MonthToDate",
    "dataset": {
        "granularity": "Daily",
        "aggregation": {
            "totalCost": {
                "name": "Cost",
                "function": "Sum"
            }
        }
    }
}
response = requests.post(cost_url, headers=headers, json=query)
cost_data = response.json()
print(cost_data)

As I have said above.

"Best practices are to use two APIs with different data, then combine them for your Power BI."

To elaborate:

1. Azure Cost Management API - This API provides detailed cost data across multiple subscriptions.
2. Azure Resource Graph API - This API allows querying resources across multiple subscriptions.

By using these two APIs, you can gather comprehensive data needed for your Power BI dashboard and then combine the data for visualization as I have given examples in the solution.

Reference

Source: Azure Cost Management + Billing REST APIs. Accessed, 6/13/2024.

Source: Azure Resource Graph REST API. Accessed, 6/13/2024.

Source: Resource Graph query examples. Accessed, 6/13/2024.

Source: Transform data using Data Flows. Accessed, 6/13/2024.

Source: Connecting Power BI to Azure SQL Database. Accessed, 6/13/2024.

Also, utilize the additional resources available by the right side of this page for more reading.

Accept Answer

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.

Best Regards,

Sina Salam