Windows PowerShell is Great at Filtering Data


The Office 365 Admin center provides several different ways to filter your data; that is, several different ways to quickly and easily locate a targeted subset of information. For example, Exchange makes it easy to filter on practically any property of a user mailbox. Need a list of mailboxes for all the users who live in the city of Bloomington? No problem:

Exchange Online Advanced search dialog box.

The Exchange Admin center also lets you combine filter criteria; for example, you can find the mailboxes for all the people who live in Bloomington and who work in the Finance department. What more could you want? Nothing, right?

Right. Well, then again, there might be times when you’d like to find the mailboxes of people who live in Bloomington or in San Diego. Or maybe mailboxes for all the people who don’t live in Bloomington. Or – well, you get the idea. But you probably can’t create those kind of filters in the Admin center, can you?

No, you can’t. But you can create those kind of filters by using Windows PowerShell. For example, you say that you want a list of mailboxes for all the people who live in Bloomington or in San Diego? Try this command:

Get-User | Where-Object {$_.RecipientTypeDetails -eq "UserMailbox" -and ($_.City -eq "San Diego" -or $_.City -eq "Bloomington")} | Select DisplayName, City

And, yes, that is a bit more complicated than some of the other commands we’ve seen so far. But that’s OK: once you know what you’re doing commands like that are actually pretty easy to figure out. And best of all, they work:

DisplayName                              City
-----------                              ----
Alex Darrow                              San Diego
Bonnie Kearney                           San Diego
Julian Isla                              Bloomington
Rob Young                                Bloomington
Zrinka Makovac                           San Diego

And what about listing all the mailboxes for people who live anywhere except Bloomington? That’s even easier:

Get-User | Where-Object {$_.RecipientTypeDetails -eq "UserMailbox" -and $_.City -ne "Bloomington"} | Select DisplayName, City

As you can see, not a Bloomington in the bunch:

DisplayName                               City
-----------                               ----
MOD Administrator                         Redmond
Alex Darrow                               San Diego
Allie Bellew                              Bellevue
Anne Wallace                              Louisville
Aziz Hassouneh                            Cairo
Belinda Newman                            Charlotte
Bonnie Kearney                            San Diego
David Longmuir                            Waukesha
Denis Dehenne                             Birmingham
Garret Vargas                             Seattle
Garth Fort                                Tulsa
Janet Schorr                              Bellevue

And here’s something else: with Windows PowerShell you can use wildcard characters in your filters. Is that important? It can be. Suppose you’re looking for a user, and all you can remember is that their last name was Anderson, or maybe Henderson. Or maybe it was Jorgenson. Anyone, it was something like that.

So how do you track down that user? Well, in the Admin center you could use the search tool and carry out three different searches:

  • One for Anderson

  • One for Henderson

  • One for Jorgenson

That works. It will take awhile. But it works.

So are we saying that it would be easier to locate this user by using Windows PowerShell? That’s exactly what we’re saying:

Get-User -Filter '{LastName -like "*son"}'

Very nice.

Next: Windows PowerShell Makes It Easy to Print or Save Data

See Also

Six Reasons Why You Might Want to Use Windows PowerShell to Manage Office 365