Combining Get-VM and Get-VMGuest in CSV output

[OwenW201110141]

Hi,

I am trying to generate a CSV that combines different properties between the Get-VM and Get-VMGuest cmdlets but struggling.

I'm aware that the Get-VM cmdlet will give me output like the number of cpus, amount of memory per vm, vm name etc, but also want to retrieve properties like IP address, running OS, hostname etc, in the same CSV report.

I've tried mixing the likes of -

get-vmguest -vm (get-vm) | select-object vmname, osfullname | export-csv c:\vm_osreport.csv

and a previous post by LucD -

--------------

get-vm | %{
  $_ | select Name | Out-Default
  $_ | Get-VMGuest | select State, IPAddress, OSFullName | Out-Default
}

Now if you want the result in 1 object (or an array of objects) you could do

get-vm | %{
  $vm = "" | Select Name, State, IPAddress, OSFullname
  $vm.Name = $_.Name
  $guest = $_ | Get-VMGuest
  $vm.State = $guest.State
  $vm.IPAddress = $guest.IPAddress
  $vm.OSFullName = $guest.OSFullName 
  $vm
}

--------------

but just cant seem to get what I want. Essentially I'm looking for is the following output to a CSV -

• vmhostname
• ip address
• numCPU
• memoryMB
• HD size allocated
• GuestOS (as reported by VMTools)
• Datacenter + Folder (if possible)

Can anyone help please?

Thank you!

[will47]

I don't know if this is the "best" way, but I've been writing sometehing to export some information based on this tool:

http://www.wooditwork.com/2010/08/16/exporting-all-that-useful-vm-information-with-powercli

So the basic idea in terms of the data structures is:

1) Create an empty report (array)

2) Create an table ( $Summary = {} | Select Thing1, Thing2) and populate it for each machine

3) $report += $Summary at the end of each iteration

4) Build your CSV off of $Report

Here's my example if it's any help; I'm exporting different information than you, but same idea (and feedback welcome if I'm doing something dumb). The idea of having an XML version is that it lets you handle nested data structures better for information for which there can be >1, e.g., network adapters, hard drives, etc.

Param(
    [Parameter(Mandatory=$true)]
    [ValidateSet("XML","CSV")]
    [String]
    $OutputFormat
)

# Limit to just "Foo" datacenter for now
$VMS = Get-VM -Location "Foo"

# Create an empty report array
$Report = @()

ForEach ($VM in $VMS) {

    $VMView = $VM | Get-View
    # Construct an object to stuff
    $Summary = {} | Select Name, Parent, OS, Powerstate, Memory, `
      ToolsVersion, ToolsStatus, HardwareVersion, VMConfigPath, `
      VMHDPaths, NetworkInfo

    # Then stuff in a bunch of facts
    $Summary.Name = $VM.name
    $Summary.Parent = $VM.VMHost.name
    $Summary.OS = $VMView.Summary.Config.guestFullName
    $Summary.Powerstate = [String] $VM.Powerstate
    $Summary.Memory = [Math]::Round(($VM.MemoryMB),2)
    $Summary.ToolsVersion = [Int] $VMView.Config.Tools.ToolsVersion
    $Summary.ToolsStatus = [String] $VMView.Guest.ToolsStatus
    $Summary.HardwareVersion = $VMView.Config.Version

    $Summary.VMConfigPath = $VMView.Config.Files.VMPathName

    # stuffing params that can have multiple values into a CSV is kind of ugly

    $HDinfo = @()
    ForEach ($hd in $hds) {
        $detail = $VM | Get-HardDisk -Name $hd.Name
        $HDTmp = @{}
        $HDTmp.HDName = $detail.Name
        $HDTmp.File = $detail.Filename
        if ($OutputFormat -eq "XML") {
            $HDinfo += $HDTmp
        } else {
            $HDinfo += [String]::join(":", $HDTmp.Values)
        }
    }
    if ($OutputFormat -eq "XML") {
        $Summary.VMHDPaths = $HDinfo
    } else {
        $Summary.VMHDPaths = [String]::join("|", $HDinfo)
    }

    $adapters = $VM | Get-NetworkAdapter

    $Netinfo = @()
    ForEach ($adapter in $adapters) {
        $detail = $VM | Get-NetworkAdapter -Name $adapter.Name
        # Pack into a data structure
        $NITmp = @{}
        $NITmp.AdapterName = $adapter.Name
        $NITmp.NetworkName = $detail.NetworkName
        $NITmp.MacAddress = $detail.MacAddress
        if ($OutputFormat -eq "XML") {
            $Netinfo += $NITmp
        } else {
            $Netinfo += [String]::join("|", $NITmp.Values)
        }
    }
    if ($OutputFormat -eq "XML") {
        $Summary.NetworkInfo = $Netinfo
    } else {
        $Summary.NetworkInfo = [String]::join("^", $Netinfo)
    }

    $Report += $Summary
}

if ($OutputFormat -eq "XML") {
    $Report | Export-Clixml vm_info.xml
} elseif ($OutputFormat -eq "CSV") {
    $Report | Export-CSV -NoTypeInformation vm_info.csv
}

Message was edited by: will47: formatting

Message was edited by: will47 add additional code block, formatting.

[LucD · Accepted solution]

Since the Guest property is present inside the VirtualMachine object returned by Get-VM, you can get those values without executing the Get-VMGuest cmdlet.

Something like this

Get-VM  | 
Select Name,
 @{N="Hostname";E={$_.Host.Name}},
 @{N="IP addr";E={[string]::Join(',',$_.Guest.IpAddress)}},
 NumCPU,MemoryMB,
 @{N="HD size allocated (KB)";E={[string]::Join(',',($_.HardDisks | Select -ExpandProperty CapacityKB))}},
 @{N="GuestOS";E={$_.Guest.OSFullName}},
 @{N="Datacenter";E={Get-Datacenter -VM $_ | Select -ExpandProperty Name}},
 @{N="Folder";E={$_.Folder.Name}} | 
Export-Csv C:\report.csv -NoTypeInformation -UseCulture

[will47]

What Lucd said (esp. since he takes into consideration things like concatenating multiple IPs), but since I already played around with this, here's an example (incorporating some of his suggestions) that's terser and closer to what you asked for, than what I wrote above. It's a little longer than what he wrote, but it's a little easier for me to parse.

Get-VM | %{

    $Summary = "" | Select Name, IP, NumCPU, MemoryMB, HDSize, `

       GuestOS, Datacenter, Folder

    $Summary.Name = $_.Name

    $Summary.IP = [String] $_.Guest.IPAddress

    $Summary.NumCPU = $_.NumCPU

    $Summary.MemoryMB = $_.MemoryMB

    $HD = @($_ | Get-HardDisk)

    $SizeMB = $HD[0].CapacityKB / 1024

    $Summary.HDSize = [Math]::Round($SizeMB, 0)

    $Summary.GuestOS = $_.Guest.OSFullName

    $Summary.Datacenter = (Get-Datacenter -VM $_).Name

    $Summary.Folder = $_.Folder.Name

    $Summary

} | Export-CSV -Notypeinformation info.csv

Message was edited by: will47: add datacenter / folder info

Message was edited by: will47: couple of edits

[OwenW201110141]

Luc and Will - thank you for these suggestions, they both give me exactly what I need!

Cheers!

[will47]

I updated mine, using LucD's style of building the hashtable. It's now much shorter, somewhat easier to read, and avoids 'Get-View' or 'Get-VMGuest' entirely.

Since I was doing some conditional formatting based on the output type (wanting to preserve the nested data structure in the XML output), I couldn't figure out how to do that at first. Eventually, I realized I could break that off into a function, and then return different data, depending on the output format, which works pretty well.

If anyone has other suggestions, esp. in terms of how to make the XML output better, let me know.