Export-CSV results in only string length as content

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 2 years, 1 month ago.

  • Author
    Posts
  • #19703
    Profile photo of Karson Van Meeteren
    Karson Van Meeteren
    Participant

    I'm trying to get a quick and dirty report done, but I'm running into an issue getting the output into a format that can be easily manipulated. Enter the Export-CSV cmdlet. However, I'm getting output of just string lengths, rather than the actual object's content. Can anyone see what I need to do to get that working? I'm thinking I need to create custom expressions for the data, but where do I do that? Inside the foreach-object loop? Outside the foreach-object on the data variable?

    Add-PSSnapin VMware.VimAutomation.Core
    Connect-VIServer vCeneterserver.vmware.com
    $VMs = @()
    $VMs += Get-VM -Name * | where { $_.PowerState -eq "PoweredOn" -and $_.Guest.IPAddress -like "10.10.9*" -or $_.Guest.IPAddress -like "10.1.10*" -or $_.Guest.IPAddress -like "10.10.14*" -and $_.Guest.OSFullname -like "Microsoft*" -and $_.name -notlike "*Replica*"}
    $data = $VMs | ForEach-Object{
    	$os = Get-WmiObject -ComputerName $_ Win32_OperatingSystem | Select-Object -ExpandProperty Caption
    	$name = Get-VMGuest -VM $_ | Select-Object -ExpandProperty hostname
    	if ($os -like "*2012 R2*")
    	{
    		Get-VMGuest -VM $_ | Select-Object hostname
    		Get-VMGuest -VM $_ | Select-Object -ExpandProperty OSFullName
    		Invoke-Command -computername $_ -ScriptBlock { Get-NetAdapterBinding | Select-Object InterfaceAlias, ifDesc, Description, Enabled }
    		Get-WmiObject -ComputerName $_ win32_networkadapterconfiguration | Where-Object { $_.description -like "VMXNet*" } | Select Description,DNSServerSearchOrder, DNSDomain, DNSDomainSuffixSearchOrder, DomainDNSRegistrationEnabled, FullDNSRegistrationEnabled
    	}
    	elseif ($os -like "*2008 R2*" -or $os -like "*7*")
    	{
    		Get-VMGuest -VM $_ | Select-Object @{ name = "hostname"; Expression = { hostname } }
    		Get-VMGuest -VM $_ | Select-Object -ExpandProperty OSFullName
    		Get-NetworkAdapter -VM $_ | Select-Object -ExpandProperty type
    		Get-WmiObject -ComputerName $_ win32_networkadapterconfiguration | Where-Object { $_.description -like "VMXNet*" } | Select Description, DNSServerSearchOrder, DNSDomain, DNSDomainSuffixSearchOrder, DomainDNSRegistrationEnabled, FullDNSRegistrationEnabled
    		
    	}
    	else
    	{
    		"$name is not Windows Server 2012 R2, Windows Server 2008 R2, or Windows 7"
    		#Write-Host "OS is $os"
    	}
    	
    }#end foreach
    #TRY CUSTOM EXPRESSION like $data = $data | Select-Object @{name="VMName";Expression={hostname}}???
    $data | Export-Csv -Delimiter ',' -NoTypeInformation -path	c:\Users\myusername\Desktop\output.csv
    
  • #19705
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Inside your ForEach loop, you're outputting several different kinds of objects. Export-Csv is really intended to receive a collection of the same types of objects, so the columns in the CSV will correspond to the properties on those objects. The reason you're seeing just a Length property is that the first object in your $data array happens to be a string.

    Sounds like what you want to do is construct custom objects with some number of properties. Something along these lines:

    Add-PSSnapin VMware.VimAutomation.Core
    Connect-VIServer vCeneterserver.vmware.com
    $VMs = @(
        Get-VM -Name * |
        where {
            $_.PowerState -eq "PoweredOn" -and 
            ($_.Guest.IPAddress -like "10.10.9*" -or 
            $_.Guest.IPAddress -like "10.1.10*" -or 
            $_.Guest.IPAddress -like "10.10.14*" ) -and
            $_.Guest.OSFullname -like "Microsoft*" 
            -and $_.name -notlike "*Replica*"
        }
    )
    
    $data = $VMs | ForEach-Object{
        $os = Get-WmiObject -ComputerName $_ Win32_OperatingSystem | Select-Object -ExpandProperty Caption
        $vmGuest = Get-VMGuest -VM $_
        $name = $vmguest.hostname
    
        $properties = @{
            HostName = $name
            OSFullName = $vmGuest.OSFullName
            NetAdapterBindings = $null
            NetAdapterConfigs = $null
            NetAdapterType = $null
        }
    
        if ($os -like "*2012 R2*")
        {
            # Instead of outputting these values directly to the pipeline, we collect them in a hashtable that will be used to construct a single object that contains all of the information.
    
            # NOTE:  An object which contains properties that are arrays of other objects,
            # such as these bits of network adapter configuration, won't work very well with
            # a CSV file.  CSVs are flat files that work better with simple data structures;
            # you may find that something like Export-CliXml is better for what you're doing here.
    
            $properties['NetAdapterBindings'] = Invoke-Command -computername $_ -ScriptBlock { Get-NetAdapterBinding | Select-Object InterfaceAlias, ifDesc, Description, Enabled }
            
            $properties['NetAdapterConfigs'] = Get-WmiObject -ComputerName $_ win32_networkadapterconfiguration |
                                               Where-Object { $_.description -like "VMXNet*" } |
                                               Select Description,DNSServerSearchOrder, DNSDomain, DNSDomainSuffixSearchOrder, DomainDNSRegistrationEnabled, FullDNSRegistrationEnabled
            
        }
        elseif ($os -like "*2008 R2*" -or $os -like "*7*")
        {
            $properties['NetAdapterType'] = Get-NetworkAdapter -VM $_ | Select-Object -ExpandProperty type
            $properties['NetAdapterConfigs'] = Get-WmiObject -ComputerName $_ win32_networkadapterconfiguration |
                                               Where-Object { $_.description -like "VMXNet*" } |
                                               Select Description, DNSServerSearchOrder, DNSDomain, DNSDomainSuffixSearchOrder, DomainDNSRegistrationEnabled, FullDNSRegistrationEnabled
        }
        else
        {
            Write-Warning "$name is not Windows Server 2012 R2, Windows Server 2008 R2, or Windows 7"
            $properties = $null
        }
    
        if ($null -ne $properties)
        {
            # This is the only line that outputs an object in the ForEach loop now.
            New-Object psobject -Property $properties
        }
    }#end foreach
    
    $data | Export-Csv -Delimiter ',' -NoTypeInformation -path    c:\Users\myusername\Desktop\output.csv
    

    As I noted in the comments, you're probably still going to find that you don't like how the CSV turns out with this code, due to the way multiple objects (with other properties of their own) might wind up stored in the NetAdapterBindings / NetAdapterConfigs / NetAdapterType field.

You must be logged in to reply to this topic.