Control query results with Exchange Management Shell's Format command
Get details on how to use Exchange Management Shell's Format command and Pipe symbol to perform complex queries in Exchange Server 2007.
When Exchange Server displays query results, it only displays certain attributes by default. For example, if you enter the Get-User command alone, only the username and recipient type are displayed.
If you enter the Get-Mailbox command, only the mailbox name, alias, server name and prohibited send quota attributes are displayed. In both cases, there are additional attributes that aren't shown. This raises the question of how to get Exchange to display these hidden attributes. The Format command can be used for this action.
The Format command enables administrators to control the information that's displayed when executing a query. For example, suppose that when you entered the Get-User command, you wanted to see the user's name and title. To do so, use the following command:
Get-User | Format-List Name, Title
Figure 4 shows the output of this command. Note: I don't populate the Title attribute in my own organization, so this field remains blank for each user.
Figure 4. This is the output from the Get-User | Format-List Name, Title command. (Click on image for enlarged view.)
Although the command appears simple, there are some complications. Notice that I referred to the Format command, even though I actually entered the Format-List command. This is because List is actually a parameter of the Format command.
When you follow the Format command with the –List parameter, Exchange displays query results in list form. Each attribute is listed on its own line. Another option is to display the results in table format. To do so, repeat the previous command, but substitute the word Table for List:
Get-User | Format-Table Name, Title
When you enter this command, results will be displayed horizontally in table format, as shown in Figure 5.
Figure 5. You can display query results in a table. (Click on image for enlarged view.)
If you look at the top row of results on this screen, you can see that Exchange has set up columns for the username and title.
You can specify the attributes that you want to display immediately following the Format-List or Format-Table command. For this example, I'm using two attributes. You can specify as many different attributes as you want to make the query display the information that you want.
Additionally, the Pipe symbol (|) separates the Get-User portion of the command from the Format portion of the command, but it is more involved than that. The Pipe symbol has a special meaning in the Exchange Management Shell. When two commands are separated by a Pipe, the second command processes the results of the first command.
For example, we issued the Get-User command, and then used the pipe symbol. Next, we issued the Format command, which formats the results of the Get-User command. If you wanted to move all mailboxes out of one mailbox database and into another, you could separate the various commands with the pipe character and perform the entire operation using one line of code.
In another example, if you wanted to move all mailboxes out of a database called Northwind and into the Contoso database on the server, the command would look like:
Get-MailboxDatabase Northwind | Get-Mailbox | Move-Mailbox –TargetDatabase Contoso
The first portion of this command uses the Get-MailboxDatabase command. If you were to enter this command alone, it would return a list of mailbox databases on the current server. Notice that the previous command names a specific database. If you have a database with a name that consists of more than one word, you must enclose the database name in apostrophes.
Generally, entering the Get-MailboxDatabase command produces fairly unimpressive results, even if you specify a database name. If you look at Figure 6, you can see that this command returns a list of the mailbox databases residing on my server. When I entered the command again, but specified a database name, I received some information on the database.
Figure 6. The Get-MailboxDatabase command doesn't produce impressive results when used alone. (Click on image for enlarged view.)
The next portion of the command involves using the Get-Mailbox command. As noted previously, this command returns information on every mailbox in the Exchange organization. Here, however, the Get-Mailbox command takes into account the results of the command initialized before it. The result is that only mailboxes residing within the specified database are returned as results.
Figure 7 shows an appended version of the Get-Mailbox command. As a result, only mailboxes residing in the specified storage group are displayed.
Figure 7. The Get-Mailbox command returns only a list of mailboxes found in the specified databases. (Click on image for enlarged view.)
There is one last part of this command string -- the Move-Mailbox command. In this case, we must only specify the name of the target database parameter. Because it received the information from a previous command string, the Move-Mailbox command knows which mailboxes it should move, and where those mailboxes reside.
You can use this single line of text for several purposes. The Get-MailboxDatabase command was used in conjunction with the Get-Mailbox command to return a list of mailboxes residing in a specific database. Suppose that you wanted to enforce a quota on the mailbox so that users were prohibited from sending new messages once their mailbox reaches 500 MB.
To accomplish this, you can use the Set-Mailbox command as the last portion of the command string. In the past, we've worked primarily with the Get verb, which queries the Active Directory and returns requested information. The Set verb works similarly to Get, except that it writes information to the Active Directory, instead of retrieving it. Figure 8 shows how this command works.
Figure 8. The Set-Mailbox command is used to make changes to a mailbox. (Click on image for enlarged view.)
As the figure shows, I have initially issued the following command:
Get-MailboxDatabase 'mailbox database' | Get Mailbox
This command showed the one mailbox that exists in the database, and also shows that the mailbox doesn't currently have a quota associated with it. I reissued the command, but appended the Set-Mailbox –ProhibitSendQuote 500MB command to the end. This assigns a 500 MB quota to any mailboxes located during the query. The actual command is:
Get-MailboxDatabase 'mailbox database' | Get Mailbox | Set-Mailbox –ProhibitSendQuota 500MB
Once the command completed, I reissued the first command to verify that the change was made. Then I made sure that the quota was applied.
A PRIMER ON THE EXCHANGE 2007 EXCHANGE MANAGEMENT SHELL
Part 1: How to use the Exchange Management Shell command syntax
Part 2: How to use the Exchange Management Shell Filter command
Part 3: Control query results with the EMS Format command
Part 4: How to test Exchange Management Shell commands
|ABOUT THE AUTHOR:|
| Brien M. Posey, MCSE
Brien M. Posey, MCSE, has previously received Microsoft's MVP award for Exchange Server, Windows Server and Internet Information Server (IIS). Brien has served as CIO for a nationwide chain of hospitals and was once responsible for the Department of Information Management at Fort Knox. As a freelance technical writer, Brien has written for Microsoft, TechTarget, CNET, ZDNet, MSD2D, Relevant Technologies and other technology companies. You can visit Brien's personal Web site at http://www.brienposey.com.