Home » Weekly Azure billing report per mail with Azure functions

Weekly Azure billing report per mail with Azure functions

I already blogged about Azure functions, the billing API and a few other things. In this blog post, I’ll combine some of my previous blog posts to build an Azure function that creates a weekly billing report of an Azure subscription. To build this solution, the following steps are required:

  1. Create an Azure function
  2. Configure the CRON schedule for the Azure function
  3. Read data from the Azure Billing API
  4. Create a HTML page with the billing data
  5. Send the report via email

To implement it, I’ll use Visual Studio 2017, C# and the AzureBillingAPI NuGet package that I created.

The final solution can be found on GitHub: https://github.com/codehollow/AzureBillingFunction

Prerequisits
If you want to develop Azure Functions with Visual Studio, you have to install the extension “Azure Functions and Web Jobs Tools”.
You also need to configure a billing account to get a client secret that will be used to read data from the billing api. Please find more information about it here: Use the Azure Billing API and calculate the costs

Creating the new Azure function

The first step is to create an Azure function project in Visual Studio.

The project is there, so we can add a new function (right click to the project – add new item):

I want to create a function that sends me the current costs on a weekly base. So I’ll add a new time triggered C# function with the schedule: 0 0 9 * * 1 (every Monday at 09:00:00 UTC). You can find more about the CRON expressions here: Azure Functions – Time Trigger (CRON) Cheat Sheet

The base is set up and we can try to start our Azure function to see if it works, but at first, let’s have a look at the run.csx. The default code only logs a message. The question is – where does it log?
The logs for Azure functions are stored in a storage account which means that we have to configure the storage account at first. If you immediately start to debug the Azure function, then you will receive the following error:
Missing value for AzureWebJobsStorage in local.settings.json. This is required for all triggers other than HTTP. You can run ‘func azure functionapp fetch-app-settings ‘ or specify a connection string in local.settings.json.

So let’s configure the storage account, so that the function works:

Configure the storage account

The connection string for the storage account must be added to the application settings of the function (local.settings.json). If you don’t have a storage account, then you need to create one at first. If there is already one, just navigate to the storage account in the Azure portal, select Access Keys and copy the connection string of one of the keys to the appsettings.json:

Run the function

After configuring the storage account, we can now run the application and will get the following result:

As we can see, the schedule is correct, but the function does not run. That’s because the host is working the same way as the function will, so it really runs only once a week.
There are different ways how to fix that for development/testing purposes:
– Change the schedule to: */10 * * * * * (every 10 seconds)
– Call the Azure function manually (via HttpTrigger)

I prefer to use an HTTP trigger for many reasons. One is that I really can manually trigger the job, which is much better than if it is executed every 10 seconds. The every 10 seconds trigger results in waiting at the beginning and probably multiple executions during the debug session. So let’s configure an http trigger.

Configure HTTP Trigger

It’s possible to configure 2 triggers for one function, but you can only have on Run method per function. So it doesn’t make sense to configure 2 triggers. Therefore I’ll create another function with an HTTP trigger:

Starting the functions now results in 2 functions. One is executed once a week, the other one is executed when I call the url (http trigger) that is shown in the console of the function (in my case: http://localhost:7071/api/AzureBillingFunctionHttp):

Getting data from the Azure billing API

Time to code a bit. Reading the data from the Azure billing API can be done with a NuGet package that I created (see: CodeHollow.AzureBillingApi). The NuGet package can be added in Visual Studio the same way as for other C# projects. If you add one of the latest versions, you will probably get an error. The reason is, that azure functions use a different newtonsoft json version than the one in the billing api. So please add the latest version of newtonsoft.json at first and then add the CodeHollow.AzureBillingApi.
You can also add the Sendgrid NuGet package, because I’ll use sendgrid to send out the emails:

If you develop the Azure function online (without Visual Studio), then you can add the NuGet packages in the project.json file (which you probably must create manually):

{
  "frameworks": {
    "net46":{
      "dependencies": {
        "CodeHollow.AzureBillingApi": "1.1.0.6",
        "Sendgrid": "9.9.0"
      }
    }
  }
}

HTML Template

When the NuGet packages are loaded, we can implement reading the costs. The costs will be shown in an html report, so I’ll at first add a MailReport.html file to my Azure function. This file will be the template for the report:

<html>
<head>
    <title>Weekly Billing Report</title>
</head>
<body>
    <style>
        table {
            border-collapse: collapse;
        }

        table, th, td {
            border: 1px solid gray;
            padding: 5px;
        } 
    </style>
    <h1>Weekly Azure Billing Report: {date}</h1>
    <h2>Costs by Resource Group</h2>
    <table id="costsPerResourceGroup">
        <thead><tr><th>Resource Group</th><th>Costs</th></tr></thead>
        <tbody>
            {costsPerResourceGroup}
        </tbody>
    </table>

    <h2>Cost Details</h2>
    <table id="costsDetails">
        <thead><tr><th>Resource Group</th><th>Resource</th><th>Meter</th><th>Usage</th><th>Costs</th></tr></thead>
        <tbody>
            {costsDetails}
        </tbody>
    </table>
</body>
</html>

C# function code

The report template will be read in the c# function and filled with the data from the billing api. The code to read the data from the billing api

using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using System;
using CodeHollow.AzureBillingApi;
using SendGrid;
using SendGrid.Helpers.Mail;

namespace AzureBillingFunction
{
    /// <summary>
    /// Time triggered function that creates a weekly billing report and sends it via mail
    /// </summary>
    public static class AzureBillingFunction
    {
        internal const string CURRENCY_CHAR = "&euro;";
        const string TRIGGER = "0 0 9 * * 1";

        const string FROM = "[email protected]";
        const string FROM_NAME = "Azure Billing API";
        const string TO = "[MAIL]";
        const string TO_NAME = "[NAME]";
        const string APIKEY = "[SENDGRIDAPIKEY]";

        [FunctionName("AzureBillingFunction")]
        public static void Run([TimerTrigger(TRIGGER)]TimerInfo myTimer, TraceWriter log, ExecutionContext context)
        {
            log.Info($"C# Timer trigger function executed at: {DateTime.Now}");
            try
            {
                Client c = new Client("mytenant.onmicrosoft.com", "[CLIENTID]",
                    "[CLIENTSECRET]", "[SUBSCRIPTIONID]", "http://[REDIRECTURL]");

                var path = System.IO.Path.Combine(context.FunctionAppDirectory, "MailReport.html");
                string html = BillingReportGenerator.GetHtmlReport(c, path);

                SendMail(TO, TO_NAME, html);
            }
            catch(Exception ex)
            {
                log.Error(ex.Message, ex);
            }
        }

        private static void SendMail(string toMail, string toName, string html)
        {
            var client = new SendGridClient(APIKEY);
            var from = new EmailAddress(FROM, FROM_NAME);
            var to = new EmailAddress(toMail, toName);

            var msg = MailHelper.CreateSingleEmail(from, to, "Weekly Azure Billing Report", "", html);
            client.SendEmailAsync(msg).Wait();
        }
    }
}
using CodeHollow.AzureBillingApi;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AzureBillingFunction
{
    public static class BillingReportGenerator
    {
        public static string GetHtmlReport(Client c, string htmlFile)
        {
            var startdate = DateTime.Now.AddDays(-7); // set start date to last monday
            var enddate = DateTime.Now.AddDays(-1); // set end date to last day which is sunday

            var allcosts = c.GetResourceCosts("MS-AZR-0003P", "EUR", "en-US", "AT", startdate, enddate,
            CodeHollow.AzureBillingApi.Usage.AggregationGranularity.Daily, true);

            var costsByResourceGroup = allcosts.GetCostsByResourceGroup();


            string cbr = CostsByResourceGroup(costsByResourceGroup);
            string costDetails = CostsDetails(costsByResourceGroup);

            string html = System.IO.File.ReadAllText(htmlFile);
            html = html.Replace("{costsPerResourceGroup}", cbr);
            html = html.Replace("{costsDetails}", costDetails);
            html = html.Replace("{date}", startdate.ToShortDateString() + " - " + enddate.ToShortDateString());

            return html.ToString();
        }

        private static string CostsDetails(Dictionary<string, IEnumerable<ResourceCosts>> costsByResourceGroup)
        {
            var costs = from cbrg in costsByResourceGroup
                        from costsByResourceName in cbrg.Value.GetCostsByResourceName()
                        from costsByMeterName in costsByResourceName.Value.GetCostsByMeterName()
                        select new
                        {
                            ResourceGroup = cbrg.Key,
                            Resource = costsByResourceName.Key,
                            MeterName = costsByMeterName.Key,
                            Costs = costsByMeterName.Value
                        };

            var data = costs.Select(x => $"<tr><td>{x.ResourceGroup}</td><td>{x.Resource}</td><td>{x.MeterName}</td><td>{x.Costs.GetTotalUsage().ToHtml()}</td><td>{x.Costs.GetTotalCosts().ToHtml(true)}</td></tr>");
            return string.Concat(data);
        }

        private static string CostsByResourceGroup(Dictionary<string, IEnumerable<ResourceCosts>> costsByResourceGroup)
        {
            var data = costsByResourceGroup.Select(x => $"<tr><td>{x.Key}</td><td>{x.Value.GetTotalCosts().ToHtml(true)}</td></tr>");
            return string.Concat(data);
        }
    }
}

namespace AzureBillingFunction
{
    public static class Extensions
    {
        public static string ToHtml(this double value, bool currency = false)
        {
            if (currency)
                return value.ToString("0.00") + " " + AzureBillingFunction.CURRENCY_CHAR;
            return value.ToString("0.################");
        }
    }
}

Result

The result is a weekly billing report that is send to my mail. It looks like:

The final step is to deploy the function to azure and to wait till Monday, 09:00 to receive the billing report.

Portal/Website?

Another useful solution could be a website or portal that shows you your costs. I added another function to the solution (see GitHub) that creates the same report, but it has some additional functions as it uses bootstrap and datatables.
A website or portal would also be nice. Maybe I’ll find some time in the future to implement it.

Additional information

GitHub Project: https://github.com/codehollow/AzureBillingFunction
NuGet Package: https://www.nuget.org/packages/CodeHollow.AzureBillingApi/

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *