Andrew H Andrew H - 10 months ago 88
PowerShell Question

PowerShell Get-ADUser - Tally of attributes

I am looking at a way to list how many times an AD property matching a certain value appears against each user.

Specifically - Users in our AD have a Custom Attribute distinguishing their company. I need to run monthly reports that will display a list of all companies and how many users are listed.

This is as close as I have it - but it is not user friendly for our Accounts team.

import-csv c:\HostedCompanyList.csv -header ID |
foreach {
$total = (get-aduser -filter "(extensionattribute1 -eq '$($_.ID)') -and (extensionattribute2 -eq 'billed')" -properties *).count
write-host $total $_.ID

This is the content of HostedCompanyList.csv


My output shows as `

35 Company1
12 Company2
27 Company3

I'd like to output this information to a CSV or HTML file as opposed to displaying it on the PS window.

  • I appreciate this is likely already a very long winded way of running this query - could I perhaps have overlooked a much simpler method?

  • Bonus points if there's a way I can do this without having to import a CSV file at all - that would cut out the need to add additional entries into that list when new "companies" get added on or taken away!

Thank you for any help offered.


Untested, since I don't have the same extension attributes, but this shape:

  • query everyone, filter the billed people
  • group-object by company name
  • select properties and export to csv

should work with one query, no text file to read from, and csv export.

$filter = "extensionattribute2 -eq 'billed'"
$users = Get-ADUser -filter $filter -Property extensionattribute1, extensionattribute2

$groups = $users | Group -Property extensionattribute1
$groups | select Count, Name | Export-Csv out.csv -notypeinformation