Order by 2 attributes

Levio · March 8, 2022, 10:26pm

Hello Powershell world!!!
I am trying to filter out a csv file date by 2 attributes (Model and CostCenter) like this example:

Latitude 5420
0 5
41310 10

Optiplex 7060
0 1
41315 3
41320 6

Here is the part of the csv file.

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

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

Olaf · March 8, 2022, 10:32pm

Please do not post images of code or sample data as this is not helpful at all. This way we’re unable to copy your data to reproduce your issue. Instead post the original CSV text data formatted as code.

Thanks in advance.

If I got it right you can use

to filter whatever data you need from your CSV data.

And BTW: your code would be much easier to read when you added line breaks after the pipe symbols.

Levio · March 8, 2022, 10:39pm

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

Olaf · March 8, 2022, 10:48pm

Did you read my answer completely or just the last sentence?

Levio · March 8, 2022, 10:51pm

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

Olaf · March 8, 2022, 10:53pm

Cool. 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.

Levio · March 8, 2022, 11:11pm

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

Olaf · March 8, 2022, 11:15pm

It looks like you’re using Excel. Don’t do that! 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.

Levio · March 8, 2022, 11:20pm

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

Olaf · March 8, 2022, 11:32pm

Ah … I see … you’re using tab stops as delimiter … ok.

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

Levio · March 9, 2022, 12:04am

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

Olaf · March 9, 2022, 12:19am

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

Levio · March 9, 2022, 12:32am

Okay, I see…
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?

Olaf · March 9, 2022, 1:13am

Do you ever google things by yourself? 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. But anyway you can do whatever you need to do with your data. 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.

Levio · March 9, 2022, 1:46am

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

Levio · March 9, 2022, 5:15pm

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

Olaf · March 9, 2022, 8:05pm

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

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!?

Levio · March 9, 2022, 8:59pm

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

Olaf · March 9, 2022, 9:41pm

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?

Levio · March 9, 2022, 10:57pm

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

Topic		Replies	Views
PSCustom Object column ordering & Export-csv PowerShell Help	3	192	June 13, 2018
comparing 2 csv with different headers by 1 column value and displaying unique PowerShell Help	6	270	February 10, 2021
Import Column PowerShell Help	11	185	April 16, 2020
import scv to two demension array PowerShell Help	0	127	December 31, 2011
Csv fie sorting and comparing with another PowerShell Help	5	255	April 27, 2021

Order by 2 attributes

Related Topics