Order by 2 attributes

Sorry for that.

#Import the csv file (Make sure no space in the name of the column title)
$ImportData = Import-Csv -path (Get-ChildItem C:\temp\Test\book2.csv | Select-Object -ExcludeProperty Fullname)
#Group by number of CostCenter and Model and count the number of model for each CostCenter
$ImportData |Select-Object CostCenter, Model | Group-Object Model  | 
Sort-Object Count -Descending 

Did you read my answer completely or just the last sentence? :smirk: :smirk: :wink: :smiley:

Yes. I am reading through the link you have shared and trying see if I understand the Where-Object command.

Cool. :+1:t4: :smiley: What I meant whas: will you provide the data you used in your question for us to play with it?

And if you do - please do not create a new post - edit your initial question. :wink:

I can’t find the way to import the csv file, so I just copied it.
Here is the desirable output Option1.
CsvdataOutput2

If not possible this option 2 also it fine too:
CsvdataOutput1

It looks like you’re using Excel. Don’t do that! :wink: If it’s a CSV file you can open the file with Notepad or even with VSCode. There you simply copy the text and paste it to the forum post formatted as code.

I am not using Excel.

Name	Model	CostCenter
CITY-50559	Latitude 5420	0
CITY-50619	Latitude 5420	0
CITY-50618	Latitude 5420	0
CITY-50253	Latitude E5470	0
CITY-24686	Latitude E7450	0
CITY-50259	Latitude E5470	0
CITY-50556	Latitude 5420	0
City-50493	Latitude 5400	41320
City-50526	Latitude 7410	41320
City-50291	Latitude 5480	41320
CITY-50549	Latitude 5420	41320
CITY-50323	Latitude 5480	41320
City-25082	Latitude 7410	41320
City-50483	Latitude 5400	41320
City-50498	Latitude 5400	41320
City-50451	Latitude 5400	41320
CITY-25106	Precision 3560	41320
CITY-50299	Latitude 5480	41410
City-50378	Latitude 5480	41410
CITY-50406	Latitude 5480	41410
CITY-50404	Latitude 5480	41410
City-50452	Latitude 5400	41410
CITY-50312	Latitude 5480	41410
City-30394	OptiPlex 7060	41410
City-50507	Latitude 5400	41410
CITY-50447	Latitude 5400	41410
City-50376	Latitude 5480	41410
CITY-50289	Latitude 5480	41520
CITY-50550	Latitude 5420	41520
CITY-50540	Latitude 5420	41520
City-50516	Latitude 7410	41520
CITY-30169	OptiPlex 9020	41520
CITY-50537	Latitude 5420	41520
City-50423	Latitude 5480	41520
CITY-50541	Latitude 5420	41520
CITY-50542	Latitude 5420	41520
CITY-50416	Latitude 5400	41520
CITY-50309	Latitude 5480	41520
City-50385	Latitude 5480	41520
CITY-50352	Latitude 7390 2-in-1	41520
CITY-30331	OptiPlex 7050	41520
CITY-50351	Latitude 7390 2-in-1	41520
City-30397	OptiPlex 7060	41520
CITY-50271	Latitude 5480	41610
CITY-50486	Latitude 5410	41610
CITY-24872	OptiPlex 7070	41610
CITY-50241	Latitude E5470	41610
CITY-50355	Latitude 5480	41610
CITY-50539	Latitude 5420	41610
City-50487	Latitude 5410	41610
City-50421	Latitude 5480	41610
City-24873	OptiPlex 7070	41610
City-50488	Latitude 5410	41610
City-50412	Latitude 5480	41610
City-50485	Latitude 5410	41610

Ah … I see … you’re using tab stops as delimiter … ok. :+1:t4: :smirk:

$CSVData = @'
Name	Model	CostCenter
CITY-50559	Latitude 5420	0
CITY-50619	Latitude 5420	0
CITY-50618	Latitude 5420	0
CITY-50253	Latitude E5470	0
CITY-24686	Latitude E7450	0
CITY-50259	Latitude E5470	0
CITY-50556	Latitude 5420	0
City-50493	Latitude 5400	41320
City-50526	Latitude 7410	41320
City-50291	Latitude 5480	41320
CITY-50549	Latitude 5420	41320
CITY-50323	Latitude 5480	41320
City-25082	Latitude 7410	41320
City-50483	Latitude 5400	41320
City-50498	Latitude 5400	41320
City-50451	Latitude 5400	41320
CITY-25106	Precision 3560	41320
CITY-50299	Latitude 5480	41410
City-50378	Latitude 5480	41410
CITY-50406	Latitude 5480	41410
CITY-50404	Latitude 5480	41410
City-50452	Latitude 5400	41410
CITY-50312	Latitude 5480	41410
City-30394	OptiPlex 7060	41410
City-50507	Latitude 5400	41410
CITY-50447	Latitude 5400	41410
City-50376	Latitude 5480	41410
CITY-50289	Latitude 5480	41520
CITY-50550	Latitude 5420	41520
CITY-50540	Latitude 5420	41520
City-50516	Latitude 7410	41520
CITY-30169	OptiPlex 9020	41520
CITY-50537	Latitude 5420	41520
City-50423	Latitude 5480	41520
CITY-50541	Latitude 5420	41520
CITY-50542	Latitude 5420	41520
CITY-50416	Latitude 5400	41520
CITY-50309	Latitude 5480	41520
City-50385	Latitude 5480	41520
CITY-50352	Latitude 7390 2-in-1	41520
CITY-30331	OptiPlex 7050	41520
CITY-50351	Latitude 7390 2-in-1	41520
City-30397	OptiPlex 7060	41520
CITY-50271	Latitude 5480	41610
CITY-50486	Latitude 5410	41610
CITY-24872	OptiPlex 7070	41610
CITY-50241	Latitude E5470	41610
CITY-50355	Latitude 5480	41610
CITY-50539	Latitude 5420	41610
City-50487	Latitude 5410	41610
City-50421	Latitude 5480	41610
City-24873	OptiPlex 7070	41610
City-50488	Latitude 5410	41610
City-50412	Latitude 5480	41610
City-50485	Latitude 5410	41610
'@ |
    ConvertFrom-Csv -Delimiter "`t"

A quick and dirty solution would be this:

$CSVData | 
    Group-Object -Property Model, CostCenter | 
        Select-Object -Property Name, Count

… probably enough to have a visual overview.

If you want to have it nice you can use a [PSCustomObject] like this:

$CSVData | 
    Group-Object -Property Model, CostCenter |
        ForEach-Object {
            [PSCustomObject]@{
                Model      = ($_.Name -split ',')[0].Trim()
                CostCenter = ($_.Name -split ',')[1].trim()
                Count      = $_.Count
            }
        }

Thank you Olaf!

But I am trying to invert to have CostCenter at the left and count the models, but the count does not work like yours.

$ImportData |
Group-Object -Property CostCenter, Model |
        ForEach-Object {
            [PSCustomObject]@{
                CostCenter      = ($_.Name -split ',')[0].Trim()
                Model = ($_.Name -split ',')[1].trim()
                Count      = $_.Count
            }
        }

I don’t know what that means. You just change the order in the [PSCustomObject]. The rest stays the same. If you want you can sort the table as you need it.

$CSVData | 
    Group-Object -Property Model, CostCenter |
        ForEach-Object {
            [PSCustomObject]@{
                CostCenter = ($_.Name -split ',')[1].trim()
                Model      = ($_.Name -split ',')[0].Trim()
                Count      = $_.Count
            }
        } |
            Sort-Object -Property CostCenter, Model

Notice that I just changed the order of the properties in the [PSCustomObject] - not the code of the properties!!

Okay, I see… :upside_down_face:
I exported it to csv, and there is message at the first row. Is that being expected?
CsvOutput

$CSVData  | 
    Group-Object -Property Model, CostCenter |
        ForEach-Object {
            [PSCustomObject]@{
                CostCenter = ($_.Name -split ',')[1].trim()
                Model      = ($_.Name -split ',')[0].Trim()
                Count      = $_.Count
            }
        } |
            Sort-Object -Property CostCenter, Model  |
        Export-Csv C:\Temp\Test\BookTest.csv

Is there a way to also group the CostCenter. Thinking about group by CostCenter and count the model in each CostCenter number?

1 Like

Do you ever google things by yourself? :smirk: Yes, it is expected when you use Export-Csv without the parameter -NoTypeInformation. But as you can see not Excel nor any other tool able to work with standard compliant CSV data will be bothered by that.

Since these two data sets have a one to one relation between CostCenter and Model the data will be the same. :point_up_2:t4: :wink: But anyway you can do whatever you need to do with your data. :wink: Play with the data and the grouping function. There’s nothing you have to fear.

If you need more advcanced reports you may take a look at the great module from Doug Finke ImportExcel.

You can even create Pivot tables with it. :wink:

Okay, thank you Olaf!
I will dig into that. :slight_smile:

Hey Olaf!
I am trying to follow your logic here to the data in this file, but the result is different. Sorry if I didn’t create a new topic.

$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	
$CSVData | 
    Group-Object -Property Hostname, QID, 'Vulnerability Title' |
        ForEach-Object {
            [PSCustomObject]@{
                Hostname = ($_.Name -split ',')[1].trim()
                QID      = ($_.Name -split ',')[0].Trim()
                VulnerabiltyName = ($_.Name -split ',')[0].Trim()
                Count      = $_.Count
            }
        } |
         Sort-Object -Property QID

" If " ??? :thinking: … why did you do it anyway when you already knew before that it might be wrong very likely? :smirk:

Your original data had 3 columns and we grouped for 2 of them. You new data have 10 columns and you try to group for 3 of them. I don’t understand what you’re trying to accomplish here but I’d say this logic does not fit for what you’re trying to do. For complex data analysis you should use the right tool. I’d say for that purpose it is not PowerShell. It might be an Excel Pivot table … but you don’t give enough information to decide.

Regardless of that - do you actually get the expected output from your CSV import? Actually you should have get an error message when you tried to process these data!?

You are right. I love the way your output displays so I was trying to just follow you script logic to see if I will have a nice output like yours.
I tried this by imptorting the data:

$CSVData = Import-Csv -path (Get-ChildItem C:\temp\data.csv | 
Select-Object -ExcludeProperty Fullname)
#Group by number of QID
$CSVData |Select-Object QID, 'Vulnerability Title' | 
Group-Object QID | Sort-Object Count -Descending 

But the result does not show the “Vulnerability title” nicely.
Output

Even at the risk of beeing misunderstood. I think you should do a big step back and start with learning the very basics of PowerShell first. The code you write does not make that much sense actually.

With this command you import the CSV file and then you remove EVERYTHING except for the strings contained in the column with the name “FullName”. And in the sample data you posted there isn’t even a column with hte name “FullName”.

With these next two lines you try to select two properties they do not exist anymore because you removed them in your last step.

What is it actually what you’re trying to do here? Could you explain in simple terms what you expect to come out of your code?

1 Like

Oh! Okay, I see.
I am trying to get this output result:

Count   QID       Vulnerability Title                                                                                                                                					
----- ----             -----                                                                                                                                             24 91846     Microsoft Windows Security Update for December 2021
   44 91866     Windows AppX Installer Spoofing Vulnerability
  215 105764    Microsoft Windows 10 Elevation of Privilege Vulnerability (Zero day) (DEPRECATED)
    6 91846     Microsoft Office and Microsoft Office Services and Web Apps Security Update - November 2021
   61 91857     Microsoft Office Security Update for December 2021

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
    }
}

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()
    }
}