Script to get 1 csv per cluster with the related info (list of vms, host, capacity, iops...)

[Shizukya]

Hi everyone, i'm new here and it's been days i am trying to find the proper script to list vms and related information on each cluster and to get 1 csv for each individual cluster.

Here my code :

Get-content C:\cl.txt | % {

  $cl=$_

  Get-VM | Select-Object  @{N="Cluster";E={Get-Cluster -VM $_}}, @{Expression="Host"; Label="ESX Host"}, @{Expression="Name"; Label="VM"},

@{Expression="Numcpu"; Label="vCPU"}, @{Expression="MemoryGB"; Label="RAM(GB)"},

@{ n="Volume provisionne"; e={[math]::round( $_.ProvisionedSpaceGB, 2 )}}, @{ n="Volume utilise"; e={[math]::round( $_.UsedSpaceGB, 2 )}},

@{N="IOPS/Ecriture"; E={[math]::round((Get-Stat $_ -stat "datastore.numberWriteAveraged.average" -RealTime | Select -Expand Value | measure -average).Average, 1)}},

@{N="IOPS/Lecture"; E={[math]::round((Get-Stat $_ -stat "datastore.numberReadAveraged.average" -RealTime | Select -Expand Value | measure -average).Average, 1)}} | Export-Csv -NoTypeInformation -UseCulture "C:\vm_report_$cl.csv"

}

Note: Each csv will be generated with respect to each cluster

cl.txt lists the clusters but all i get is multiple files with same content and they are not renamed properly

How can i make it to generate 1 csv per cluster ?

For example, there are 3 clusters A1, A2, A3 (in the file cl.txt), we should get 3 csv files generated individually A1.csv, A2.csv and A3.csv and the related data into them :

A1.csv :                                                                 A2.csv :                                                                    A3.csv

Cluster     ESX Host     VM          RAM(GB)             Cluster      ESX Host          VM          RAM(GB)          Cluster     ESX Host       VM                         

A1           sx0001          su19001              4              A2          sx0001adm        iu01990                32             A3          su1386adm     su10670

A1           sx0001          su23000              2               A2          sx0001adm        su12404              32             A3          sx1385adm     su10034

A1           sx0002          su55000              2               A2          sx0002pack        sw12785              1              A3          su1384adm     iu00025

Any help with this script  would be gladly appreciated.

Thanks,

Joseph.

[LucD]

Try like this

Get-content C:\cl.txt | % {

    $cl=$_

    Get-VM -Location (Get-Cluster -Name $cl) |

    Select-Object  @{N="Cluster";E={$cl}},

        @{Expression="Host"; Label="ESX Host"},

        @{Expression="Name"; Label="VM"},

        @{Expression="Numcpu"; Label="vCPU"},

        @{Expression="MemoryGB"; Label="RAM(GB)"},

        @{ n="Volume provisionne"; e={[math]::round( $_.ProvisionedSpaceGB, 2 )}},

        @{ n="Volume utilise"; e={[math]::round( $_.UsedSpaceGB, 2 )}},

        @{N="IOPS/Ecriture"; E={[math]::round((Get-Stat $_ -stat "datastore.numberWriteAveraged.average" -RealTime | Select -Expand Value | measure -average).Average, 1)}},

        @{N="IOPS/Lecture"; E={[math]::round((Get-Stat $_ -stat "datastore.numberReadAveraged.average" -RealTime | Select -Expand Value | measure -average).Average, 1)}} |

    Export-Csv -NoTypeInformation -UseCulture "C:\vm_report_$($cl).csv"

}

[Shizukya]

hi LucD, thanks for your quick answer.

I tried and i got this error plus i dont understand, it created 5 empty files, two with the right cluster name and others like "vm_report_Name", "vm_report_----" and "vm_report_" :

The file cl.txt looks like :

Name                                                                        

----                                                                        

FARM_RN_PFV_STD                                                             

FARM_RN_PFV_MQ

CLUSTER_HR_PFD_1                                                             

CLUSTER_HR_PFD_2

A1

A2

[LucD]

It looks as if your file cl.txt is a CSV file.

Replace the first 2 lines with these.

Import-Csv C:\cl.txt -UseCulture | %{

    $cl=$_.Name

[Shizukya]

This time i got this :

[LucD]

Does your input file (cl.txt) look like the attached file ?

[Shizukya]

Not exactly, here the current preview and file (attached)

                                    

I've created it using " Get-Cluster | Select Name | Out-file C:\cl.txt "  to keep the file updated.                   

[LucD]

Try creating the file with this line

Get-Cluster | Select Name | Export-Csv C:\cl.txt -NoTypeInformation -UseCulture

[Shizukya]

Thanks LucD, you rock ! It is working like a charm !! By using the file generated from your script. It was a file issue i guess.

But few minor things still bother me according to the output :

I have the 17 files related to the 17 clusters with the correct columns/data (i have checked them) but if you take a look on the output, there is 2 files duplicated and one random file :

"vm_report_farm_rn_pfv_mq                     .csv"           0 kb

"vm_report_farm_rn_pfv_mq.csv"                                2 kb

"vm_report_farm_rn_pfv_std                             .csv"   0 kb

"vm_report_farm_rn_pfv_std.csv"                              17 kb

"vm_report_Name                                    .csv"           0 kb

Do you know how i could fix it to not having empty files and duplicates (with random space between name and .csv) ?

Thanks in advance !

[LucD · Accepted solution]

Judging by the timestamp, it looks as if those files with the blanks between the filename and the filetype, were made yesterday.

Could this have been one your earlier trials ?

Perhaps do a test run of the script in an empty folder.

[Shizukya]

Oh right, my bad.

So thats all working so great thanks to you !

Many thanks you saved my day

This looks like this now :

Get-Cluster | Select Name | Export-Csv C:\cl.txt -NoTypeInformation -UseCulture

Import-Csv C:\cl.txt -UseCulture | %{

    $cl=$_.Name

    Get-VM -Location (Get-Cluster -Name $cl) |

    Select-Object  @{N="Cluster";E={$cl}},

        @{Expression="Host"; Label="ESX Host"},

        @{Expression="Name"; Label="VM"},

        @{Expression="Numcpu"; Label="vCPU"},

        @{Expression="MemoryGB"; Label="RAM(GB)"},

        @{ n="Volume provisionne"; e={[math]::round( $_.ProvisionedSpaceGB, 2 )}},

        @{ n="Volume utilise"; e={[math]::round( $_.UsedSpaceGB, 2 )}},

        @{N="IOPS/Ecriture"; E={[math]::round((Get-Stat $_ -stat "datastore.numberWriteAveraged.average" -RealTime | Select -Expand Value | measure -average).Average, 1)}},

        @{N="IOPS/Lecture"; E={[math]::round((Get-Stat $_ -stat "datastore.numberReadAveraged.average" -RealTime | Select -Expand Value | measure -average).Average, 1)}} |

    Export-Csv -NoTypeInformation -UseCulture "C:\vm_report_$($cl).csv"

}

Cheers