How to find consumption of Virtual machines in Azure during the last 3 months through Powershell and export it into a CSV file

Question

I want to generate VM consumption for the last 3 months separately in 3 different columns , for eg : Nov-Dec in 1st column , Dec-Jan in 2nd column and Jan-feb in 3rd column and exporting this data into CSV. I am using :

 $startDate=Get-Date
    for($i=1 ; $i -le 3; $i++)
     {
        $currentdate=$startDate.AddMonths(-$i)
        $enddate=$startDate.AddMonths(-$i+1)
        $infos = Get-AzConsumptionUsageDetail -StartDate $currentdate -EndDate $endDate - ResourceGroup $ResourceGroup
        $infos | Where-Object {$_.Product -like "*$sku*" -and $_.InstanceLocation -like 
          "$Region"} | Export-Csv -NoTypeInformation -Path .\"$($subscription).csv"
     }

Here I am passing the value for $sku as Virtual machine through a function . This script is giving me the usagequantity details in 1 column itself for all the 3 months but I want it in 3 different columns

Use [`Select-Object`](https://learn.microsoft.com/powershell/module/microsoft.powershell.utility/select-object) with a [calculated property](https://learn.microsoft.com/powershell/module/microsoft.powershell.core/about/about_calculated_properties). Something like: `... |Select-Object AccountName,Product,@{n='Nov-Dec', e={$_.usagequantity[0]}},@{n='Dec-Jan', e={$_.usagequantity[1]}},...` — iRon, Feb 07 '23 at 08:34

Anthony Norwood · Answer 1 · 2023-02-07T08:58:54.097

0

You'll need to build the object (or table) in Powershell before exporting to CSV, so that you can define the columns:

$table = "" | Select-Object Column1,Column2,Column3,Month1,Month2,Month3

$table.Column1 = $someValue
$table.Column2 = $someValue
$table.Column3 = $someValue

{
Your Script
}

$table.Month1 = $ConsumptionWhere$i=1
$table.Month2 = $ConsumptionWhere$i=2
$table.Month3 = $ConsumptionWhere$i=3

edited Feb 07 '23 at 08:58

answered Feb 07 '23 at 07:52

Anthony Norwood

357
1
7

Can you give an example wrt to the script I have posted above? @Anthony Norwood – Siddharth Singh Feb 07 '23 at 08:06
I can create a column but how shall I make sure that the values will be stored inside their respective columns – Siddharth Singh Feb 07 '23 at 08:08
Apart from the fact that this unlikely answers the question from @Siddharth Singh, you should try to [avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/a/60708579/1701026) – iRon Feb 07 '23 at 08:37
You're absolutely right @iRon – Anthony Norwood Feb 07 '23 at 08:52
1

@iRon thanks for sharing the link, I'll check that out as I've always used += so great to see a better method. I've updated my answer Siddharth – Anthony Norwood Feb 07 '23 at 08:59

How to find consumption of Virtual machines in Azure during the last 3 months through Powershell and export it into a CSV file

1 Answers1