Order by 2 attributes

PowerShell Help
21

Start simple, break it down into steps.

Look at the output, see it’s a grouping that contains its own properties, not yours… but it’s own

$CSVData | 
    Group-Object QID

Inspect it, what do you see? What can you learn?

$CSVData | 
    Group-Object QID | Get-Member

Test

$CSVData | 
    Group-Object QID | Select-Object group

$CSVData | 
    Group-Object QID | Select-Object -ExpandProperty group

Do you see your properties? They are contained within each grouping?

So now think about what you want. You want the count, a property of the group object, the QID, which is both a property of the group object (Name) and also a property of the group property of the group object, and vulnerability Title, which is only a property of the group property of the group object.

So couple of different ways to build your custom object. I also prefer [PSCustomObject] like Olaf.

$GroupedData = $CSVData | Group-Object QID
    
$GroupedData | ForEach-Object {
    [PSCustomObject]@{
        Count                 = $_.count
        QID                   = $_.name
        'Vulnerability Title' = $_.group.'vulnerability title' | Select-Object -First 1
    }
}
22

I just can echo what Doug said and encourage you to start step by step trying to understand what each single command actually does before moving on to the next. Do not run the complete code. Run it until the first pipe - look at it, inspect the variables you filled in and when you understood it add the next step until the next pipe and look at it, inspect it … and so on. That’s how we all do it. :wink:

But since I already started messing around with your data I will give you the complete code once more. Assuming you don’t mind loosing all the other data from your input CSV you can do it like this:

$CSVData = 
@"
Ticket 	State	Due Date	Hostname	Severity	QID	Vulnerability Title	Modified	Created	Resolved		
344426	Open	3/12/2022	CITY-24570	Vulnerability - level 4	91866	Microsoft Windows Codecs Library HEVC Video and VP9 Extensions Remote Code Execution (RCE) Vulnerability for February 2022	02/10/2022 at 11:42:01 AM (GMT-0600)	02/10/2022 at 11:42:01 AM (GMT-0600)			
204292	Open	02/10/2022 (Overdue)	CITY-24570	Vulnerability - level 5	105764	EOL/Obsolete Software: Microsoft SQL Server Compact 3.5 Detected	01/11/2022 at 02:53:45 AM (GMT-0600)	05/14/2020 at 09:42:00 AM (GMT-0500)			
335003	Open	02/17/2022 (Overdue)	CITY-24576	Vulnerability - level 4 - Disabled	91846	Microsoft Windows Security Update for December 2021	01/18/2022 at 07:56:02 AM (GMT-0600)	12/16/2021 at 08:22:26 AM (GMT-0600)			
344945	Open	3/18/2022	CITY-24576	Vulnerability - level 4 - Disabled	91857	Microsoft Windows Security Update for February 2022	02/16/2022 at 10:09:42 AM (GMT-0600)	02/16/2022 at 10:09:42 AM (GMT-0600)			
341338	Open	02/17/2022 (Overdue)	CITY-24576	Vulnerability - level 4 - Disabled	91852	Microsoft Hypertext Transfer Protocol (HTTP) Protocol Stack Remote Code Execution (RCE) Vulnerability for January 2022	01/18/2022 at 07:56:02 AM (GMT-0600)	01/18/2022 at 07:56:02 AM (GMT-0600)			
218666	Open	07/09/2021 (Overdue)	CITY-24576	Vulnerability - level 5 - Disabled	105764	EOL/Obsolete Software: Microsoft SQL Server Compact 3.5 Detected	06/09/2021 at 09:52:34 AM (GMT-0500)	07/20/2020 at 05:55:00 PM (GMT-0500)			
340008	Open	02/11/2022 (Overdue)	CITY-24576	Vulnerability - level 4 - Disabled	376232	Microsoft Windows Internet Key Exchange (IKE) Extension Multiple Vulnerabilities for January 2022	01/12/2022 at 11:35:47 AM (GMT-0600)	01/12/2022 at 11:35:47 AM (GMT-0600)			
340007	Open	02/11/2022 (Overdue)	CITY-24576	Vulnerability - level 4 - Disabled	91851	Microsoft Windows Security Update for January 2022	01/12/2022 at 11:35:47 AM (GMT-0600)	01/12/2022 at 11:35:47 AM (GMT-0600)				
"@ | 
    ConvertFrom-Csv -Delimiter "`t" 

$CSVData | 
Group-Object -Property QID, 'Vulnerability Title' |
ForEach-Object {
    [PSCustomObject]@{
        Count            = $_.Count
        QID              = ($_.Name -split ',')[0].Trim()
        VulnerabiltyName = ($_.Name -split ',')[1].Trim()
    }
}
23

Awesome!!! That is exactly what I was looking for. Thank you Krzydoug! Thank you Olaf!
Well explained.
Just confirm. If I choose to count one property, the other properties are group objects.

$GroupedData = $CSVData | Group-Object QID
$GroupedData | ForEach-Object {
    [PSCustomObject]@{
        Count                 = $_.count
        QID                   = $_.name
        Severity = $_.group.'Severity' | Select-Object -First 1
    }
}
24

Thank you!!! Perfectly great!
One last question:
What the [0] and [1] mean?

25

Think about it simply!! You’re taking the entire csv contents and grouping on 1, 2, etc properties. For instance your QID. So picture in your head making a new bag for each new QID. So first line, no bags yet so make new bag and give it a couple of “fields”. One is going to be count, which is how many items we put in this bag. Next will be the Name, which will contain the list of properties you grouped on.** There is also a field called “Group” which is the contents of the bag. Each time we find another QID that already has a bag, just drop that entire object into it.

** Since there is 1 to 1 relation between QID and Vulnerability Title, you don’t need to group on more than QID (or the other… for that matter)

26

Merci beaucoup (French). :slight_smile:
Danke (German). :wink:
Thank you for the explanation. Now I understand with that analogy.