Sorting Powershell Array by Unique Column and newest Time

Example:

$list = @(
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:37"
    Host = "Host0815"
  }
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:31"
    Host = "Host2123"
  }
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:31"
    Host = "Host1"
  }
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:31"
    Host = "Host0815"
  }
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:25"
    Host = "Host0815"
  }
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:25"
    Host = "Host2123"
  }
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:25"
    Host = "Host1"
  }
  [PSCustomObject] @{
    Date = [DateTime] "10/31/2017 16:59:19"
    Host = "Host0815"
  }
)

$list | Sort-Object Host -Unique | Sort-Object Date -Descending

Output:

Date                  Host
----                  ----
10/31/2017 4:59:37 PM Host0815
10/31/2017 4:59:31 PM Host1
10/31/2017 4:59:31 PM Host2123

I created the sample input data ($list) with the Date column being an actual [DateTime] object. I don't know whether that's the case with your data (you may need to cast as such), but my short test outputs what you were expecting.

PowerShell unique-ing isn't built to handle this complexity. You can group by host first, then you can sort and select on a per host basis:

$array | group Host | % {$_ | select -ExpandProperty group | sort Date -Descending | select -First 1}


Date                Host    
----                ----
31.10.2017 16:59:37 Host0815    
31.10.2017 16:59:31 Host2123    
31.10.2017 16:59:31 Host1

first of all: Thanks for all your time! I tried to adapt your example and it worked, just did one "improvement":

$list = @(
[PSCustomObject] @{
Date = [DateTime]::ParseExact("31.10.2017 16:59:37","dd.MM.yyyy HH:mm:ss", $null)
Host = "Host1234"
}...

works too, which is pretty nice! But now i tried do adapt these changes to my Script and failed again:

$data = Get-Content "C:\heartbeat-log.txt" -tail 50

$array.Clear()

        ForEach ($line in $data)
        {
            $string = ($line -split "SPACER,") -split ","

              $array += @(
               [PSCustomObject] @{
                Date = [DateTime]::ParseExact($string[1], "dd.MM.yyyy HH:mm:ss", $null)
                SN = [string]$string[2]
                Host = [string]$string[3]
                Msg = [string]$string[4]
                Status = [string]$string[5]
                CpuCores = [int]$string[6]

              }
              )

        }

This is how my input Data looks like (SN is changing because random created String):

SPACER,31.10.2017 21:03:23,MZ58XJ6UBH,Host1,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:03:23,MZ58XJ6UBH,Host2123,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:03:29,MZ58XJ6UBH,Host0815,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:03:29,MZ58XJ6UBH,Host1,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:03:29,MZ58XJ6UBH,Host2123,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:03:35,MZ58XJ6UBH,Host0815,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:03:35,MZ58XJ6UBH,Host1,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:03:35,MZ58XJ6UBH,Host2123,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:08:55,PKRTVWLOMJ,Host0815,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:08:55,PKRTVWLOMJ,Host1,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:08:55,PKRTVWLOMJ,Host2123,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:09:01,PKRTVWLOMJ,Host0815,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:09:01,PKRTVWLOMJ,Host1,CustomMsg,CustomStatus,2
SPACER,31.10.2017 21:09:01,PKRTVWLOMJ,Host2123,CustomMsg,CustomStatus,2

Output with the your sort example:

$array | Sort-Object Host -Unique | Sort-Object Date -Descending | ft

Date                    SN            Host        Msg        Status        CpuCores
----                    --            ----        ---        ------        --------
31.10.2017 21:03:23     MZ58XJ6UBH    Host1       CustomMsg  CustomStatus  2
31.10.2017 19:54:06     XM6YCR5ED2    Host2123    CustomMsg  CustomStatus  2
31.10.2017 19:50:46     QJKWLD7901    Host0815    CustomMsg  CustomStatus  2

Expectation:

Date                    SN            Host        Msg        Status        CpuCores
----                    --            ----        ---        ------        --------
31.10.2017 21:09:01     PKRTVWLOMJ    Host1       CustomMsg  CustomStatus  2
31.10.2017 21:09:01     PKRTVWLOMJ    Host2123    CustomMsg  CustomStatus  2
31.10.2017 21:09:01     PKRTVWLOMJ    Host0815    CustomMsg  CustomStatus  2

Any suggestions why the "same" Array is working when manually filled but not in my version?

(hopefully formatted my post correct this time ;) )

Thank you very much!