Aggregate Count of 1 Objects in Group-By

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

$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
<colgroup><col style="width: 48pt;" span="2" width="64" /> </colgroup>
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

SampleData:

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

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. :wink:

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

My apologies to all the moderators out there :slight_smile:

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.

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             

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

I got it :slight_smile:

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

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  

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

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 https://docs.microsoft.com/en-us/dotnet/api/system.management.automation.whereoperatorselectionmode?view=powershellsdk-7.0.0

Thanks for explanation, Doug, this is great!

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