1

I am building an Azure Chargeback solution and for that I am pulling the Azure Usage data from Azure Billing REST APIs for multiple subscriptions and different dates. I need to store this into custom MS SQL database as per customer’s requirements. I get various usage records from Azure.

  • Problem: From these Usage records, I am not able to find any combination of the columns in the data I receive which will give me a Unique Key to identify a Usage record for a particular subscription and for a specific date. Only column I see as different is Quantity but even that can be duplicated. E.g. If there are 2 VMs of type A1 with no data or applications on them, in the same cloud service, then they will have exact quantity of usage. I do not get the exact name of the VM or any other resource via the Usage APIs.
  • One Custom Solution (Ineffective): I can append a counter or unique ID to the usage records but if I fetch the data next time the order may shuffle or new data may be introduced thereby affecting the logic for uniqueness. Any logic I build to checking if any data is missing in DB will result in bugs if there is any alteration in the order the usage records are returned (for a specific subscription for a specific date).

I am sure that Microsoft stores this data in some database. I can’t find the unique id to identify a usage record from many records returned by the Billing API. Maybe I am missing something here.

I will appreciate any help or any pointers on this.

Aman Sharma
  • 1,930
  • 1
  • 17
  • 31
  • Are you using C# Azure SDK or PowerShell? – juvchan Feb 24 '16 at 23:27
  • @juvchan I am using C# Azure SDK. More Specifically I am using the Usage sample which is official and available here: [Billing Usage Console App](https://github.com/Azure-Samples/billing-dotnet-usage-api). But I believe it doesn't matter. The JSON data remains the same which I need to save into the MS SQL Server. – Aman Sharma Feb 25 '16 at 02:12
  • Can you explain if you want to store raw usage data or aggregate the data in some shape or form and then store it? If it is aggregated data, can you describe how do you wish to aggregate it? – Gaurav Mantri Feb 25 '16 at 03:05
  • @GauravMantri I want to store the raw data. I will additionally also store the related rate data. – Aman Sharma Feb 26 '16 at 03:17

1 Answers1

0

When you call the Usage API set the ShowDetails parameter to true: &showDetails=true

MSDN Doc

This will populate the instance data in the returned JSON with the unique URI for the resource which includes the name for example:

Website:

"instanceData": "{\"Microsoft.Resources\":{\"resourceUri\":\"/subscriptions/xxx-xxxx/resourceGroups/mygoup/providers/Microsoft.Web/sites/WebsiteName\",\"...

Virtual Machine:

"instanceData": "{\"Microsoft.Resources\":{\"resourceUri\":\"/subscriptions/xxx-xxxx/resourceGroups/TESTINGBillGroup/providers/Microsoft.Compute/virtualMachines/TestingBillVM\",\...

If ShowDetails is false all your resources will be aggregated on the server side based on the resource type, all your websites will show as one entry.

The resource URI, date range and meterid will form a unique key as far as I know.

NOTE: If you are using the legacy API your VMs will be aggregated under the cloud service hosting them.

Francois
  • 873
  • 1
  • 8
  • 17
  • I did use the showDetails=true switch. If you have 2 VMs in a Cloud Service then it will not give you exact name of the VM. It will only give you with cloud service name. I submit the query after checking the actual JSON data and comparing two records for 2 VMs in 1 cloud service. Please try again for multiple VMs in classic portal within same cloud service and you will see the same observation as mine. I know that VMs and Websites data is shown differently. Today there is no way you can get exact name of the VM from Usage APIs. – Aman Sharma Feb 26 '16 at 03:20
  • @AmanSharma In my usage data I get a unique URI for each VM but I'm using ARM. I ran a test to create VMs on the old portal and for the legacy VMs the usage is aggregated under the cloud service hosting them I don't get unique names for the VMs either. – Francois Feb 28 '16 at 11:09