Andrew H Andrew H - 4 months ago 22
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

ID
Company1
Company2
Company3


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.

Answer

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
Comments