Powershell Multi-dimensional arrays sorting

Question

I have a script which processes all our SQL Servers. The script has several functions and I have an error routine which records the server name, function name and error mesage into a 500 row x 3 column array. At the end of the script I want to sort this array into server name sequence. Several posts have suggested all I need to do is pipe the array to the sort-object cmdlet, but when I do this every element of my array is replaced with system.Object[]. NB. The array filling before the function is just an example of what my array looks like

$global:ErrorCount = 0
$global:ErrArray = new-object 'object[,]' 500,3

$global:ErrArray[1,00]= "SV000004"
$global:ErrArray[1,01]= "ProcessServers"
$global:ErrArray[1,02]= "The server was not found or was not accessible."

$global:ErrArray[2,00]= "BOSWEB02"
$global:ErrArray[2,01]= "GetDatabases"
$global:ErrArray[2,02]= "Database Status = Shutdown"

$global:ErrArray[3,00]= "SATURN"
$global:ErrArray[3,01]= "GetDatabases"
$global:ErrArray[3,02]= "Database Status = Shutdown"

$global:ErrArray[4,00]= "BOSWEB02"
$global:ErrArray[4,01]= "GetSystemInfo"
$global:ErrArray[4,02]= "Access is denied"

$global:ErrorCount = 4

Function DisplayErrors
{
    Write-Host "`nBefore:-`n"

    for ( $iLoop=1; $iLoop -le $global:ErrorCount; $iLoop++)
    {
        "{0,-14}  {1,-18}  {2,-80}" -f 
          $global:ErrArray[$iLoop,0], $global:ErrArray[$iLoop,1], 
          $global:ErrArray[$iLoop,2]
    }

    $Sorted = $global:ErrArray | Sort-Object @{Expression={$_[0]}}

    Write-Host "`nAfter:-`n"    

    for ( $iLoop=1; $iLoop -le $global:ErrorCount; $iLoop++)
    {
        "{0,-14}  {1,-18}  {2,-80}" -f 
          $Sorted[$iLoop,0], $Sorted[$iLoop,1], $Sorted[$iLoop,2]
    }
}  

DisplayErrors

Output looks like this:-

Before:-

SV000004        ProcessServers      The server was not found or was not accessible.                                 
BOSWEB02        GetDatabases        Database Status = Shutdown                                                      
SATURN          GetDatabases        Database Status = Shutdown                                                      
BOSWEB02        GetSystemInfo       Access is denied                                                                

After:-

System.Object[]  System.Object[]     System.Object[]                                                                 
System.Object[]  System.Object[]     System.Object[]                                                                 
System.Object[]  System.Object[]     System.Object[]                                                                 
System.Object[]  System.Object[]     System.Object[]  

Can anyone tell me what I am doing wrong here ?

Many thanks :-)

Answer 1

Just the code, ma'am

Caveat: Quicksorts do exist.

I changed the ErrArray to be 0-based in its implementation --

$global:ErrArray[0,00]= "SV000004"

... but otherwise insert the below function, trading the line...

$Sorted = $global:ErrArray | Sort-Object @{Expression={$_[0]}}

... for...

$Sorted = Order-2dArray $global:ErrArray;

# Naming sorts stinks: http://stackoverflow.com/questions/27173621/
# NOTE: **EVERY** array entry in [0,x] must be non-null if -ColCount not set.
# NOTE: **EVERY** array entry in [y,0] must be non-null. Thanks.
Function Order-2dArray
{
    param
    (
        [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)]
        $ArrayToSort,
        [int]$ColCount = -1,  # I hate multi-dim PS arrays.
        [int]$SortColumn = 0, # Would be a touch faster if this were a constant.
        [switch][alias("desc")]$Descending # Ascend by default.
    )

    $fullCount = $ArrayToSort.Length;
    if ($ColCount -le 0) # if not given, guess by checking for $nulls.
    {
        $ColCount=0;while ($ArrayToSort[0,$ColCount] -ne $null) {
            $ColCount++;
        }
    }

    $RowCount = $fullCount / $ColCount;
    $nullRowCap = $RowCount-1;  # 1-based to 0-based
    while ($ArrayToSort[$nullRowCap,0] -eq $null) { $nullRowCap--; }

    $itemToPlace = $nullRowCap;
    for ($i=0;$i -lt $nullRowCap;$i++)
    {
        for ($j=0;$j -lt $itemToPlace;$j++)
        {
            # This dual-check method for optionally descending is not efficient.
            if (($ArrayToSort[$j,$SortColumn] -gt $ArrayToSort[($j+1),$SortColumn] -and !$Descending) -or
                ($ArrayToSort[$j,$SortColumn] -lt $ArrayToSort[($j+1),$SortColumn] -and $Descending))
            {
                for($k=0;$k -lt $ColCount;$k++) {
                    $hold = $ArrayToSort[$j,$k];
                    $ArrayToSort[$j,$k] = $ArrayToSort[($j+1),$k];
                    $ArrayToSort[($j+1),$k] = $hold;
                }
            }
        }
        $itemToPlace--;
    }
    Write-Host -BackgroundColor Magenta $ArrayToSort.GetType();
    , $ArrayToSort; # see http://stackoverflow.com/questions/7833317/ for comma use
}

---

Why didn't the original work?

[Realizing this is sorta a zombie question --] This was a little too big for a comment, so I'll create an answer to explain why you need to use what @CB. or @vonPryz suggest.

First, see the comment at the end. The OP's method would have worked, afaict, in PowerShell 1.0. Not now, apparently.

In brief, you can no longer access the "values in the first row of a two-dimensional array" in PowerShell. You need to have an array of objects if you want to group each "row" of entries into an object and pass that array to Sort-Object. So you can choose an array of arrays, as in CB.'s answer, or an array of New-Object objects, as in vonPryz -- or whatever feels best. But it has to be an array of X, not a two-dimensional array.

To show what's up, try

PS> $global:ErrArray[0]

... at any point. You'll get...

You cannot index into a 2 dimensional array with index [0].
At line:1 char:1
+ $global:ErrArray[0]
+ ~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NeedMultidimensionalIndex

That is, there is no $global:ErrArray[0] that returns three values, at least not in the way you're envisioning it. The first dimension of an array does not group the three objects in the second dimension. It's more like 1500 addresses than 500 floors of three addresses each.

---

Okay, so why doesn't the code error out?

Try this in your code to confirm why it doesn't throw an error:

$Sorted = $global:ErrArray | Sort-Object @{
    Expression={
        $_[0];
        Write-Host $_[0];
    }
}

The results might surprise you -- it's the first character of each string.

S
P
T
B
G
D
S
G
D
B
G
A

Since there's no concept of "a single row of a multidimensional array", PowerShell is unpacking all of the entries from your two-dimension array into a single-dimension array of strings, and with $_[0], you're grabbing the first character in the strings-as-arrays. !! (If you had integers in each of those spots instead of strings, the code, I believe, would've broken here.)

That means you're not just sorting all the non-null values from any spot in your two-dimensional array, you're only sorting by the first letter of each!

$Sorted[$iLoop,0] works because (and I agree this is insanely counterintuitive) PowerShell gives you the $iLoop-th and 0-th values in the now single-dimensioned $Sorted array. It thinks you're passing in two indices for the new, flattened array, and the value returned is an array with those two values in it.

Try these two lines in place of your -f one:

"{0} {1}" -f $Sorted[$iLoop,0]
"{0} {1}" -f $Sorted[$iLoop,0], $Sorted[$iLoop,1], $Sorted[$iLoop,2]

The first one "works" -- it gives you the $iLoopth value of $Sorted for {0} and the 0th for {1} since you essentially passed in two strings. They might have been passed in as an array, but PowerShell "helpfully" unpacks that into two strings. Perfect, sorta.

The second line gets back to the System.Object[] you're seeing, since it's... An array of three arrays (since it believes you're using array-comma notation: X,Y,Z), each of which has a length of two (from the two indices it thinks it sees from $iLoop, 0, etc). In this case, the first two elements in the array get shoved into {0} and {1}, have ToString() implicitly called, and become System.Object[].

Let's confirm it's returning arrays when we have stuff like $Sorted[$iLoop,0]. Here, I'm using PowerGUI and dropping a breakpoint in after the sorting to debug:

[DBG]: PS> $t2 = $Sorted[$iLoop,0], $Sorted[$iLoop,1], $Sorted[$iLoop,2]
[DBG]: PS> $t2.Length
3
[DBG]: PS> $t2[0]
BOSWEB02
BOSWEB02
[DBG]: PS> $t2[0][0]
BOSWEB02

So you're sending three Object[] arrays with two strings a piece into your format call. Calling ToString() on an Object[] gives you that nasty System.Object[].

If you want to sort a two dimensional array by what's in the 0th element of each first dimension's second dimension [sic, I think], you'd probably have to write it out old-style, Bubble or Quicksort or what-have-you, like I've done in the first section of this answer. (Better to use what CB. suggests, I think, if you can.)

So that all makes some sense. To PowerShell you have 1500 addresses, but no real grouping of them by their "x" value. Don't worry, it didn't immediately make sense to me either (thus my stumbling over this question).

EDIT: Just to make things more fun, it appears arrays did work as you expect in PowerShell v1.0: http://arstechnica.com/civis/viewtopic.php?t=52603 but see how The Scripting Guy is already doing arrays of arrays for multiple dimensions in 2011.

Answer 2

Creating the array in this way ( a jagged array: array[ ][ ] ) your sort-object can works:

$global:ErrArray += ,@("SV000004","ProcessServers","The server was not found or was not accessible.")
$global:ErrArray += ,@("BOSWEB02","GetDatabases","Database Status = Shutdown")
$global:ErrArray += ,@("SATURN","GetDatabases","Database Status = Shutdown")
$global:ErrArray += ,@("BOSWEB02","GetSystemInfo","Access is denied" )

Function DisplayErrors
{
    Write-Host "`nBefore:-`n"

    foreach ( $server in $global:Errarray)
    {
    write-host $server 
    }

    $sorted = $global:ErrArray | Sort-Object @{Expression={$_[0]}}

    Write-Host "`nAfter:-`n"    

   foreach ( $server in $sorted)
    {
        write-host $server    
    }
}  

DisplayErrors

or more like your code:

$global:ErrArray += ,@("SV000004","ProcessServers","The server was not found or was not accessible.")
$global:ErrArray += ,@("BOSWEB02","GetDatabases","Database Status = Shutdown")
$global:ErrArray += ,@("SATURN","GetDatabases","Database Status = Shutdown")
$global:ErrArray += ,@("BOSWEB02","GetSystemInfo","Access is denied" )



Function DisplayErrors
{
    Write-Host "`nBefore:-`n"

for ( $iLoop=0; $iLoop -lt $global:errarray.count; $iLoop++)
{
    "{0,-14}  {1,-18}  {2,-80}" -f   $global:ErrArray[$iLoop][0], $global:ErrArray[$iLoop][1], $global:ErrArray[$iLoop][2]
}

$sorted = $global:ErrArray | Sort-Object @{Expression={$_[0]}}

Write-Host "`nAfter:-`n"    

for ( $iLoop=0; $iLoop -lt $sorted.count; $iLoop++)
    {
        "{0,-14}  {1,-18}  {2,-80}" -f   $Sorted[$iLoop][0], $Sorted[$iLoop][1], $Sorted[$iLoop][2]
    }
}  

DisplayErrors

Answer 3

Whlist I don't know why Powershell mucks up the array, I might have more a Powershellish work-around. Let's create a custom object that contains data and a one-dimensional array of those. Sorting is simple.

# Custom error object
function CreateErrorObject {
    param ([string]$server, [string]$proc, [string]$message)
    $objError = New-Object System.Object
    $objError | Add-Member -type NoteProperty -name Server -value $server
    $objError | Add-Member -type NoteProperty -name Proc -value $proc
    $objError | Add-Member -type NoteProperty -name Message -value $message
    $objError
}

$errors = @() # Empty array

# Populate error objects some way
$errors += CreateErrorObject "SV000004" "ProcessServers" "The server was not found or was not accessible"
$errors += CreateErrorObject "BOSWEB02" "GetDatabases" "Database Status = Shutdown"
$errors += CreateErrorObject "SATURN" "GetDatabases" "Database Status = Shutdown"
$errors += CreateErrorObject "BOSWEB02" "GetSystemInfo" "Access is denied"


$errors # Unsorted list
$errors | sort -Property server # sort by server property
$errors | sort -Property proc # sort by proc property
$errors | sort -Property message # sort by message property