Get total cost from Azure billing Invoice CSV file

Question

I have added a schedule export for my Azure Resource Group Billing invoice on a monthly basis. The invoice generated will consist of the billing details of the Last Month and will store the .CSV file in my storage account as a Blob every month.
I'm using an Azure Logic app to retrieve the Invoice file and send it via mail to a group of recipients.
The invoice is a .CSV file which consists of a number of columns like "InstanceID, MeterID, UsageQuantity, ResourceLocation". But I need to get the TOTAL COST for the billing period.

Any idea how I can achieve this? Is there a specific column that I need to include in my CSV file. Or do I need to do some sort of data processing of the CSV file to get the total amount of resources consumed?

Any advise on this? Thanks!

Is it possible to get the total cost by add up the value of some columns ? — Hury Shen
Yes Hury. There's a column named Usage Quantity. Adding up that column should give the total cost. But can we achieve this step with Logic app? — SD4
In my opinion, you should do the add number operation when you get the json result from the billing rest api. Because it is easy for you to do it when the data is in json format. — Hury Shen
If you want to do the add number operation in logic app, it is not easy for us to implement it in logic app. Because in logic app we can't parse the csv data easily, we need to use some third party connector in logic app to do it and it may incur additional costs. — Hury Shen
Any idea how can I do that? Because I'm using the rest api to basically just create a schedule export for my invoice. The json result just returns the configuration parameters that I set in the body — SD4

Hury Shen Hury Shen · Accepted Answer · 2020-11-02T09:16:05

1. I created a csv file(named billing.csv) as below and upload it to blob storage.

InstanceID, MeterID, UsageQuantity, ResourceLocation, Pre tax cost
1,1,2,aa,10
2,2,3,bb,20
3,3,5,cc,30

2. In logic app, use "Get blob content" to get the csv file.

3. Search the action "Parse CSV" in you logic app.

4. The "Parse CSV" action will ask you to input "API Key", you need to go to this page first --> click "Start free trial", register an account and create a new API Key. Copy the secret and paste it to your logic app as "API key", it will allow you to connect Plumsail.

5. Then choose the blob content into the "Parse CSV" and input the headers InstanceID, MeterID, UsageQuantity, ResourceLocation, Pre tax cost. Add a new parameter "Skip first line" and set its value as Yes.

6. Initialize a variable sum and set its value as 0 in integer type. Initialize another variable tempItem and also set its value as 0.

7. Use a "For each" loop.

The Body comes from "Parse CSV" action and the expression of "value" is: add(variables('tempItem'), int(items('For_each')?['Pre tax cost']))

8. After running the logic app, we can see the sum in last loop is:

9. Here is the whole logic app for your reference:

Import:

This solution uses the third party connector "Plumsail Documents", I'm not sure if it is free. I registered account in the past, it worked without any cost. But today the api key can't continue use, I need to register another account and create another api key. So I think this third party connector need extra cost if you want to use it for a long time.

Get total cost from Azure billing Invoice CSV file

1 Answers