0

I found the following PowerShell VMware script that exports several pieces of information from the environment.

&{foreach($vm in (Get-VM)) {
$vm.ExtensionData.Guest.Net | select -Property @{N='VM';E={$vm.Name}},
@{N='Host';E={$vm.VMHost.Name}},
@{N='OS';E={$vm.Guest.OSFullName}},
@{N='Tools';E={$vm.ExtensionData.Guest.ToolsRunningStatus}},
@{N='NicType';E={[string]::Join(',',(Get-NetworkAdapter -Vm $vm | Select-Object -ExpandProperty Type))}},
@{N='VLAN';E={[string]::Join(',',(Get-NetworkAdapter -Vm $vm | Select-Object -ExpandProperty NetworkName))}},
@{N='IP';E={[string]::Join(',',($vm.Guest.IPAddress | Where {($_.Split(".")).length -eq 4}))}},
@{N='Gateway';E={[string]::Join(',',($vm.ExtensionData.Guest.IpStack.IpRouteConfig.IpRoute | %{if($_.Gateway.IpAddress){$_.Gateway.IpAddress}}))}},
@{N='Subnet Mask';E={
            $dec = [Convert]::ToUInt32($(('1' * $_.IpConfig.IpAddress[0].PrefixLength).PadRight(32, '0')), 2)
            $DottedIP = $( For ($i = 3; $i -gt -1; $i--) {
                    $Remainder = $dec % [Math]::Pow(256, $i)
                    (                        $dec - $Remainder) / [Math]::Pow(256, $i)
                    $dec = $Remainder
                } )
            [String]::Join('.', $DottedIP) 
        }},
@{N="DNS";E={[string]::Join(',',($vm.ExtensionData.Guest.IpStack.DnsConfig.IpAddress))}},
@{N='MAC';E={[string]::Join(',',$_.MacAddress)}}}}

The output of it, is in a following style: (several hundred of those "blocks", one per VM, showing two of them)

VM          : VMname1
Host        : ESXi1.domain.com
OS          : Red Hat Enterprise Linux 6 (64-bit)
Tools       : guestToolsRunning
NicType     : Vmxnet3
VLAN        : 123-DMZ-VMnet
IP          : 10.10.10.40
Gateway     : 10.10.10.1
Subnet Mask : 255.255.255.0
DNS         : 10.10.10.11,10.10.10.13
MAC         : 01:50:56:9a:98:62


VM          : VMname2
Host        : ESXi2.domain.com
OS          : Microsoft Windows Server 2012 (64-bit)
Tools       : guestToolsRunning
NicType     : Vmxnet3
VLAN        : 20-VMnet
IP          : 10.55.10.14
Gateway     : 10.55.10.1
Subnet Mask : 255.255.255.0
DNS         : 10.10.10.11,10.10.10.13,10.20.10.11,10.10.231.13
MAC         : 12:50:32:22:68:9c

I have tried to find a correct approach to convert the output to the CSV format: VM,HOST,OS,Tools,NicType,VLAN,IP,Gateway, Subnet Mask, DNS, MAC VMname1,ESXi1.domain.com, etc VMname1,ESXi2.domain.com, etc

With the "conversion script", also found on the internet, which I tried to adjust to meet my needs, I tried to convert the information however it doesn't seems to work.

$list = Get-Content C:\temp\IPs\results.txt
$i = 0
Do
{
$VM1 = $list[$i].value
$Host1 =$list[$i+1].value
$OS1 =$list[$i+2].value
$Tools1 =$list[$i+3].value
$NicType1 =$list[$i+4].value
$VLAN1 =$list[$i+5].value
$IP1 =$list[$i+6].value
$Gateway1 =$list[$i+7].value
$SubnetMask1 =$list[$i+8].value
$DNS1 =$list[$i+9].value
$MAC1 =$list[$i+10].value
[array]$converter += $VM1 + "," + $Host1 + "," + $OS1 + "," + $Tools1 + "," 
+ $NicType1 + "," + $VLAN1 + "," + $IP1 + "," + $Gateway1 + "," + 
$SubnetMask1 + "," + $DNS1 + "," + $MAC1
$i = $i + 11
} while ($i -le $list.count)
$converter = $converter|convertfrom-csv -header VM1, Host1, OS1, Tools1, 
NicType1, VLAN1, IP1, Gateway1, SubnetMask1, DNS1, MAC1
$converter|export-csv C:\temp\IPs\results.csv -notypeinformation

Please note, for some entries such as DNS or IP address, there might be multiple values.

How would I go and convert the rows to columns to capture all the available info?

Thank you.

Improve this question
6
  • In $Converter why are you using all those joins? Just use double-quotes and string expansion! $Converter="$VM1,$Host1,..." Commented Nov 15, 2017 at 21:11
  • The answer quite simple really - I use this approach because I don't know PowerShell good enough yet. It's actually a learning curve that I'm going through right now, which is also by coincident something that I need to provide for work purposes. May I please ask you to indicate which line in the above code I should replace? Commented Nov 15, 2017 at 21:27
  • What's inside your results.txt? Commented Nov 15, 2017 at 21:35
  • It's almost empty, just column headers VM1, Host1, OS1 .... etc Commented Nov 15, 2017 at 22:37
  • I've added an answer that takes your objects, puts them into an array, and also exports all those values to a CSV specified by $Path Commented Nov 15, 2017 at 23:05

2 Answers 2

Reset to default
1

Edit: In hindsight, I have no idea why the original author is piping .Net to Select-Object when there is nothing done with that value. I've updated my answer with a better representation

Putting it all together (with formatting):

#Requires -Version 3

$Collection = ForEach ($VM in (Get-VM))
{
    $Out = Select-Object -InputObject $VM -Property @(
        @{ N = 'VM'
           E = {$PSItem.Name}
         },
        @{ N = 'Host'
           E = {$PSItem.VMHost.Name}
         },
        @{ N = 'OS'
           E = {$PSItem.Guest.OSFullName}
         },
        @{ N = 'Tools'
           E = {$PSItem.ExtensionData.Guest.ToolsRunningStatus}
         },
        @{ N = 'NicType'
           E = {(Get-NetworkAdapter -Vm $PSItem).Type -join ','}
         },
        @{ N = 'VLAN'
           E = {(Get-NetworkAdapter -Vm $PSItem).NetworkName -join ','}
         },
        @{ N = 'IP'
           E = {($PSItem.Guest.IPAddress | Where-Object { ($PSItem -split '.').Count -eq 4}) -join ','}
         },
        @{ N = 'Gateway'
           E = {($PSItem.ExtensionData.Guest.IpStack.IpRouteConfig.IpRoute | Where-Object { $PSItem.Gateway.IpAddress }) -join ','}
         },
        @{ N = 'Subnet Mask'
           E={
                $dec = [Convert]::ToUInt32($(('1' * $PSItem.ExtensionData.Guest.Net.IpConfig.IpAddress[0].PrefixLength).PadRight(32, '0')), 2)
                $DottedIP = $( For ($i = 3; $i -gt -1; $i--) {
                        $Remainder = $dec % [Math]::Pow(256, $i)
                        (                        $dec - $Remainder) / [Math]::Pow(256, $i)
                        $dec = $Remainder
                    } )
                [String]::Join('.', $DottedIP) 
            }
         },
        @{ N = 'DNS'
           E = {$PSItem.ExtensionData.Guest.IpStack.DnsConfig.IpAddress -join ','}
         },
        @{ N = 'MAC'
           E = {$PSItem.MacAddress -join ','}
         }
    )

    $Out | Export-Csv -Path $Path -NoTypeInformation -Append -Force -Encoding 'UTF8'
    $Out
}

If all you care about is the resulting CSV:

#Requires -Version 3

Get-VM |
    Select-Object -Property @(
        @{ N = 'VM'
           E = {$PSItem.Name}
         },
        @{ N = 'Host'
           E = {$PSItem.VMHost.Name}
         },
        @{ N = 'OS'
           E = {$PSItem.Guest.OSFullName}
         },
        @{ N = 'Tools'
           E = {$PSItem.ExtensionData.Guest.ToolsRunningStatus}
         },
        @{ N = 'NicType'
           E = {(Get-NetworkAdapter -Vm $PSItem).Type -join ','}
         },
        @{ N = 'VLAN'
           E = {(Get-NetworkAdapter -Vm $PSItem).NetworkName -join ','}
         },
        @{ N = 'IP'
           E = {($PSItem.Guest.IPAddress | Where-Object { ($PSItem -split '.').Count -eq 4}) -join ','}
         },
        @{ N = 'Gateway'
           E = {($PSItem.ExtensionData.Guest.IpStack.IpRouteConfig.IpRoute | Where-Object { $PSItem.Gateway.IpAddress }) -join ','}
         },
        @{ N = 'Subnet Mask'
           E={
                $dec = [Convert]::ToUInt32($(('1' * $PSItem.ExtensionData.Guest.Net.IpConfig.IpAddress[0].PrefixLength).PadRight(32, '0')), 2)
                $DottedIP = $( For ($i = 3; $i -gt -1; $i--) {
                        $Remainder = $dec % [Math]::Pow(256, $i)
                        (                        $dec - $Remainder) / [Math]::Pow(256, $i)
                        $dec = $Remainder
                    } )
                [String]::Join('.', $DottedIP) 
            }
         },
        @{ N = 'DNS'
           E = {$PSItem.ExtensionData.Guest.IpStack.DnsConfig.IpAddress -join ','}
         },
        @{ N = 'MAC'
           E = {$PSItem.MacAddress -join ','}
         }
    ) |
    Export-Csv -Path $Path -NoTypeInformation -Force -Encoding 'UTF8'
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for the script. When I import the csv into an excel, as well as when I open the output in text editor, IP column is blank across all rows, as well as MAC. Also GATEWAY column contains "VMware.Vim.NetIpRouteConfigInfoIpRoute" entry for all rows instead of IP of the gateway. Any idea on how to correct this?
@VickVega I'd suggest looking at a single VM's properties and see where the values you are looking for exist and plug them into the expression blocks. $VM = @(Get-VM)[0] then $VM | Get-Member and keep digging until you have the property you need.
Thanks for your help. I was able to resolve the issue with the original approach, slightly adjusted. It takes much longer to run, but results are correctly displayed.
0

The final working code:

&{foreach($vm in (get-VM)) {
$vm.ExtensionData.Guest.Net | select -Property @{N='VM';E={$vm.Name}},
@{N='Host';E={$vm.VMHost.Name}},
@{N='OS';E={$vm.Guest.OSFullName}},
@{N='Tools';E={$vm.ExtensionData.Guest.ToolsRunningStatus}},
@{N='NicType';E={[string]::Join(',',(Get-NetworkAdapter -Vm $vm | Select-Object -ExpandProperty Type))}},
@{N='VLAN';E={[string]::Join(',',(Get-NetworkAdapter -Vm $vm | Select-Object -ExpandProperty NetworkName))}},
@{N='IP';E={[string]::Join(',',($vm.Guest.IPAddress | Where {($_.Split(".")).length -eq 4}))}},
@{N='Gateway';E={[string]::Join(',',($vm.ExtensionData.Guest.IpStack.IpRouteConfig.IpRoute | %{if($_.Gateway.IpAddress){$_.Gateway.IpAddress}}))}},
@{N='Subnet Mask';E={
            $dec = [Convert]::ToUInt32($(('1' * $_.IpConfig.IpAddress[0].PrefixLength).PadRight(32, '0')), 2)
            $DottedIP = $( For ($i = 3; $i -gt -1; $i--) {
                    $Remainder = $dec % [Math]::Pow(256, $i)
                    (                        $dec - $Remainder) / [Math]::Pow(256, $i)
                    $dec = $Remainder
                } )
            [String]::Join('.', $DottedIP) 
        }},
@{N="DNS";E={[string]::Join(',',($vm.ExtensionData.Guest.IpStack.DnsConfig.IpAddress))}},
@{N='MAC';E={[string]::Join(',',$_.MacAddress)}}
  }
} | ft -AutoSize | Out-String -Width 4096 | Out-File C:\temp\VM-GetSomeInfo.txt
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.