Querying Exchange Mailboxes with Powershell Pt 2

In my previous blog post, I outlined some methods to query Exchange mailboxes using the get-mailbox cmdlet. Whilst the information obtained using this cmdlet is very useful, it does not contain all the information that is available for an Exchange mailbox.

There is another cmdlet available called Get-MailboxStatistics. It is used/applied in the same way as the Get-Mailbox cmdlet, but contains different fields or properties, and therefore, different information relating to the mailbox.

If you run the command Get-MailboxStatistics -identity "MailboxName" | fl it will display the mailbox statistics for that particular mailbox.

This information includes;

• Associated item count (ie. number of items within the mailbox)
• Deleted item count
• Last logon/logoff time
• Total size of items
• Total size of deleted items

So, for a practical example that you could potentially use to query this information. Let's say you want to query all your mailboxes to identify those that have more than 20,000 items within them. You could run the following command;

Get-Mailboxstatistics | Where {$_.ItemCount -gt "20000"} | Select DisplayName

You could replace $_.ItemCount with any of the other properties that are contained within the Get-MailboxStatistics cmdlet.

If you want the query to return more than just the DisplayName field for objects that match your query, you can add them after DisplayName and separate them with a comma (,), or if you want to return all properties then remove | Select DisplayName altogether.

You can also add | Export-CSV-path "C:\Export.csv" -notype to the end to export your query results to a .csv file.

Querying Exchange Mailboxes with Powershell

Exchange and Powershell go hand in hand - as it is essentially what Exchange is built on top of, and is what the Exchange Management Console (EMC) uses to execute commands you choose through the GUI. (Ever noticed you get the Powershell command line equivalent for commands you execute in EMC?)

One of the most common things an administrator will need to do is query all the mailboxes within the system for certain criteria, which I will explain and breakdown in this blog entry.

The command Get-Mailbox is one of the most useful and powerful commands you can use (for information gethering). It can be used to retrieve all the properties for all the mailboxes in your system, or can be tweaked to only return some of the properties for some of the mailboxes within your system.

If you execute the command Get-Mailbox on its own, you will probably be flooded with 1000 responses, followed by the following warning message;


WARNING: By default only the first 1000 items are returned. To change the number of items returned, specify the parameter "-ResultSize". To return all items specify "-ResultSize Unlimited" (Note: Returning all items may take a 
very long time and consume a large amount of memory depending on the actual number of items). It is not recommended to store the results in a variable; instead pipe the results to another task or script to perform batch changes

As per the message, the command on its own will only return the first 1000 results. You can change this by adding -ResultSize Unlimited to it, so it would look like this;

Get-Mailbox -ResultSize Unlimited

If you run this command it will run through and display the first few properties for every mailbox within your system. While this can be useful, the command can be further tweaked to run faster and provide more accurate/informative results.

To get all the properties for a single users mailbox, you can use the following command

Get-Mailbox -Identity "Morrissey, Peter"

You can replace what is between the " " with any identifier for the mailbox - such as Primary SMTP Address, Display Name or Alias.

When you run this command it will only display the first few properties as it is presenting the information in a table format. If you change the command to display in list format (by adding | fl), you get MUCH more information:

Get-Mailbox -Identity "Morrissey, Peter" | fl

Now you can view ALL the properties for an individual users mailbox. These properties apply to every mailbox within the system, so you can once again, modify the Get-Mailbox command to search on any of the properties you see in this list.

For example, if I wanted to query all the mailboxes within my system, and only return those whose 'Name' property contain the text "Peter", I would run the following command

Get-Mailbox -ResultSize Unlimited | Where {$_.Name -like "*Peter*"}

You still need to include -Resultsize Unlimited because although you aren't expecting more than 1000 results to be returned, you do want the Get-Mailbox command to query more than the first 1000 mailboxes, which it will not do if this omitted.

You can replace the $_.Name with any of the other properties for a mailbox - it just needs to have the $_. before it.

The -like operator can also be changed to use -eq (equal to) -ne (not equal to) -gt (greater than) -lt (less than) -le (less than or equal to) -ge (greater than or equal to)

Next you can fine tune the properties that you wish to display for each object whos name contains "Peter". Say you want to only display the DisplayName, Alias, Database and PrimarySMTPAddress properties for each of the results. You could use the following line

Get-Mailbox -ResultSize Unlimited | Where {$_.Name -like "*Peter*"} | Select DisplayName, Alias, Database, PrimarySMTPAddress

The benefit of doing this is that by retrieving fewer properties for each object, it speeds up the time it takes for the query to run. It also makes looking through the results much faster as you aren't reading over information you don't need. Once again, you can replace the properties I used with any of the properties available for each mailbox, as we discovered earlier.

Finally, you  can export the results of your query to a CSV file

Get-Mailbox -ResultSize Unlimited | Where {$_.Name -like "*Peter*"} | Select DisplayName, Alias, Database, PrimarySMTPAddress | Export-CSV -path "C:\Export.csv" -notype

Once you have the information in a CSV file you can attach it to an email and send it to yourself (or someone else) as per my previous blog post

Creating Custom Tables in Powershell

When working with scripts, it is often necessary to collect, store and present information in a table format. An example of this could be to export a number of properties from a users mailbox in Exchange into a table format.

For my example, I will run the following command to export all the properties of a mailbox (mine) to a variable called $UserList

$UserList = get-mailbox -ResultSize Unlimited | Where-Object {$_.DisplayName -eq "Morrissey, Peter"}

The next thing to do is to declare/create the table. It can be called anything you like, but I tend to use $report. The following line of code creates the table;

$report = @()

Next, you need to populate each row of your table with whatever fields you require. This would usually be done with a ForEach statement where you cycle through a list of objects, with numerous properties for each object.

In my example, the object list is stored in $UserList. I can run the command $UserList | fl to display the list of all the properties available for each object in my variable.

This ForEach command will cycle through each entry in $UserList (in my example it is only a single entry for my mailbox) and add whatever properties of that object I declare into the table (I have used DisplayName and Database) but you can use as many as you like - they just need to be separated by a semi colon (;)

ForEach ($User in $UserList)
             {
             $report += New-Object psobject -Property @{DisplayName=$($User.DisplayName);Database=$($User.Database)}

             }

This is a simple example and will create a table with two values for each row; DisplayName and Database.

Once you have populated all the rows you require in your table, you can then export it to a CSV file using the following command;

$report | select DisplayName, Database | Export-CSV -path "C:\ReportOutput.csv"

Once again, you will need to replace $report with whatever variable you used to declare your table. You also need to use the Select component to select all the table fields you wish to export into the CSV file - in my case it was the DisplayName and Database fields.

Finally, once the CSV file is exported, you can then attach it to an email and send it to yourself (or someone else) as outlined in my previous blog post.