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
SYS-CON Events announced today that Linux Academy, the foremost online Linux and cloud training platform and community, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Linux Academy was founded on the belief that providing high-quality, in-depth training should be available at an affordable price. Industry leaders in quality training, provided services, and student certification passes, its goal is to c...
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm. In his Day 3 Keynote at 20th Cloud Expo, Chris Brown, a Solutions Marketing Manager at Nutanix, will explore t...
Providing the needed data for application development and testing is a huge headache for most organizations. The problems are often the same across companies - speed, quality, cost, and control. Provisioning data can take days or weeks, every time a refresh is required. Using dummy data leads to quality problems. Creating physical copies of large data sets and sending them to distributed teams of developers eats up expensive storage and bandwidth resources. And, all of these copies proliferating...
SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
SYS-CON Events announced today that StorageCraft Technology Corp, a global leader in backup and disaster, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. The StorageCraft family of companies, founded in 2003, provides award-winning backup, disaster recovery, system migration and data protection solutions for servers, desktops and laptops in addition to powerful data analytics.
In his session at @ThingsExpo, Eric Lachapelle, CEO of the Professional Evaluation and Certification Board (PECB), will provide an overview of various initiatives to certifiy the security of connected devices and future trends in ensuring public trust of IoT. Eric Lachapelle is the Chief Executive Officer of the Professional Evaluation and Certification Board (PECB), an international certification body. His role is to help companies and individuals to achieve professional, accredited and worldw...
SYS-CON Events announced today that Auditwerx will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Auditwerx specializes in SOC 1, SOC 2, and SOC 3 attestation services throughout the U.S. and Canada. As a division of Carr, Riggs & Ingram (CRI), one of the top 20 largest CPA firms nationally, you can expect the resources, skills, and experience of a much larger firm combined with the accessibility and attent...
SYS-CON Events announced today that Technologic Systems Inc., an embedded systems solutions company, will exhibit at SYS-CON's @ThingsExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Technologic Systems is an embedded systems company with headquarters in Fountain Hills, Arizona. They have been in business for 32 years, helping more than 8,000 OEM customers and building over a hundred COTS products that have never been discontinued. Technologic Systems’ pr...
SYS-CON Events announced today that CA Technologies has been named “Platinum Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business – from apparel to energy – is being rewritten by software. From ...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
HyperConvergence came to market with the objective of being simple, flexible and to help drive down operating expenses. It reduced the footprint by bundling the compute/storage/network into one box. This brought a new set of challenges as the HyperConverged vendors are very focused on their own proprietary building blocks. If you want to scale in a certain way, let’s say you identified a need for more storage and want to add a device that is not sold by the HyperConverged vendor, forget about it...
FinTech is the sum of financial and technology, and it’s one of the fastest growing tech industries. Total global investments in FinTech almost reached $50 billion last year, but there is still a great deal of confusion over what it is and what it means – especially as it applies to retirement. Building financial startups is not simple, but with the right team, technology and an innovative approach it can be an extremely interesting domain to disrupt. FinTech heralds a financial revolution that...
What if you could build a web application that could support true web-scale traffic without having to ever provision or manage a single server? Sounds magical, and it is! In his session at 20th Cloud Expo, Chris Munns, Senior Developer Advocate for Serverless Applications at Amazon Web Services, will show how to build a serverless website that scales automatically using services like AWS Lambda, Amazon API Gateway, and Amazon S3. We will review several frameworks that can help you build serverle...
Most companies are adopting or evaluating container technology - Docker in particular - to speed up application deployment, drive down cost, ease management and make application delivery more flexible overall. As with most new architectures, this dream takes a lot of work to become a reality. Even when you do get your application componentized enough and packaged properly, there are still challenges for DevOps teams to making the shift to continuous delivery and achieving that reduction in cost ...
SYS-CON Events announced today that Loom Systems will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Founded in 2015, Loom Systems delivers an advanced AI solution to predict and prevent problems in the digital business. Loom stands alone in the industry as an AI analysis platform requiring no prior math knowledge from operators, leveraging the existing staff to succeed in the digital era. With offices in S...