PowerShell Gotcha – Export-CSV, Out-GridView and others

I came across this one a little while back and was hoping it would be fixed in V2, I haven’t seen anyone else mention it so I thought I would add it to my blog as a warning to other PowerShell freaks like me….

I have changed the example to be more universal so you get the point:

I was creating a list of my servers and how big the disks were, to do this I decided to create a collection of custom PS objects so I could add further details to the object later as below:

$Computers = "Localhost","TESTPC01"
$AllDisks = @()
Foreach ($comp in $Computers){
	$Disks = Get-WMIObject Win32_LogicalDisk -computer $comp -filter "DriveType=3" |Select SystemName,DeviceID,VolumeName,Size
	$Details = New-object PSObject
	$Details |Add-Member -Name Name -Value $comp -Membertype NoteProperty
	$DiskNum = 0
	Foreach ($disk in $disks){ 
		$Details | Add-Member -Name "Disk$($DiskNum)DeviceID" -MemberType NoteProperty -Value $Disk.DeviceID
		$Details | Add-Member -Name "Disk$($DiskNum)VolumeName" -MemberType NoteProperty -Value $disk.VolumeName
		$Details | Add-Member -Name "Disk$($DiskNum)Size(MB)" -MemberType NoteProperty -Value ([math]::Round($disk.Size / 1MB))
		$DiskNum++
	}
	$AllDisks += $Details
}

This was fine and worked as I expected, if I output the results to Format-List I had the desired results (Note my second server has 2 disks).

$AllDisks | Format-List

Name : Localhost

Disk0DeviceID : C:

Disk0VolumeName : System

Disk0Size(MB) : 20478

Name : TESTPC01

Disk0DeviceID : C:

Disk0VolumeName :

Disk0Size(MB) : 34703

Disk1DeviceID : D:

Disk1VolumeName : Data

Disk1Size(MB) : 140004

Great all results are fine you can clearly see server 1 has 1 disk and server 2 has 2 disks, so lets output them to Out-Gridview:

image

Spot the difference !  Yes, my second disk has disappeared and what’s more there was no error at the prompt, the same thing happens with Export-CSV.

From what I can tell these cmdlets base the row names on the first object in the collection, if the subsequent objects have more properties then it drops them with no warning to the prompt, something I found the hard way when exporting my results to CSV.

So how can we get around this ?  The easiest way I have found to do this is to list the objects with the most amount of disks first so they all have the most amount of rows, either this or you will have to determine how many disks the servers are going to have as a maximum and create blank spaces for all other records.

Have you had this issue before, is it just me scripting in a strange way, I would love to hear your thoughts on this.

Thanks goes to LucD for helping me work out what was going on here !

6 thoughts on “PowerShell Gotcha – Export-CSV, Out-GridView and others

  1. Wesley

    OK, I was able to figure out a method to only export what’s needed from every server with DiskType = 3.

    #Queries servers from ActiveDirectory
    $servers = Get-ADComputer -Filter * -Properties * | Where-Object OperatingSystem -Like ‘*Server*’ | Select-Object -expand Name
    #Created a blank array for Powershell to create objects in
    $Results = @()
    #Foreach loop to perform actions on every server within the ActiveDirectory query
    Foreach ($Server in $Servers){
    $results += gwmi win32_logicaldisk -Filter “DriveType = 3” -ComputerName $server |
    Select-Object `
    @{n=’Computer’;e={$_.__Server}},
    @{n=’DriveLetter’;e={$_.DeviceID}},
    @{n=’Free(GB)’;e={$_.Freespace / 1GB -as [int]}},
    @{n=’Size(GB)’;e={$_.Size / 1GB -as [int]}},
    @{n=’% Free’;e={$_.Freespace / $_.Size *100 -as [int]}},
    @{n=’Name’;e={$_.VolumeName}}
    }
    $Results | Export-Csv C:\Scripts\DiskSpace.csv -NoTypeInformation

  2. Wesley

    Thanks for this, I was having a lot of trouble getting the ForEach loop to work with a list of Servers that I grab via the following command: $servers = Get-ADComputer -Filter * -Properties * | Where-Object OperatingSystem -Like ‘*Server*’ | Select-Object -expand Name

    WIth your help I was able to get the information I need. I think there should be a way where we can specify an if statement of some sort stating the following: if $DiskNum -gt 1 give us the output. else out-null to avoid the errors within Powershell.
    Anyone think this could work? If so, would someone mind helping out with the syntax a bit? I’m a bit new to powershell but am heavily investing myself into it.

    Also – I export this to CSV, unfortunately the output is pretty ugly with the $DiskNum parameter included since it created an entirely new set of columns for each server that has a second disk. It would be so nice to get output that specifies an extra row with the separate disk information if a server did have 2 disks.

    Thanks again for publishing your work! Much appreciated.

  3. Steven Murawski

    Interesting topic. One workaround would be to pipe the output through
    $yourinput | Sort-Object -Property @{Expression={$_.psobject.properties | Measure-Object | Select-Object -ExpandProperty Count};Descending=$true} | out-gridview
    or something to that effect.

    I think the challenge lies in the fact that we are dealing with an object pipeline of dynamic objects and, as you’ve described, the property list is being determined by the first object through the pipeline, rather than being dynamically modified by the objects entering the pipeline.

  4. Virtu-Al

    @Jason Archer
    Thanks for the comment, glad to see its not just me ! looks like we used the same workaround too, my main purpose of the post is to let people know it happens as there is no output to the screen to tell you that it has dropped some rows.

  5. Jason Archer

    It has been talked about some, but usually you shouldn’t run into this as you should avoid variable amounts of properties on the same type objects.

    One work around is to create a disk count property with the number of disks, then sort that descending before piping to Export-CSV or Out-GridView.

    I was going to suggest creating an object for each disk that includes the computer name, then group by computer name. But it appears that they removed the group fuctionality from Out-GridView!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.