Welcome!

Blog Feed Post

Making sense of your Azure billing and Azure EA usage reporting

I’ve written in the past about how Stackify was “born in Azure” and we’ve never used any other hosting solution. In this post, I want to share how we make sense of our Azure EA (Enterprise Agreement) bill and usage. Azure billing can be quite a mess to figure out.

Azure Billing Can Get Expensive and Out of Control Fast

When you get started, you’re working in small doses, spending a few hundred dollars per month on one or two resources…Fast forward 5 years, and you’re spending tens of thousands of dollars across many resources.

We are always looking at ways to cut costs, and the increased volume of usage and complexity makes it difficult to know exactly where you are spending money. We take advantage of having an Enterprise Agreement in place, but the reporting we get out the other side has two levels of granularity: Sum Total or Thousands of Lines of Detail.

Analyzing Our Actual Azure EA Usage

Recently, we started using a great free tool: azure-costs.com. It connects to your account, pulls in your usage, and pulls in your EA price sheet, and shows you how much you are spending. You can break it down by region, subscription, service type, etc. It’s pretty great…Until you hit the scenario we just did.

Our Enterprise Agreement is up for renewal and we’d like to negotiate additional price breaks on our heavily used services. “Easy,” I say and pull up Azure-Costs and sort by the most expensive component.

There it is: Locally Redundant Storage is my most expensive service, and I have a number of resources using it. So, I email my sales rep and say “Give me the biggest discount you can on locally redundant storage!” But it’s not so easy.

Figuring Out Azure Part Number SKUs

In order to do this, we need to be able to identify the specific product part numbers that we want to negotiate. And the price sheet has 8619 unique part numbers on it. Azure-Costs doesn’t show me the part number. (For that matter, it doesn’t even show region, which would make it somewhat easier to find.)

But surely it has to be easy to find your SKU, right? Not so fast. Let’s look for locally redundant storage on the price sheet. That’s 291 SKUs. For one service type.

Same thing applies with one of our next largest categories. Azure Cloud Services. That’s 1810 individual SKUs. Clearly, I need a better way to analyze our usage.

Remember earlier, I mentioned that the EA billing portal gives two levels of detail. It’s time to look at the big hairy data set, which fortunately is available in CSV. Pulling only 3 months of data yields over 150,000 rows, and fortunately only 145 distinct “MeterIDs” in the data set.

A little quick aggregating and sorting and we should be done, right? Well, once again, not so fast. The MeterID is a GUID value type. The price sheet has part numbers such as: “N9H-00074.”

Correlating Azure SKUs to Azure Usage

So, how do you correlate your actual usage to the part numbers you have pricing for? According to this twitter conversation I had with Azure Support, I should be using a preview API to dig out the data.

Yes, that’s right. They want me to write code… to figure out my bill.

Show of hands: who has an accounting department that knows how to use an API?

No one? Thought so.

I’m not having it, and neither should you, because there is an easier way.

Downloading Your Azure EA Usage and Price Sheet

You just need:

  1. The CSV export of your usage, available in the EA portal under Reports -> Download Usage and
  2. The CSV export of your price sheet, available in the EA portal (it’s the “Price Sheet” link right next to the usage).

Step 1: Import the Data into SQL

Import into SQL, SQL Express, or heck…. Even Microsoft Access. It’s just two tables. I pull them into two tables called ‘DetailedUsage’ and ‘PriceList’.

(Pro Tip: Remember to remove spaces from the column names. I missed a couple.)

Step 2: Query Your Azure Usage Data

If you’re used to writing SQL, you’re not going to like this part because it’s ugly. But you want to join Product to Service on a varchar field.

Want to know your spend per part number?

SELECT
  pl.[part number],
  du.product,
  SUM(du.extendedcost) AS 'Cost'
FROM detailedusage du
LEFT JOIN pricelist pl
  ON du.product = pl.[service]
GROUP BY pl.[part number],
         du.product
ORDER BY SUM(du.extendedcost) DESC

Want to know your spend per resource, with part number?

SELECT
  pl.[part number],
  du.product,
  du.instanceid,
  SUM(du.extendedcost) AS 'Cost'
FROM detailedusage du
LEFT JOIN pricelist pl
  ON du.product = pl.[service]
GROUP BY pl.[part number],
         du.product,
         du.instanceid
ORDER BY SUM(du.extendedcost) DESC

Need more detail?

SELECT
  du.product,
  pl.[service],
  du.metercategory,
  du.metersubcategory,
  du.metername,
  du.meterregion,
  du.meterid,
  pl.[part number],
  pl.[unit price],
  pl.[unit of measure],
  du.resourcerate,
  SUM(du.extendedcost) AS 'Cost'
FROM detailedusage du
LEFT JOIN pricelist pl
  ON du.product = pl.[service]
--where metersubcategory='Locally Redundant' and metercategory = 'Storage'
GROUP BY du.product,
         pl.[service],
         du.metercategory,
         du.metersubcategory,
         du.metername,
         du.meterregion,
         du.meterid,
         pl.[part number],
         pl.[unit price],
         pl.[unit of measure],
         du.resourcerate
ORDER BY SUM(du.extendedcost) DESC

You can learn some interesting things as well. In my original example, I was trying to find my spend on locally redundant storage. Azure-costs showed it was my most expensive category, and inside of there, my most expensive resource was named ‘stackifymc2es.’ I want to see more detail on it:

SELECT
  pl.[part number],
  du.product,
  du.instanceid,
  SUM(du.extendedcost) AS 'Cost'
FROM detailedusage du
LEFT JOIN pricelist pl
  ON du.product = pl.[service]
WHERE instanceid = 'stackifymc2es'
GROUP BY pl.[part number],
         du.product,
         du.instanceid
ORDER BY SUM(du.extendedcost) DESC

And here are all of the part numbers that specific resource has used:

The reason I find this interesting is that this particular resource has used a few different levels of Premium Storage, as we tried to find the optimal configuration. So, on the surface, azure-costs.com showed that Page Blob/P20 on this instance was my biggest spend – but what if I had just recently upgraded it to use Page Blob / P30? For forecasting purposes, it’s good to see this bigger picture.

My Summary on How to Get a Summary of Your Azure EA Billing

There are a few different ways to accomplish this sort of analysis, but they are either:

  • Too much work (API)
  • Don’t give the same flexible reporting (Free tools such as azure-costs)
  • Paid options on similar tools (would you pay your credit card company to give you details on your bill?)

This author’s opinion is that you shouldn’t have to work too hard or pay to analyze your usage. Once you’ve pulled this data into two very simple tables, querying the data out is easy. You can even dump the query results into Excel and give your boss a pretty graph.                 </div>
      
                                  <p class=Read the original blog entry...

More Stories By Stackify Blog

Stackify offers the only developers-friendly solution that fully integrates error and log management with application performance monitoring and management. Allowing you to easily isolate issues, identify what needs to be fixed quicker and focus your efforts – Support less, Code more. Stackify provides software developers, operations and support managers with an innovative cloud based solution that gives them DevOps insight and allows them to monitor, detect and resolve application issues before they affect the business to ensure a better end user experience. Start your free trial now stackify.com

Latest Stories
Modern software design has fundamentally changed how we manage applications, causing many to turn to containers as the new virtual machine for resource management. As container adoption grows beyond stateless applications to stateful workloads, the need for persistent storage is foundational - something customers routinely cite as a top pain point. In his session at @DevOpsSummit at 21st Cloud Expo, Bill Borsari, Head of Systems Engineering at Datera, explored how organizations can reap the bene...
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, led attendees through the exciting evolution of the cloud. He looked at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering m...
Kubernetes is an open source system for automating deployment, scaling, and management of containerized applications. Kubernetes was originally built by Google, leveraging years of experience with managing container workloads, and is now a Cloud Native Compute Foundation (CNCF) project. Kubernetes has been widely adopted by the community, supported on all major public and private cloud providers, and is gaining rapid adoption in enterprises. However, Kubernetes may seem intimidating and complex ...
In his session at 21st Cloud Expo, Michael Burley, a Senior Business Development Executive in IT Services at NetApp, described how NetApp designed a three-year program of work to migrate 25PB of a major telco's enterprise data to a new STaaS platform, and then secured a long-term contract to manage and operate the platform. This significant program blended the best of NetApp’s solutions and services capabilities to enable this telco’s successful adoption of private cloud storage and launching ...
In a recent survey, Sumo Logic surveyed 1,500 customers who employ cloud services such as Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform (GCP). According to the survey, a quarter of the respondents have already deployed Docker containers and nearly as many (23 percent) are employing the AWS Lambda serverless computing framework. It’s clear: serverless is here to stay. The adoption does come with some needed changes, within both application development and operations. Tha...
In his general session at 21st Cloud Expo, Greg Dumas, Calligo’s Vice President and G.M. of US operations, discussed the new Global Data Protection Regulation and how Calligo can help business stay compliant in digitally globalized world. Greg Dumas is Calligo's Vice President and G.M. of US operations. Calligo is an established service provider that provides an innovative platform for trusted cloud solutions. Calligo’s customers are typically most concerned about GDPR compliance, application p...
Digital transformation is about embracing digital technologies into a company's culture to better connect with its customers, automate processes, create better tools, enter new markets, etc. Such a transformation requires continuous orchestration across teams and an environment based on open collaboration and daily experiments. In his session at 21st Cloud Expo, Alex Casalboni, Technical (Cloud) Evangelist at Cloud Academy, explored and discussed the most urgent unsolved challenges to achieve f...
You know you need the cloud, but you’re hesitant to simply dump everything at Amazon since you know that not all workloads are suitable for cloud. You know that you want the kind of ease of use and scalability that you get with public cloud, but your applications are architected in a way that makes the public cloud a non-starter. You’re looking at private cloud solutions based on hyperconverged infrastructure, but you’re concerned with the limits inherent in those technologies.
With tough new regulations coming to Europe on data privacy in May 2018, Calligo will explain why in reality the effect is global and transforms how you consider critical data. EU GDPR fundamentally rewrites the rules for cloud, Big Data and IoT. In his session at 21st Cloud Expo, Adam Ryan, Vice President and General Manager EMEA at Calligo, examined the regulations and provided insight on how it affects technology, challenges the established rules and will usher in new levels of diligence arou...
Smart cities have the potential to change our lives at so many levels for citizens: less pollution, reduced parking obstacles, better health, education and more energy savings. Real-time data streaming and the Internet of Things (IoT) possess the power to turn this vision into a reality. However, most organizations today are building their data infrastructure to focus solely on addressing immediate business needs vs. a platform capable of quickly adapting emerging technologies to address future ...
Most technology leaders, contemporary and from the hardware era, are reshaping their businesses to do software. They hope to capture value from emerging technologies such as IoT, SDN, and AI. Ultimately, irrespective of the vertical, it is about deriving value from independent software applications participating in an ecosystem as one comprehensive solution. In his session at @ThingsExpo, Kausik Sridhar, founder and CTO of Pulzze Systems, discussed how given the magnitude of today's application ...
The dynamic nature of the cloud means that change is a constant when it comes to modern cloud-based infrastructure. Delivering modern applications to end users, therefore, is a constantly shifting challenge. Delivery automation helps IT Ops teams ensure that apps are providing an optimal end user experience over hybrid-cloud and multi-cloud environments, no matter what the current state of the infrastructure is. To employ a delivery automation strategy that reflects your business rules, making r...
As you move to the cloud, your network should be efficient, secure, and easy to manage. An enterprise adopting a hybrid or public cloud needs systems and tools that provide: Agility: ability to deliver applications and services faster, even in complex hybrid environments Easier manageability: enable reliable connectivity with complete oversight as the data center network evolves Greater efficiency: eliminate wasted effort while reducing errors and optimize asset utilization Security: imple...
The 22nd International Cloud Expo | 1st DXWorld Expo has announced that its Call for Papers is open. Cloud Expo | DXWorld Expo, to be held June 5-7, 2018, at the Javits Center in New York, NY, brings together Cloud Computing, Digital Transformation, Big Data, Internet of Things, DevOps, Machine Learning and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding busin...
Blockchain is a shared, secure record of exchange that establishes trust, accountability and transparency across business networks. Supported by the Linux Foundation's open source, open-standards based Hyperledger Project, Blockchain has the potential to improve regulatory compliance, reduce cost as well as advance trade. Are you curious about how Blockchain is built for business? In her session at 21st Cloud Expo, René Bostic, Technical VP of the IBM Cloud Unit in North America, discussed the b...