Skip to content

Using the PowerShell “Where statement” for creating filtered search | Office 365 and Exchange Online objects | Part 1#3

The current article series, includes three articles that are dedicated to the subject of – using PowerShell for “view” information about Office 365 (Azure Active Directory), and Exchange Online objects.

  1. The first article includes a basic introduction to the PowerShell “Where statement,” that we use as a “filter” for getting information about objects with specific characters.
  2. The second article, include many samples of – filtering the search results in Office 365 (Azure Active Directory) environment. The example that I provide, will be mostly related to the “Office 365 objects.”
  3. The third article includes many samples of – filtering search results in the Exchange Online environment. The example that I provide, will be related to the Exchange Online “Mailbox object,” and to the Exchange Online different type of recipients.

Use PowerShell to view Office 365 and Exchange Online objects | Article series

The article series includes the following articles:

  1. Using the PowerShell “Where statement” for creating filtered search | Office 365 and Exchange Online objects (this article)
  2. Use PowerShell for view Office 365 objects
  3. Use PowerShell for view Exchange Online objects

One of an essential needs of each Administrator who manages any infrastructure is, the ability to look (search) for a “specific object,” and perform some “action” on those objects.

The term – “objects.”

In Office 365 and Exchange Online, the term “objects” can be translated to various type objects such as -Azure Active Directory user accounts, Exchange Online mailboxes, Public Folders, Groups, and so on.

The term – performs some “action.”

The meaning of – performing some action can also be translated to an endless number of examples.

In most scenarios, we will need to look for specific “objects,” and then, perform an administrative task “on the objects” such as:

  • Update some property of the specified objects.
  • Enable a specific feature on the specified objects.
  • Export information about the specified objects to file such as Text, CSV, and HTML.

What to do with the “Filtered Search” results?

When we use PowerShell for performing a “Filtered Search,” we can use the results in one of the following ways:

1. Display search Results on the PowerShell console

In this scenario, our purpose is just to get an “answer” to a “question,” that will be displayed on the PowerShell console. For example, show me all the Office 365 that doesn’t have a license, show me all the Exchange Online mailboxes that considered “Shared mailbox” and so on.

2. Save the search result into a variable

In this scenario, our purpose is to “do something” for each object, that included in the “PowerShell Filtered Search.”

The first step is implemented by defining a PowerShell variable that serves as a “temporary store,” that will “hold” the result of the “PowerShell Filtered Search.”

In the next step, we ask from PowerShell to “fetch” each of the objects that included in the array of the “Filtered Search results” and then, “do something” to each of these objects.

For example

  1. Create a “PowerShell Filtered Search” of Office 365 users who doesn’t have a license.
  2. Store the information that we got from the “PowerShell Filtered Search” in a variable.
  3. Use additional PowerShell command, that will “access” the information that is stored in the variable that was created, fetch the information about the “user accounts,” and assign Office 365 licenses for each of the Office 365 users.

3. Export the search result to various types of files

In this scenario, our purpose is to save the information that we got from the “PowerShell Filtered Search” for later use, by exporting the information to various types of file formats such as – TEXT, CSV, and HTML.

What can I do with the Filtered search results

The tool that we use for the task of performing filtered search query, is the following PowerShell cmdlet.

Where-Object

Note: In the current article series, we use the shorter form – Where (instead of Where-Object). Another option we can use for defining the “Where statement” is by using the question mark characters, ‘?‘ instead of ‘Where‘ or ‘Where-Object‘.

The Where-Object PowerShell cmdlet, provides us a way, to filter the data returned by other PowerShell cmdlets.

In the following diagram, we can see the structure of PowerShell command, which uses the “Where statement.”

1. The PowerShell command that “Get” information about specific objects

This is the part that “fetch” the information about a specific object type such as Azure Active Directory user or Exchange Online mailbox (number 1).

For example – Get-Mailbox

2. The filter PowerShell “Where statement”

This is the part (number 2) in which we use the PowerShell “Where statement” for “filtering” the output that we get from the previous command.

If we want to use a technical term, we use the output that was “piped” from the previous PowerShell command.

Each Where statement starts and encloses the filter condition, by using curly braces.

3. The “$” character

The $_ notation (number 3), is used to represent the default object (that is, the object being transferred across the pipeline).

In other words, the $_ notation is used for representing each of the objects, that are included in the “array of objects” that we get from the filtered query results.

4. The specific property that we look for

This is the specific object property (number 4), that used by the “Where filter”.

For example, get Exchange Online mailboxes that they’re “RecipientTypeDetails” property, include a specific value.

Another example could be – filter information about users whose “Department” property includes a specific value.

5. The PowerShell comprehension operator

This is the “condition” that is used by the “Where statement” (number 5). The condition is defined by using a comprehension operator such as – Less than, Equal to, Not equal to, and so on.

In the following table, we can see information about PowerShell comprehension operators

-ltLess than
-leLess than or equal to
-gtGreater than
-geGreater than or equal to
-eqEqual to
-neNot equal to
-likeLike; uses wildcards for pattern matching

6. The specific value

This is the last part in which we define the specific value (number 6) that “included” in the object property.

The value could be a date, number, text string, and so on.

For example, display only users whose “Department” property includes the value “manager.”

An example of PowerShell “Where statement”

In the following example, we use the PowerShell Where statement” to get information about a specific type of Exchange Online mailboxes.

To get a list of a specific Exchange Online mailbox that considered as “Shared mailbox,” we will use the following PowerShell command:

Notice that in this example, we “pipe” the results that will be displayed by using the “FT” (File table) format and instruct PowerShell to display only specific properties of the objects such as – Alias and RecipientTypeDetails.

Get-Mailbox -ResultSize Unlimited | Where {$_.RecipientTypeDetails -eq "SharedMailbox"} | FT Alias,RecipientTypeDetails

The first PowerShell command is:

Get-Mailbox -ResultSize Unlimited

The result of this command is information about ALL the existing Exchange Online mailboxes.

In the second part of the PowerShell command (the part that appears after the Pipe), we use the PowerShell Where statement” in the following way:

We ask PowerShell to “look at the results” that we got in the previous steps (the array of all Exchange Online mailboxes), and “fetch” only Exchange Online mailboxes that answer the following condition:

Exchange Online mailboxes which their property named “RecipientTypeDetails” include a value that is equal to “Shared Mailbox”.

Using the PowerShell Where-Object statement -02

Using the PowerShell Where statement” with a combination of variable

In the following section, I would like to provide an example of a scenario in which we use the PowerShell “Where statement” with a combination of a variable.

Our mission is to provide a solution for the following scenario:

Assign Full access permissions to Exchange user, for all the Exchange mailboxes, which their user title is “Manager.”

In our example, we want to provide Adele, Full access permission to all the “Managers mailboxes.”

Part 1#5 – Get the information about specific Exchange object

In our example, we want to get a list of all Exchange Online users. The PowerShell command that we use is:

Get-User

Part 2#5 – Using “Where statement” to filter the information

In this part, we use the PowerShell Where statement to filter the result by getting information only about Exchange users whose title is equal (eq) to “Manager.”

Get-User | Where {$_.Title -eq "Manager"}

Part 3#5 – Creating a variable, and store information and the variable

In this part, we are creating a variable, that will store the result of the Where statement results.

The variable that we define is $Managers (in a PowerShell environment, we use the $ characters for specifying a variable).

The PowerShell command will look like this:

$Managers = Get-User | Where {$_.Title -eq "Manager"}

Part 4#5 – Using the PowerShell ForEach Loop operator

In this step, we use the PowerShell ForEach “Loop operator” to declare that we ask from PowerShell, to do something for each of the array members (each user whose title is equal to Manager).

The information that is stored in the variable named $Managers consider as an “array”, and each of the users that include in the results, can be considered as a “Member in the array.”

We will use the following PowerShell syntax:

Foreach ($Member in $Managers)

Notice that when using ForEach Loop operator, we use an additional variable. In our example, we define a variable named $Member
This variable is just a temporary variable, that is used as a logical cartridge, that will hold information on the specific Member, that we fetch from the array of objects that are stored in the variable named $Managers.

Part 5#5 – Executing the required command for each of the array members

In this step, we execute the PowerShell command, which will assign Full access permissions, to a username Adele, for each of the “members” included in the array.

In other words, we assign Adele Full access permissions to each Exchange Online mailbox, that her user considers as Manager.

The PowerShell command that we use is:

Add-MailboxPermission "Adele" -User $Member.name -AccessRights FullAccess -InheritanceType All

Notice that we didn’t relate to a specific Manager name but instead, use the $Member variable with the property “Name.”

The complete PowerShell command is:

$Managers = Get-User | Where { $_.Title -eq "Manager" }
Foreach ($Member in $Managers) {
    Add-MailboxPermission "Adele" -User $Member.name -AccessRights FullAccess -InheritanceType All
}

Using the PowerShell Where statement” for exporting specific data to a File.

In the last section, I would like to review the option of – Using the PowerShell “Where statement,” for export specific data to a File.

The most common file formats that are used:

  • Text file format
  • CSV (Comma-separated Value) file format
  • HTML file format

In the following diagram, we can see the format of the PowerShell command, that we use for exporting the “results” that we get from the filtered search query to the various file types.

Exporting the output from PowerShell command to a File

An example of the PowerShell command that will run a “Filtered search query” by using the PowerShell “Where statement,” could be a command in which we ask from PowerShell, to get information about specific types of Exchange Online mailboxes.

For example, Exchange Online mailboxes that consider as “Shared mailboxes” and then, export the information to various types of file formats.

In the following section, we can see an example of the PowerShell command syntax that we use for exporting data to various types of file formats:

Example 1 – Export PowerShell filtered search to a Text file.

Get-Mailbox -ResultSize Unlimited | Where {$_.RecipientTypeDetails -eq "SharedMailbox"} | FT Alias,RecipientTypeDetails | Out-File c:\temp\file.txt

Example 2 – Export PowerShell filtered search to, a CSV file.

Get-Mailbox -ResultSize Unlimited | Where {$_.RecipientTypeDetails -eq "SharedMailbox"} | FT Alias,RecipientTypeDetails | Export-CSV c:\temp\file.csv

Example 3 – Export PowerShell filtered search to, an HTML file.

Get-Mailbox -ResultSize Unlimited | Where {$_.RecipientTypeDetails -eq "SharedMailbox"} | FT Alias,RecipientTypeDetails | ConvertTo-Html c:\temp\file.html

In the following section, we can see an example of the results.

In my example, I exported the search result in three different file types. Each of the File type formats has his “Advantages.”

Using the PowerShell Where statement for exporting specific data to a File -01

This is an example of the information that was exported to a Text file.

Using the PowerShell Where statement for exporting specific data to a File -Text file 02

This is an example of the information that was exported to a CSV file.

The advantage of using the CSV file format is, that we can use applications such as Microsoft Excel, that will help us to view the information in a more convenient way, filter specific columns and so on.

Using the PowerShell Where statement for exporting specific data to a File -csv file 04

This is an example of the information that was exported to an HTML file.

The advantage of using the HTML file format is, that it’s more suitable for “Reports,” and we can display the exported information using a simple web browser.

The example that appears in the following screenshot, is not the “standard HTML” PowerShell output. The “original HTML format output” is more basic.

In my example, I use PowerShell script that adds an additional design format to the HTML output.

In case you want to use this more “designed HTML output,” you can use the PowerShell scripts that I have added to the Other articles in the series.

Using the PowerShell Where statement for exporting specific data to a File -HTML file 03

In the next article, we will look into Use PowerShell for view Office 365 objects.

o365info Team

o365info Team

This article was written by our team of experienced IT architects, consultants, and engineers.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *