Powershell Group-Object - filter using multiple objects

Question

I have a CSV of devices that are missing security updates along with the date the update was released and kb number.

devicename,date,kb
Desktop1,9/12/17,KB4011055
Desktop1,9/12/17,KB4038866
Desktop2,9/12/17,KB4011055
Desktop2,6/13/17,KB3203467

I am trying to compile a list of devices that are missing updates that have been released in the past 30 days but exclude devices that are also missing older updates. So in the example above, the only device I want is Desktop 1. I know I could do something like this to see devices that are under that 30 day window but that would still include devices that have other entries which are greater than 30 days.

$AllDevices | Where-Object {[datetime]$_.date_released -gt ((get-date).adddays(-30))} 

I was thinking I could use Group-Object devicename to group all the devices together but I'm not sure how to check the dates from there. Any ideas?

Answer 1

^{The assumption is that $AllDevices was assigned the output from something like
Import-Csv c:\path\to\some.csv and that PSv3+ is used.}

$AllDevices | Group-Object devicename | Where-Object {
  -not ([datetime[]] $_.Group.date -le (Get-Date).AddDays(-30))
} | Select-Object @{ l='devicename'; e='Name' }, @{ l='kbs'; e={ $_.Group.kb } }

With the sample input, this yields:

devicename kbs                   
---------- ---                   
Desktop1   {KB4011055, KB4038866}

Explanation:

• Group-Object devicename groups all input objects by device name, which outputs a collection of [Microsoft.PowerShell.Commands.GroupInfo] instances each representing all input objects sharing a given device name (e.g., Desktop1) - see Get-Help Group-Object.

• The Where-Object call is then used to weed out groups that contain objects whose date is older than 30 days.

  • [datetime[]] $_.Group.date creates an array of date-time objects [datetime[]] from the date-time strings (.date) of every member of the group $_.Group.

    • Note that $_.Group is the collection of input objects that make up the group, and even though .date is applied directly to $_.Group, the .date property is accessed on each collection member and the results are collected in an array - this handy shortcut syntax is called member-access enumeration and was introduced in PSv3.

  • -le (Get-Date).AddDays(-30) filters that array to only return members whose dates are older than 30 days; note that -le applied to an array-valued LHS returns a filtered subarray, not a Boolean.

  • -not negates the result of the -le comparison, which forces interpretation of the filtered array as a Boolean, which evaluates to $False if the array is empty, and $True otherwise; in other words: if one or more group members have dates older than 30 days, the -le comparison evaluates to $True as a Boolean, which -not negates.
    This results in groups (and therfore devices) containing at least 1 older-than-30-days date getting removed from further pipeline processing.

• Select-Object then receives only those group objects whose members all have dates that fall within the last 30 days, and uses calculated properties (via hashtable literals (@{...}) with standardized entries) to construct the output objects:

  • A group object's .Name property contains the value of the grouping property/ies passed to Group-Object, which in this case is the input objects' devicename property; @{ l='devicename'; e='Name' } simply renames the .Name property back to devicename.

  • @{ l='kbs'; e={ $_.Group.kb } } then constructs a kbs property that contains the array of kb values from the members of each group, retrieved by member-access enumeration via a script block { ... }

  • Note that Select-Object outputs [pscustomobject] instances containing only the explicitly defined properties; in this case, devicename and kbs.

Answer 2

I propose other solution:

import-csv "C:\temp\test.csv" | 
    select *, @{N="Date";E={[DateTime]$_.Date}} -ExcludeProperty "Date" |
               group devicename | 
                %{
                    if (($_.Group | where Date -le (Get-Date).AddDays(-30)).Count -eq 0)
                    {
                       $LastUpdate=$_.Group | sort Date, kb -Descending | select -First 1
                        [pscustomobject]@{
                        DeviceName=$LastUpdate.DeviceName
                        DateLastUpdate=$LastUpdate.Date
                        LastUpdate=$LastUpdate.Kb
                        UpdateList=$_.Group.Kb -join ', '
                        Group=$_.Group
                        } 
                    }

                }