Querying against Active Directory

Question

I'm the "script guy" at my work, which is a blessing and a curse... I've been tasked with generating a script of:

Can you create a script to pull all users that have a proxy address that is not an xyz.org email address out of Active Directory.

What would be the best approach? I'm not sure where the best place to look for this is. I'm researching it now, but since I don't know the best terms to use in my searches... It's taking me longer than I hoped to find something useful.

Utilizing this:

%SystemRoot%\SYSTEM32\rundll32.exe dsquery,OpenQueryWindow  

gives me a lightweight search tool that shows the basics of what I want... the only problem is it doesn't have a "like" or "contains" search function.

I need the same kinda search... but with the ability to filter out the proxy addresses that don't contain "xyz.org".

Answer 1

Your Question sounds like a job for the PowerShell.

If you don't have PowerShell installed, grab it here. Windows 7 and Server 2008 R2 have PowerShell already installed.

Get the Quest PowerShell Commands for Active Directory. This extension provides handy commands for scripting all kinds of AD operations.

Now for your query:

Note: you can keep this query much simpler, but I'll stretch it out a bit for better understanding.

# Get all Users and save it to a variable. 
#-SizeLimit 0 returns ALL users, otherwise only 1000 are returned.
# -IncludedProperties proxyaddresses makes sure the proxyaddresses are included

$allusers = Get-QADUser -SizeLimit 0 -IncludedProperties proxyaddresses

# Filter Users without a xyz.org email address and save them to a variable

$filteredusers = $allusers | where {!($_.proxyaddresses -like "*@xyz.org") -and $_.proxyaddresses}

# Sort and output the list

$filteredusers | sort Displayname | format-table Displayname, proxyaddresses -auto


# Other interesting fieldnames you could use with format-table
# GivenName, sn, mail, SamAccountName and many more
# 
# To get a list of all possible field names use: 
# $filteredusers[0] | format-list *
# This will return all fields of the first user saved in $filteredusers

Answer 2

This free AD reporting tool might help as you can generate a list to import into a spreadsheet app for more refining - I use it at work for managing our systems:

AD Info - Active Directory Reporting Tool

A flexible Active Directory reporting tool with over 140 built in reports as well as the option to create your own. With more flexability than other Active Directory reporting tools and a modern user friendly interface, AD Info lets you easily query your Active Directory domain for the information you need. Use one of the 140+ queries that come with the application or use the custom query designer to create your own query based on any attributes you choose.

http://www.cjwdev.co.uk/Software/ADReportingTool/Info.html