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
Today we can collect lots and lots of performance data. We build beautiful dashboards and even have fancy query languages to access and transform the data. Still performance data is a secret language only a couple of people understand. The more business becomes digital the more stakeholders are interested in this data including how it relates to business. Some of these people have never used a monitoring tool before. They have a question on their mind like “How is my application doing” but no id...
Join us at Cloud Expo June 6-8 to find out how to securely connect your cloud app to any cloud or on-premises data source – without complex firewall changes. More users are demanding access to on-premises data from their cloud applications. It’s no longer a “nice-to-have” but an important differentiator that drives competitive advantages. It’s the new “must have” in the hybrid era. Users want capabilities that give them a unified view of the data to get closer to customers and grow business. The...
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
It is ironic, but perhaps not unexpected, that many organizations who want the benefits of using an Agile approach to deliver software use a waterfall approach to adopting Agile practices: they form plans, they set milestones, and they measure progress by how many teams they have engaged. Old habits die hard, but like most waterfall software projects, most waterfall-style Agile adoption efforts fail to produce the results desired. The problem is that to get the results they want, they have to ch...
IoT solutions exploit operational data generated by Internet-connected smart “things” for the purpose of gaining operational insight and producing “better outcomes” (for example, create new business models, eliminate unscheduled maintenance, etc.). The explosive proliferation of IoT solutions will result in an exponential growth in the volume of IoT data, precipitating significant Information Governance issues: who owns the IoT data, what are the rights/duties of IoT solutions adopters towards t...
Wooed by the promise of faster innovation, lower TCO, and greater agility, businesses of every shape and size have embraced the cloud at every layer of the IT stack – from apps to file sharing to infrastructure. The typical organization currently uses more than a dozen sanctioned cloud apps and will shift more than half of all workloads to the cloud by 2018. Such cloud investments have delivered measurable benefits. But they’ve also resulted in some unintended side-effects: complexity and risk. ...
With the introduction of IoT and Smart Living in every aspect of our lives, one question has become relevant: What are the security implications? To answer this, first we have to look and explore the security models of the technologies that IoT is founded upon. In his session at @ThingsExpo, Nevi Kaja, a Research Engineer at Ford Motor Company, discussed some of the security challenges of the IoT infrastructure and related how these aspects impact Smart Living. The material was delivered interac...
The taxi industry never saw Uber coming. Startups are a threat to incumbents like never before, and a major enabler for startups is that they are instantly “cloud ready.” If innovation moves at the pace of IT, then your company is in trouble. Why? Because your data center will not keep up with frenetic pace AWS, Microsoft and Google are rolling out new capabilities. In his session at 20th Cloud Expo, Don Browning, VP of Cloud Architecture at Turner, posited that disruption is inevitable for comp...
In 2014, Amazon announced a new form of compute called Lambda. We didn't know it at the time, but this represented a fundamental shift in what we expect from cloud computing. Now, all of the major cloud computing vendors want to take part in this disruptive technology. In his session at 20th Cloud Expo, Doug Vanderweide, an instructor at Linux Academy, discussed why major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform are all trying to sidestep VMs and containers wit...
While DevOps most critically and famously fosters collaboration, communication, and integration through cultural change, culture is more of an output than an input. In order to actively drive cultural evolution, organizations must make substantial organizational and process changes, and adopt new technologies, to encourage a DevOps culture. Moderated by Andi Mann, panelists discussed how to balance these three pillars of DevOps, where to focus attention (and resources), where organizations might...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
No hype cycles or predictions of zillions of things here. IoT is big. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, Associate Partner at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He discussed the evaluation of communication standards and IoT messaging protocols, data analytics considerations, edge-to-cloud tec...
When growing capacity and power in the data center, the architectural trade-offs between server scale-up vs. scale-out continue to be debated. Both approaches are valid: scale-out adds multiple, smaller servers running in a distributed computing model, while scale-up adds fewer, more powerful servers that are capable of running larger workloads. It’s worth noting that there are additional, unique advantages that scale-up architectures offer. One big advantage is large memory and compute capacity...
New competitors, disruptive technologies, and growing expectations are pushing every business to both adopt and deliver new digital services. This ‘Digital Transformation’ demands rapid delivery and continuous iteration of new competitive services via multiple channels, which in turn demands new service delivery techniques – including DevOps. In this power panel at @DevOpsSummit 20th Cloud Expo, moderated by DevOps Conference Co-Chair Andi Mann, panelists examined how DevOps helps to meet the de...
Cloud applications are seeing a deluge of requests to support the exploding advanced analytics market. “Open analytics” is the emerging strategy to deliver that data through an open data access layer, in the cloud, to be directly consumed by external analytics tools and popular programming languages. An increasing number of data engineers and data scientists use a variety of platforms and advanced analytics languages such as SAS, R, Python and Java, as well as frameworks such as Hadoop and Spark...