Trying to combine 3 variables for elegant looking output

PowerShell Help
1

Hi everyone,
I have these 3 one-liners that get me various information, Im having trouble combing them so the output looks nice. I know this is not strictly Powershell, I have some PowerCLI in there:

$esx = (Get-Cluster -Name "ClusterName" | Get-VMHost | Get-VMHostNetworkAdapter -VMKernel | 
        Select-Object VMHost, Name, IP, SubnetMask, PortGroupName, mtu, VMKernelGateway, Gateway | Export-Csv -Path c:\temp\test.csv)

    $esxnetwork = (Get-Cluster -Name "ClusterName" | Get-VMHost | Get-VMHostNetworkStack | Select-Object -Property VMHost, DnsHostName, Gateway, ID, IPv6Enabled, DnsAddress, DnsSearchDomain) | Export-Csv -Path c:\temp\test1.csv -Append -Force


    $NTP = (Get-Cluster -Name "ClusterName" | Get-VMHost | Select-Object name, @{l = ”ServiceRunning”; e = { get-vmhostservice $_ | Where-Object { $_.key -like “ntpd” } | Select-Object -expand running } }, @{l = ”NTPServer”; E = { get-vmhostntpserver $_ } }) | Export-Csv -Path c:\temp\ntp.csv -Append -Force

As it is now, if I run those commands and look at each variable, it looks ok. But when I append them all to the CSV file, not so much. Im losing some output and it just doesnt look good at all.

Thanks for any help.

Matt

2

Usually we use loops or nested loops in combination with a PSCustomObject to combine the results from different but related queries.

Here’s an example of how to combine the results of 3 different sources in one output object:

$ComputerName = 'DesiredRemoteComputer'

$so = New-CimSessionOption -Protocol DCOM 
$CimSession = New-CimSession -CN $ComputerName -SessionOption $so
$BIOS = Get-CimInstance -Class CIM_BIOSElement -CimSession $CimSession | Select-Object -Property *
$OS = Get-CimInstance -Class CIM_OperatingSystem -CimSession $CimSession | Select-Object -Property *
$Computer = Get-CimInstance -Class CIM_ComputerSystem -CimSession $CimSession | Select-Object -Property *
[PSCustomObject]@{
    ComputerName   = $BIOS.PSComputerName;
    Model          = $Computer.Model;
    BIOSName       = $BIOS.Name;
    SMBIOSVersion  = $BIOS.SMBIOSBIOSVersion;
    BIOSVersion    = $BIOS.BIOSVersion;
    ReleaseDate    = $BIOS.ReleaseDate;
    SerialNumber   = $BIOS.SerialNumber;
    OSCaption      = $OS.Caption;
    OSVersion      = $OS.Version;
    InstallDate    = $OS.InstallDate;
    LastBootUpTime = $OS.LastBootUpTime;
    PhysicalRAM    = [math]::round((($Computer.TotalPhysicalMemory) / 1GB), 2);
}

If one of this queries results in an array you’d need to add a loop and iterate over its elements. The [PSCustomObject] is always created inside the inner most loop if you have nested loops.

1 Like
3

Olaf,
Here is what I have, but I get no output from this:

Do I need another foreach loop before the $esxnetwork and $NTP declarations? I tried but not working as I expect:

foreach ($a in $esx) {
    $esx = (Get-Cluster -Name "clustername" | Get-VMHost | Get-VMHostNetworkAdapter -VMKernel | 
        Select-Object VMHost, Name, IP, SubnetMask, PortGroupName, mtu, VMKernelGateway, Gateway)

    $esxnetwork = (Get-Cluster -Name "clustername" | Get-VMHost | Get-VMHostNetworkStack | Select-Object -Property VMHost, DnsHostName, Gateway, ID, IPv6Enabled, DnsAddress, DnsSearchDomain)
  
      
    $NTP = (Get-Cluster -Name "clustername" | Get-VMHost | Select-Object name, @{l = ”ServiceRunning”; e = { get-vmhostservice $_ | Where-Object { $_.key -like “ntpd” } | Select-Object -expand running } }, @{l = ”NTPServer”; E = { get-vmhostntpserver $_ } })
   
    [PSCustomObject]@{
        Hostname        = $esx.VMHost;
        IP              = $esx.IP;
        SubnetMask      = $esx.SubnetMask;
        PortGroupName   = $esx.PortGroupName;
        ID              = $esxnetwork.ID;
        Gateway         = $esxnetwork.Gateway;
        IPv6Enabled     = $esxnetwork.IPv6Enabled;
        DnsAddress      = $esxnetwork.DnsAddress;
        DNS             = $esxnetwork.DnsHostName;
        DnsSearchDomain = $esxnetwork.DnsSearchDomain;
        NTP             = $NTP.NTPserver;

    }
}

Thanks for any help as always/
4

… that looks weird … how do you populate $esx? Using the same variable for the loop itself and defining it inside the loop cannot work. :thinking:

What is it actually what you want to achieve? If you want to get information about each individual VMHost of a given cluster you may run something like this:

Get-Cluster -Name "swbdvcpyld01v.nslijhs.net" | 
Get-VMHost | 
ForEach-Object {
    $VMHostNetworkAdapter = Get-VMHostNetworkAdapter -VMKernel -VMHost $_
    $VMHostNetworkStack = Get-VMHostNetworkStack -VMHost $_

    [PSCustomObject]@{
        Hostname   = $VMHostNetworkAdapter.VMHost
        IP         = $VMHostNetworkAdapter.IP
        IPv6Enable = $VMHostNetworkStack.IPv6Enabled
        DnsAddress = $VMHostNetworkStack.DnsAddress
    }
}

!! You may adapt the queries inside the loop. You don’t provide the VMHost via pipeline - so you have to specify it.

5

I didn’t mean to put my cluster name there, I had a crazy day and am just getting back to this. anyway, Thanks Olaf, how would I go about adding an expression to the pscustomobject? I am trying to get the NTP servers added in, but my code pipes the cluster name into get-vmhost, which pipes to a select-object with an expression in it.

    
    $NTP = (Get-Cluster -Name "Clustername" | Get-VMHost | Select-Object name, @{l = ”ServiceRunning”; e = { get-vmhostservice $_ | Where-Object { $_.key -like “ntpd” } | Select-Object -expand running } }, @{l = ”NTPServer”; E = { get-vmhostntpserver $_ } })

    Get-Cluster -Name "Clustername" | 
    Get-VMHost | 
    ForEach-Object {
        $VMHostNetworkAdapter = Get-VMHostNetworkAdapter -VMKernel -VMHost $_
        $VMHostNetworkStack = Get-VMHostNetworkStack -VMHost $_
        $ntpserver = $ntp.NTPserver    
        [PSCustomObject]@{
            Hostname                 = $VMHostNetworkAdapter.VMHost
            IP                       = $VMHostNetworkAdapter.IP
            Mask                     = $VMHostNetworkAdapter.SubnetMask
            PortGroup                = $VMHostNetworkAdapter.PortGroupName
            IPv6Enable               = $VMHostNetworkStack.IPv6Enabled
            DnsAddress               = $VMHostNetworkStack.DnsAddress
            DNSSearchDomain          = $VMHostNetworkStack.DnsSearchDomain
            Gateway                  = $VMHostNetworkStack.Gateway
            NTP                      = $NTP.NTPserver
        }
    }

My output has nothing in the NTP field. I know it wont work like that because the $NTP variable has all of the hosts in it right from the start, and then inside the foreach-object, the cluster and hosts are piped again. But I do not know how to get it to work. I am also failing at trying to get this output to a .csv file :frowning:

Thanks,
Matt

6

Here we go, this gives me the NTP in the output, woohoo!

any suggestions on how to output to a csv file?

    Get-Cluster -Name "ClusterName" | 
    Get-VMHost | 
    ForEach-Object {
        $VMHostNetworkAdapter = Get-VMHostNetworkAdapter -VMKernel -VMHost $_
        $VMHostNetworkStack = Get-VMHostNetworkStack -VMHost $_
        $NTPSERVER = get-vmhostntpserver -VMHost $_
        [PSCustomObject]@{
            Hostname                 = $VMHostNetworkAdapter.VMHost
            IP                       = $VMHostNetworkAdapter.IP
            Mask                     = $VMHostNetworkAdapter.SubnetMask
            PortGroup                = $VMHostNetworkAdapter.PortGroupName
            IPv6Enable               = $VMHostNetworkStack.IPv6Enabled
            DnsAddress               = $VMHostNetworkStack.DnsAddress
            DNSSearchDomain          = $VMHostNetworkStack.DnsSearchDomain
            Gateway                  = $VMHostNetworkStack.Gateway
            NTP                      = $NTPSERVER
        }
    }
7

?? … you pipe it to Export-CSV?! :man_shrugging:t4:

8

hmm, here is my output when I open it in excel

#TYPE System.Management.Automation.PSCustomObject								
Hostname	IP	Mask	PortGroup	IPv6Enable	DnsAddress	DNSSearchDomain	Gateway	NTP
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
9

So your results are arrays and you have to use loops. But this approach has its limits when you want to show hierarchical data. It does not make that much sense to use it for data from different branches of hierarchical data.

Get-Cluster -Name "ClusterName" | 
Get-VMHost | 
ForEach-Object {
    $VMHostNetworkAdapterList = Get-VMHostNetworkAdapter -VMKernel -VMHost $_
    $NTPSERVER = get-vmhostntpserver -VMHost $_
    foreach ($VMHostNetworkAdapter in $VMHostNetworkAdapterList) {
        [PSCustomObject]@{
            Hostname  = $VMHostNetworkAdapter.VMHost
            IP        = $VMHostNetworkAdapter.IP
            Mask      = $VMHostNetworkAdapter.SubnetMask
            PortGroup = $VMHostNetworkAdapter.PortGroupName
            NTP       = $NTPSERVER
        }
    }
}

Another way would be to flatten the contained arrays:

Get-Cluster -Name "ClusterName" | 
Get-VMHost | 
ForEach-Object {
    $VMHostNetworkAdapter = Get-VMHostNetworkAdapter -VMKernel -VMHost $_
    $VMHostNetworkStack = Get-VMHostNetworkStack -VMHost $_
    $NTPSERVER = get-vmhostntpserver -VMHost $_
    [PSCustomObject]@{
        Hostname        = ($VMHostNetworkAdapter.VMHost) -join ', '
        IP              = ($VMHostNetworkAdapter.IP) -join ', '
        Mask            = ($VMHostNetworkAdapter.SubnetMask) -join ', '
        PortGroup       = ($VMHostNetworkAdapter.PortGroupName) -join ', '
        IPv6Enable      = ($VMHostNetworkStack.IPv6Enabled) -join ', '
        DnsAddress      = ($VMHostNetworkStack.DnsAddress) -join ', '
        DNSSearchDomain = ($VMHostNetworkStack.DnsSearchDomain) -join ', '
        Gateway         = ($VMHostNetworkStack.Gateway) -join ', '
        NTP             = ($NTPSERVER) -join ', '
    }
}
10

Thanks for all your help Olaf, here is a nice working version:

$virtualcenter = 'a', 'b', 'c', 'd'
$clustername = 'e', 'f', 'g', 'h'

foreach ($vc in $virtualcenter) {

    Connect-VIServer -Server $vc
    foreach ($clus in $clustername) {
        Get-Cluster -Name "$clus" -ErrorAction SilentlyContinue | 
        Get-VMHost | 
        ForEach-Object {
            $clust = (Get-Cluster -Name "$clus" -ErrorAction SilentlyContinue)
            $vmhostnetworkadapter = Get-vmhostnetworkadapter -VMKernel -VMHost $_
            $VMHostNetworkStack = Get-VMHostNetworkStack -VMHost $_
            $NTPSERVER = get-vmhostntpserver -VMHost $_
            $Nic = Get-VMHostNetwork -VMHost $_ | Select-Object -ExpandProperty VirtualNic

            [PSCustomObject]@{
                Cluster         = ($clust.Name) -join ', '
                Hostname        = ($vmhostnetworkadapter.VMHost.GetValue(0)) -join ', '
                Device          = ($Nic.DeviceName) -join ', '
                DeviceIP        = ($Nic.IP) -join ', '
                DeviceMask      = ($nic.SubnetMask) -join ', '
                Gateway         = ($VMHostNetworkStack.Gateway) -join ', '
                DnsAddress      = ($VMHostNetworkStack.DnsAddress) -join ', '
                NTP             = ($NTPSERVER) -join ', '
                PortGroup       = ($vmhostnetworkadapter.PortGroupName) -join ', '
                DNSSearchDomain = ($VMHostNetworkStack.DnsSearchDomain) -join ', '
                
            } 
        } | Export-Csv -Append c:\temp\hosts.csv
    }  Disconnect-VIServer -Server $vc -Confirm:$false
}

The only issue I have is more of a VMware issue, for some reason its not showing the VMK1 gateway address. I would love to be able to dig that out, I know its there because I see it in Virtual Center, but on this output its just blank. I get the VMK0 GW.

Anyone good with VMware specific problems?

Thanks!

11

Great that you’ve found a solution. :+1:t4:

Sometimes it helps to focus on just the single element you have issues with. Try to get only the infromation you’re after …

What’s the code you use to get this?

12

Hi Olaf,
Crazy day, just able to reply now.

The code that gets the Gateway is supposed to be this:

$VMHostNetworkStack = Get-VMHostNetworkStack -VMHost $_

That gets the VMK0 GW, but nadda for the VMK1.

I asked about this elsewhere as well, and it turns out its a bit harder to get that value. It was suggested to use esxcli to get that info, instead of querying the network stack, like so:

Get-VMHost -PipelineVariable esx | Get-VMHostNetworkAdapter -VMKernel -PipelineVariable vmk |
ForEach-Object -Process {
  $esxcli = Get-EsxCli -VMHost $esx -V2
  $if = $esxcli.network.ip.interface.ipv4.get.Invoke(@{interfacename = $vmk.Name })
  New-Object -TypeName PSObject -Property ([ordered]@{
    VMHost = $esx.Name
    VMK = $vmk.Name
    IP = $if.IPv4Address
    Mask = $if.IPv4Netmask
    GW = $if.Gateway
  })
}

That works and retrieves the info I wanted. But of course it creates other problems like how to just add that bit of info, the vmk1 GW address, into this other script, that you helped me out with:

$virtualcenter = 'a', 'b', 'c', 'd'
$clustername = 'e', 'f', 'g', 'h'

foreach ($vc in $virtualcenter) {

    Connect-VIServer -Server $vc
    foreach ($clus in $clustername) {
        Get-Cluster -Name "$clus" -ErrorAction SilentlyContinue | 
        Get-VMHost | 
        ForEach-Object {
            
            $clust = (Get-Cluster -Name "$clus" -ErrorAction SilentlyContinue)
            $vmhostnetworkadapter = Get-vmhostnetworkadapter -VMKernel -VMHost $_
            $vmhostnetworkstack = Get-VMHostNetworkStack -VMHost $_
            $ntpserver = get-vmhostntpserver -VMHost $_
            $ip6enabled = Get-VMHostNetwork -VMHost $_ | Select-Object IPv6Enabled
                       
            [PSCustomObject]@{
                Cluster         = ($clust.Name) -join ', '
                Hostname        = ($vmhostnetworkadapter.VMHost.GetValue(0)) -join ', '
                Device          = ($vmhostnetworkadapter.DeviceName) -join ', '
                DeviceIP        = ($vmhostnetworkadapter.IP) -join ', '
                DeviceMask      = ($vmhostnetworkadapter.SubnetMask) -join ', '
                Gateway         = ($vmhostnetworkstack.Gateway) -join ', '
                DnsAddress      = ($vmhostnetworkstack.DnsAddress) -join ', '
                NTP             = ($ntpserver) -join ', '
                PortGroup       = ($vmhostnetworkadapter.PortGroupName) -join ', '
                DNSSearchDomain = ($vmhostnetworkstack.DnsSearchDomain) -join ', '
                IP6             = ($ip6enabled.IPv6Enabled) -join ', '
                                
            } 
        } | Export-Csv -Append c:\temp\hosts.csv
    }  Disconnect-VIServer -Server $vc -Confirm:$false
}

This has everything I need, BUT that vmk1 GW :frowning: