Azure Billing using CLI

Question

Hello Team

I want to download the Azure Billing by passing the duration and other details in the parameter and in the form of a file (Excel, CSV) and it should contain the data ( SubscriptionName, SubscriptionId, Cost , CostUSD, Currency ) , I can do this manually by Portal --- > Cost Management --> Cost Analysis but I want to automate and save the data on daily basis with the described data in give format.

Answer 1

@Gaurav Singh Thanks for using Microsoft Q&A!!

You can use the az costmanagement export create Azure CLI cmdlet

az costmanagement export create --name "TestExport" --type "Usage" --dataset-configuration columns="MeterId" columns="MeterName" columns="InstanceId" columns="ResourceLocation" columns="PreTaxCost" --timeframe "MonthToDate" --storage-container="exports" --storage-account-id="/subscriptions/{SubscriptionId}/resourceGroups/{resourcegroup}-Analytics/providers/Microsoft.Storage/storageAccounts/{storageaccount}" --storage-directory="cost-mgmt-export-report" --recurrence "Monthly" --recurrence-period from="2023-06-28T00:00:00Z" to="2023-06-30T00:00:00Z" --schedule-status "Active" --scope "/subscriptions/{SubscriptionId}/resourceGroups/{ResourceGroup}"

Once the export task is completed you will see exported csv file like below under the storage container like below -

Please refer to Tutorial: Create and manage exported data which provides details to create a cost management export schedule. You can also refer to az costmanagement export document to know more about the CLI cmdlet.

Please let me know if you have any questions.

Thanks

Saurabh

---

Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

I have tested this option but this won't work for me, I want something like the attached image, this I am getting currently using the Cost Analysis (Preview).

Answer 3

@Gaurav Singh

I looked into various PowerShell and Azure CLI cmdlets and found that the Invoke-AzCostManagementQuery could be used to get the results which you are looking for but the cmdlet does not give you data in slightly different format and so you need to add logic in Powershell to transform and store the result in a desired format in a csv file.
Please find below the cmdlet to get the required results-

$aggregation = @{  
  totalCost = @{
    name     = "Cost"
    function = "SUM"
  }
  totalCostUSD = @{
        name =  "CostUSD"
        function = "Sum"
  }
}

$grouping = @(
      @{
        "type"= "Dimension"
        "name"= "SubscriptionId"
      },
      @{
        "type"= "Dimension"
        "name"= "SubscriptionName"
      },
      @{
        "type"= "Dimension"
        "name"= "PublisherType"
      }
)
# Management scope query - working fine
$output = Invoke-AzCostManagementQuery -Scope "/providers/Microsoft.Management/managementGroups/{ManagementGroupId}" -Type ActualCost -Timeframe Custom -TimePeriodFrom "2023-06-01T00:00:00+00:00" -TimePeriodTo "2023-06-30T23:59:59+00:00" -DatasetAggregation $aggregation -DatasetGrouping $grouping -debug

Result

Now, you can use the below code to transform and store the result in a csv format.

# Create a new StringBuilder object to build the CSV content
$csvBuilder = New-Object -TypeName System.Text.StringBuilder

# Build the CSV header row
$csvBuilder.AppendLine(($output.Column.Name -join ","))

# Extract the row values from the output
$rowValues = $output.Row

# Iterate over the row values
foreach ($row in $rowValues) {
    # Convert the row values to CSV format
    $csvRow = $row -join ","
    
    # Append the row to the CSV content
    $csvBuilder.AppendLine($csvRow)
}

# Convert the StringBuilder content to a string
$csvContent = $csvBuilder.ToString()

# Specify the output file path
$outputFilePath = "output.csv"

# Write the CSV content to the output file
$csvContent | Out-File -FilePath $outputFilePath -Encoding UTF8

Here is the .csv file which I believe would suffice your needs -

Please let me know if you have any questions. Thank you. Please let me know if you have any questions. Thank you. Saurabh

---

Please consider hitting Accept Answer button. Accepted answers help community as well.