5 min read

Working with Microsoft.CostManagement queries (2/2): Partner Earned Credits

Working with Microsoft.CostManagement queries (2/2): Partner Earned Credits

In my previous post we looked at querying the Microsoft.CostManagement/query API. The examples I showed were posted using Postman. But wouldn't it be useful to automate some stuff and get the results by running an Azure Function? That's what I thought.

The Use Case

In my use case I needed to automate the detection of customers that contained resources where Partner Earned Credits weren't applied. Getting the actual resource in question is a completely different story and requires you to query the usageDetails API which is a part of the Microsoft.Consumption resource provider. It provides a lot of information and provide you with the ability to filter out whether the item in question is a market place item and how many Partner Earned Credits you are actually receiving. The downside of this API is that it generates a lot of data and querying it over all your customers is going to take a couple of hours (in case of a reasonbly sized CSP).

I chose a different approach. As have we seen in my previous post, Cost Management APIs are relatively fast for the amount of data we are requesting. I only want to "deep dive" into the customers that contain resources where no Partner Earned Credits are generated. This resulted in the queries as provided here: Working with Microsoft.CostManagement queries (1/2) (wesleyhaakman.org).

The Solution

Too long didn't read, code: whaakman/azfunction-app-get-missingpec: Retrieve customer and SubscriptionIds that are missing Partner Earned Credits (github.com).

We do have some requirements before the Azure Function can run:

Alright, let's get started. I slightly altered the query from the previous post and had it include the Customer Tenant Name and the subscription ID

{
    "type": "ActualCost",
    "dataSet": {
        "granularity": "monthly",
        "aggregation": {
            "totalCost": {
                "name": "Cost",
                "function": "Sum"
            },
            "totalCostUSD": {
                "name": "CostUSD",
                "function": "Sum"
            }
        },
        "sorting": [
            {
                "direction": "ascending",
                "name": "UsageDate"
            }
        ],
        "grouping": [
            {
                "type": "Dimension",
                "name": "CustomerTenantId"
            },
            {
                "type": "Dimension",
                "name": "CustomerName"
            }
        ],
        "filter": {
            "Dimensions": {
                "Name": "PartnerEarnedCreditApplied",
                "Operator": "In",
                "Values": [
                    "false"
                ]
            }
        }
    },
    "timeframe": "MonthToDate"
    }
}

The interested part is in the "Dimensions" where we are looking for Partner Earned Credits to be "false".

This should give us a list of all customers, the missing Partner earned Credits and how much that actually was. I'm not particularly interested in the "how much" part but more in which Subscription IDs and which customers.

I decided to stuff everything into an Azure Function and return a list of the customers and the subscription Ids. This will provide me with the output I need for queries against other APIs, for instance the usageDetails API :)

Let's not go through every line of code step by step but let me explain the important bits. You can download the complete code here. It's not the prettiest, but it works. If you do run into stuff and need help, please reach out.

The important part of the code is performing the POST to the query endpoint and dealing with the response.

You can deal with this multiple in ways. You can create a class that you can use to deserialize the response (for instance by using https://json2csharp.com/) or, you can select the exact results you want if know the exact position in the array.

I really had no need for a whole class and processing the entire data set. Instead I took the quick and dirty approach. For the query we are using, the result is always formatted the same:

What we need is the 3rd and 4th items in the array. Using the query, we already filtered out the customer that did not have any issues with Partner Earned Credits, just the ones we need. That means we can focus on processing the result and returning it.

Because I wasn't looking to deserialize everything into an object I could get away with using a dynamic json, parse the result from the query and grab item number 3 and 4.

Contents from APICall.cs:

string token = await GetToken();
            client.DefaultRequestHeaders.Accept.Clear();
            client.DefaultRequestHeaders.Accept.Add(
            new MediaTypeWithQualityHeaderValue("*/*"));
            client.DefaultRequestHeaders.Remove("Authorization");
            client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token);

            string URI = $"https://management.azure.com/providers/Microsoft.Billing/billingAccounts/{billingAccountName}/providers/Microsoft.CostManagement/query?api-version=2019-11-01";          
           
            HttpRequestMessage request = new HttpRequestMessage(HttpMethod.Post, URI);
            request.Content = new StringContent(jsonBody,
                                    Encoding.UTF8, 
                                    "application/json");

            HttpResponseMessage result = await client.SendAsync(request);
            string output = await result.Content.ReadAsStringAsync();
            dynamic json = JObject.Parse(output);

            List<Customers> customerList = new List<Customers>();
  
            foreach (dynamic item in json.properties.rows) {
                Customers customer = new Customers();
                customer.subscriptionId = item[3];
                customer.customerName = item[4];
                if (!String.IsNullOrEmpty(customer.subscriptionId))  
                    {
                        customerList.Add(customer);
                    }
                }

            return customerList;
        } 

Once I did that, I stored the customerTenantId as"customerName" and the subscription as "subscriptionId" as properties in a list of type "customer" (Customer.cs) and finally return that list. What I also noticed that if the subscription ID is empty and Partner Earned Credits report false the costs are also set to 0, so I decided to filter that out.

In the main class of our function, all we need to do is call the method, provide the billingAccountName as the parameter and return the results back to whoever executed the function.

Contents from GetCustomersMissingPEC.cs:

            string billingAccountName = config["billingAccountName"];

            var APICall = new APICall();

            var customersWithoutPec = await APICall.GetCustomersWithMissingPEC(billingAccountName);

            return new OkObjectResult(customersWithoutPec);

And that returns all the customers that are missing Partner Earned Credits. Whether it's a single dollar or thousands.

Wrap up

Having this kind of output means we can dive into the specifics like the usageDetails without having to pull the details for every customer. This saves loads of time. You can also use this for your daily reporting. After all, you want to know if you're missing out as soon as possible!

I'm currently working on automating the process from detecting that something is missing to detecting why it is missing (missing permissions, marketplace items, Partner Admin Link). When I have something I will definitely share!

As usual, if you have any questions, please reach out!