VMware Cloud Community
raghavek
Contributor
Contributor

unable to export the output to csv/xls file

Hi

Please find below my script. I am not able to export the output to csv/xls file. Please help me. And also i have 10 vcenters, I need this report in xls format with each VC worksheets in one excel file.

$VM = Get-Cluster test-cluster | Get-VM | `
ForEach-Object {
$VM = $_
$VMview = $VM | Get-View
$Snapshots = $VM | Get-Snapshot
$Report = "" | Select-Object VMname,vmCreatedByUser,vmCreatedDate,ESXname,ClusterName,MemoryGB,vCPUcount,VMXname,VmdkSizeGB,DatastoreName,SnapshotCount,Owner
$Report.VMName = $VM.name
$Report.vmCreatedByUser = Get-VIEvent $VM | sort createdTime | select -first 1 | select UserName | % {$_.UserName.split("\")[1]}
$Report.vmCreatedDate = Get-VIEvent $VM | sort createdTime | select -first 1 | select CreatedTime
$Report.ESXname = $VM.VMHost
$Report.ClusterName = ($VM | Get-Cluster).Name
$Report.MemoryGB = $VM.MemoryMB/1024
$Report.vCPUcount = $VM.NumCpu
$Report.VMXname = $VM.ExtensionData.Config.Files.VmPathName.Split("/")[1]
$Report.VmdkSizeGB = $VM.UsedSpaceGB
$Report.DatastoreName = $VMview.Config.DatastoreUrl
$Report.SnapshotCount = ($VM | Get-Snapshot).Count
$Report.Owner = $VM | Get-VIPermission | Where-Object {$_.Role -like "xxxxxx*"} | select Principal | % {$_.Principal.split("\")[1]}
Write-Output $Report
}

Thanks
Raghav

Reply
0 Kudos
5 Replies
LucD
Leadership
Leadership

Try like this

$table = @()

ForEach($VM in (Get-Cluster test-cluster | Get-VM)){

    $Snapshots = $VM | Get-Snapshot

    $Report = "" | Select-Object VMname,vmCreatedByUser,vmCreatedDate,ESXname,ClusterName,MemoryGB,vCPUcount,VMXname,VmdkSizeGB,DatastoreName,SnapshotCount,Owner

    $Report.VMName = $VM.name

    $Report.vmCreatedByUser = Get-VIEvent $VM | sort createdTime | select -first 1 | select UserName | % {$_.UserName.split("\")[1]}

    $Report.vmCreatedDate = Get-VIEvent $VM | sort createdTime | select -first 1 | select CreatedTime

    $Report.ESXname = $VM.VMHost

    $Report.ClusterName = ($VM | Get-Cluster).Name

    $Report.MemoryGB = $VM.MemoryMB/1024

    $Report.vCPUcount = $VM.NumCpu

    $Report.VMXname = $VM.ExtensionData.Config.Files.VmPathName.Split("/")[1]

    $Report.VmdkSizeGB = $VM.UsedSpaceGB

    $Report.DatastoreName = $VM.ExtensionDataConfig.DatastoreUrl

    $Report.SnapshotCount = ($VM | Get-Snapshot).Count

    $Report.Owner = $VM | Get-VIPermission | Where-Object {$_.Role -like "xxxxxx*"} | select Principal | % {$_.Principal.split("\")[1]}

    $table += $Report

}

$table | Export-Csv report.csv -NoTypeInformation -UseCulture


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Prakas
Enthusiast
Enthusiast

Add the below line at the end of your code (before }), this will do the export for you.

$Report | Export-Csv -Path C:\ResultExport.csv -NoTypeInformation -Append

Exporting in Excel requires PowerShell Excel module. You can find more information here - Introducing the PowerShell Excel Module – Hey, Scripting Guy! Blog 

Reply
0 Kudos
LucD
Leadership
Leadership

And if you want this in a XLSX in separate worksheets per VC, you will have to use the ImportExcel module.

$fileName = 'report.xlsx'

foreach($vc in $global:DefaultVIServers){

    $table = @()

    ForEach($VM in (Get-Cluster test-cluster | Get-VM)){

        $Snapshots = $VM | Get-Snapshot

        $Report = "" | Select-Object VMname,vmCreatedByUser,vmCreatedDate,ESXname,ClusterName,MemoryGB,vCPUcount,VMXname,VmdkSizeGB,DatastoreName,SnapshotCount,Owner

        $Report.VMName = $VM.name

        $Report.vmCreatedByUser = Get-VIEvent $VM | sort createdTime | select -first 1 | select UserName | % {$_.UserName.split("\")[1]}

        $Report.vmCreatedDate = Get-VIEvent $VM | sort createdTime | select -first 1 | select CreatedTime

        $Report.ESXname = $VM.VMHost

        $Report.ClusterName = ($VM | Get-Cluster).Name

        $Report.MemoryGB = $VM.MemoryMB/1024

        $Report.vCPUcount = $VM.NumCpu

        $Report.VMXname = $VM.ExtensionData.Config.Files.VmPathName.Split("/")[1]

        $Report.VmdkSizeGB = $VM.UsedSpaceGB

        $Report.DatastoreName = $VM.ExtensionDataConfig.DatastoreUrl

        $Report.SnapshotCount = ($VM | Get-Snapshot).Count

        $Report.Owner = $VM | Get-VIPermission | Where-Object {$_.Role -like "xxxxxx*"} | select Principal | % {$_.Principal.split("\")[1]}

        $table += $Report

    }

   

    $table | Export-Excel -Path $fileName -WorkSheetname "$($vc.Name)"

}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
LucaV
Contributor
Contributor

Hi how would you run the same script but using a list of servers rather than all the vm's in the vcenter? thanks

Reply
0 Kudos
LucD
Leadership
Leadership

If the names are in a .txt file, one name per line, you could just replace the line of the loop by

ForEach($VM in (Get-VM -Name (Get-Content -Path .\vmnames.txt))){


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos