Grouping by department and counting users - Powershell

Jacob Durham
Jacob Durham used Ask the Experts™
on
I'm trying to output all users with a certain department and count them also using PowerShell.

The output that I'm trying to get is

Department1
User1
User2
User3

3 Users

Department2
User1
User2

2 users


Here's what I am using but all I get a single list of users:

Department1
User1
User2
User3
User1
User2


$businesslines = "Technology Support Svcs", "Development"


ForEach ($line in $businesslines)



{

get-aduser -filter {department -like $line -and enabled -eq $true} | Select-Object name 

}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Something like this?
$businessLines = "Technology Support Svcs", "Development"
$businessLines | ForEach-Object {
	Get-ADUser -Filter "(enabled -eq '$true') -and (department -like '$($_)')" -Property Department, DisplayName |
		Group-Object -Property Department |
		ForEach-Object {
			"Department '$($_.Name)' [$($_.Count) users]"
			$_.Group | ForEach-Object {
				"    $($_.SamAccountName) ($($_.DisplayName))"
			}
			''
		}
}

Open in new window

Jacob DurhamIT Support Analyst II (Lead Infrastructure Engineer)

Author

Commented:
Yes. Perfect.


Is it possible to export this to a CSV?
Tim CurwickAutomation Consultant

Commented:
It can be done a bit more efficiently with a single AD call. The AD filter does not support the -in operator, but we can dynamically build an equivalent. We're not worried about length, as the maximum filter size is 4 MB. Group-Object then groups the results for us, and we output each group object however we want.

$Depts = @(
    'Technology Support Svcs'
    'Development' )

$ADQuery = ( $Depts | ForEach-Object { "Department -eq '$_'" } ) -join ' -or '

Get-ADUser -Filter $ADQuery -Properties 'Department' |
    Group-Object -Property Department |
    ForEach-Object {
        $_.Name
        $_.Group | Select-Object -ExpandProperty Name
        ''
        [string]$_.Group.Count + ' users'
        '' } 

Open in new window

Jacob DurhamIT Support Analyst II (Lead Infrastructure Engineer)

Author

Commented:
Let me ask you this. Can I call every department that ANY user has? And then group them including a group for $null?
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
It wouldn't be PowerShell if you couldn't ...
Get-ADUser -Filter "enabled -eq '$true'" -ResultSetSize $null -Property Department, DisplayName |
	Group-Object -Property Department |
	Sort-Object -Property Name |
	ForEach-Object {
		If ($_.Name) {
			"Department '$($_.Name)' [$($_.Count) users]"
		} Else {
			"<No department> [$($_.Count) users]"
		}
		$_.Group | ForEach-Object {
			"    $($_.SamAccountName) ($($_.DisplayName))"
		}
		''
	}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial