Aggregate Count of 1 Objects in Group-By

tluizzi · July 14, 2020, 3:29pm

Hello friends.  I am having troubling posting the sample audit data (maybe too much text?).

$auditSample | Group-Object -Property IRNumber -NoElement | Sort-Object -Property Count -Descending

Count Name                     
----- ----                     
   10 Not Found                
   10 Service Account          
    9 Local Admin Account      
    5 IR56242                  
    3 Domain Admin             
    3 IR29068                  
    1 IR44372                  
    1 IR42115                  
    1 IR27733                  
    1 IR243582                 
    1 IR243581

Trying to output:

$auditSample | Group-Object -Property IRNumber -NoElement | Sort-Object -Property Count -Descending

Count Name

10 Not Found
10 Service Account
9 Local Admin Account
5 IR56242
3 Domain Admin
3 IR29068
5 Unique IRs

tluizzi · July 14, 2020, 3:30pm

$auditSample

Name IRNumber 
---- -------- 
Last, First1 Not Found 
Last, First2 Not Found 
Last, First3 Not Found 
Last, First4 Not Found 
Last, First5 Not Found 
Last, First6 Not Found 
Last, First7 Not Found 
Last, First8 Not Found 
Last, First9 Not Found 
Last, First10 Not Found 
Last, First01 Service Account 
Last, First02 Service Account 
Last, First03 Service Account 
Last, First04 Service Account 
Last, First05 Service Account 
Last, First06 Service Account 
Last, First07 Service Account 
Last, First08 Service Account 
Last, First09 Service Account 
Last, First10 Service Account 
Last, First 20 Local Admin Account
Last, First 21 Local Admin Account
Last, First 22 Local Admin Account
Last, First 23 Local Admin Account
Last, First 24 Local Admin Account
Last, First 25 Local Admin Account
Last, First 26 Local Admin Account
Last, First 27 Local Admin Account
Last, First 28 Local Admin Account
Admin1 Domain Admin 
Admin2 Domain Admin 
Admin3 Domain Admin 
Last, First 36 IR56242 
Last, First 43 IR56242 
Last, First 46 IR56242 
Last, First 48 IR56242 
Last, First 55 IR56242 
Dux, Frank IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 70 IR56242 
Last, First 50 IR44372 
Last, First 33 IR42115 
Last, First 32 IR29068 
Last, First 32 IR29068 
Last, First 32 IR29068 
Last, First 40 IR27733 
Last, First 49 IR243582 
Last, First 37 IR243581

tluizzi · July 14, 2020, 3:33pm

Name	IRNumber
Last, First1	Not Found
Last, First2	Not Found
Last, First3	Not Found
Last, First4	Not Found
Last, First5	Not Found
Last, First6	Not Found
Last, First7	Not Found
Last, First8	Not Found
Last, First9	Not Found
Last, First10	Not Found
Last, First01	Service Account
Last, First02	Service Account
Last, First03	Service Account
Last, First04	Service Account
Last, First05	Service Account
Last, First06	Service Account
Last, First07	Service Account
Last, First08	Service Account
Last, First09	Service Account
Last, First10	Service Account
Last, First 20	Local Admin Account
Last, First 21	Local Admin Account
Last, First 22	Local Admin Account
Last, First 23	Local Admin Account
Last, First 24	Local Admin Account
Last, First 25	Local Admin Account
Last, First 26	Local Admin Account
Last, First 27	Local Admin Account
Last, First 28	Local Admin Account
Admin1	Domain Admin
Admin2	Domain Admin
Admin3	Domain Admin
Last, First 36	IR56242
Last, First 43	IR56242
Last, First 46	IR56242
Last, First 48	IR56242
Last, First 55	IR56242
Last, First 50	IR44372
Last, First 33	IR42115
Last, First 32	IR29068
Last, First 32	IR29068
Last, First 32	IR29068
Last, First 40	IR27733
Last, First 49	IR243582
Last, First 37	IR243581

tluizzi · July 14, 2020, 3:37pm

SampleData:

https://1drv.ms/u/s!AsS2bM4UhEA2iI9yTkUveVIC0J-uJQ?e=UGVZfq

Olaf · July 14, 2020, 3:39pm

You may ask in the forum Web Site Feedback & Assistance to release your post from moderation. I think most of the time the problem is when you edit your post to often in a shorter amount of time.

You don’t have to post all original data. Only as much to be able to reproduce your situation. I think 5 to 15 rows of data should be enough.

Olaf · July 14, 2020, 4:07pm

This provides an output very similar to your desired output I think:

$auditSample | 
Group-Object -Property IRNumber -NoElement | 
Sort-Object -Property Count -Descending |
ForEach-Object {
    if([INT]$_.Count -eq 1){
        $Count ++
    }
    else{
        $_
    }
}
'{0} UNique IRs' -f $Count

tluizzi · July 14, 2020, 4:36pm

My apologies to all the moderators out there

Thanks for the input, Olaf! I’m going to try to write a PSCustomObject - your foreach-object loop sparked an idea.

I intend to use this object as input into a pie chart or other visualization. I’m confident I can build a report using R and Markdown but I’d like to keep my script Powershell-pure. A few SYTLE tags with some CSS and flexbox classes go a long way.

rob-simmers · July 15, 2020, 4:33am

Are you just wanting non-unique groups?

PS C:\Users\rasim> 

$auditSample = Import-CSV -Path C:\Users\rasim\Downloads\sampleData.csv

$auditSample | 
    Group-Object -Property IRNumber -NoElement | 
        Where-Object {$_.Count -gt 1} |
            Sort-Object -Property Count -Descending

Count Name                     
----- ----                     
   10 Not Found                
   10 Service Account          
    9 Local Admin Account      
    5 IR56242                  
    3 Domain Admin             
    3 IR29068

tluizzi · July 15, 2020, 5:40am

Hi Rob,

I am trying aggregate all objects that have a count of 1 into one object:

Count Name                     
----- ----                     
   10 Not Found                
   10 Service Account          
    9 Local Admin Account      
    5 IR56242                  
    3 Domain Admin             
    3 IR29068
    5 Unique

tluizzi · July 15, 2020, 5:58am

I got it

$SummaryReportTest = $audit | Group-Object -Property IRNumber -NoElement

$SummaryReportTest.GetEnumerator() | ForEach-Object -Begin {$Begin = 0} -Process {

    if ($_.Count -gt 1) {

        [PSCustomObject]@{

            Name  = $_.Name

            Count = $_.Count

        }

    }

    else {

        $Begin +=1

    }

}`

-End {

    [PSCustomObject]@{

        Name  = "Unique"

        Count = $Begin

    }

} | Sort-Object -Property Count -Descending

Name                Count
----                -----
Not Found              99
Unique                 57
IR56242                18
IR29068                12
Service Account        10
Local Admin Account     9
Domain Admin            3

(I am using a different data set)

krzydoug · July 15, 2020, 6:27am

How about something like this?

$auditSample = import-csv C:\temp\sampleData.csv | Group-Object -Property IRNumber -NoElement
$multi,$singles = $auditSample.where({$_.count -gt 1},5)
$multi + [pscustomobject]@{Count=$singles.Count;Name="Unique IRs"} | select count,name | sort -Property count -Descending

Output

Count Name               
----- ----               
   10 Not Found          
   10 Service Account    
    9 Local Admin Account
    5 IR56242            
    5 Unique IRs         
    3 Domain Admin       
    3 IR29068

tluizzi · July 15, 2020, 7:17am

Thanks for feedback gentlemen. Doug, an issue I see with your solution is that you have to know ahead of time how many unique groups there are:

$multi,$singles = $auditSample.where({$_.count -gt 1},5)

Thanks for the code snipit - I wasn’t aware that you can assign two variables at once.

Here's another example I wrote:

@('a','a','a','a','a','a','b','b','b','b','b','c','c','c','d','e','f','g') | Group-Object | ForEach-Object -Begin {$Begin = 0} -Process {
if ($_.Count -gt 1) {
[PSCustomObject]@{
Name = $_.Name
Count = $_.Count
}
}
else {
$Begin +=1
}
}`
-End {
[PSCustomObject]@{
Name = "Unique"
Count = $Begin
}
} | Sort-Object -Property Name -Descending
Name Count

Unique 4

c 3

b 5

a 6

krzydoug · July 15, 2020, 8:09am

I could see why you’d think that, but that is not the case. The number 5 indicates the where operation mode “split” - here are all the modes

0 - Default
Return all matches.

1 - First
Stop processing after the first match.

2 - Last
Return the last matching element.

3 - SkipUntil
Skip until the condition is true, then return the rest.

4 - Until
Return elements until the condition is true then skip the rest. 

5 - Split
Return an array of two elements, first index is matched elements, second index is the remaining elements.

This should make it clearer to see.

$auditSample = import-csv C:\temp\sampleData.csv | Group-Object -Property IRNumber -NoElement
$multi,$singles = $auditSample.where({$_.count -gt 1},'SPLIT')
$multi + [pscustomobject]@{Count=$singles.Count;Name="Unique IRs"} | select count,name | sort -Property count -Descending

And for reference WhereOperatorSelectionMode Enum (System.Management.Automation) | Microsoft Docs

tluizzi · July 15, 2020, 8:23am

Thanks for explanation, Doug, this is great!

rob-simmers · July 16, 2020, 6:10am

That is an interesting solution Doug. It’s always fun when you can find a use case for an obscure functionality in programming.

Topic		Replies	Views
Sum Unique Items in Group-Object PowerShell Help	3	260	May 16, 2024
Aggregating Count of 1 Objects in Group-By PowerShell Help	1	137	May 16, 2024
Aggregate Count of 1 Objects in Group-By PowerShell Help	1	186	May 16, 2024
How to group object PowerShell Help	1	201	May 16, 2024
Help with Group-Object to find duplicates PowerShell Help	4	350	March 21, 2024

Aggregate Count of 1 Objects in Group-By

Trying to output:

Here's another example I wrote:

Related topics