Search code examples
azurecsvazure-logic-appsazure-billing-api

Get total cost from Azure billing Invoice CSV file

  • 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!

Solution

  • 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. enter image description here

    3. Search the action "Parse CSV" in you logic app. enter image description here

    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. enter image description here Copy the secret and paste it to your logic app as "API key", it will allow you to connect Plumsail. enter image description here

    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.

    enter image description here

    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.

    enter image description here

    7. Use a "For each" loop.

    enter image description here

    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: enter image description here

    9. Here is the whole logic app for your reference: enter image description here

    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.