How to do it...

To export command output to a text file, use the Out-File cmdlet. To generate a report of mailboxes in a specific mailbox database that can be stored in a text file, use the following command:

Get-Mailbox | Select-Object Name,Alias | Out-File c:\report.txt

You can also save the output of the previous command as a CSV file that can then be opened and formatted in Microsoft Excel:

Get-Mailbox | Select-Object Name,Alias | 
  Export-CSV c:\report.csv –NoType

How it works...

The Out-File cmdlet is simply a redirection command that will export the output of your command to a plain text file. Perhaps one of the most useful features of this cmdlet is the ability to add data to the end of an existing file using the -Append parameter. This allows you to continuously update a text file when processing multiple objects or creating persistent log files or reports.

The Export-CSV cmdlet converts the object's output, by your command, into a collection of comma-separated values and stores them in a CSV file. When we ran the Get-Mailbox cmdlet in the previous example, we filtered the output, selecting only the Name and Alias properties. When exporting this output using Export-CSV, these property names are used for the column headers. Each object returned by the command will be represented in the CSV file as an individual row, therefore populating the Name and Alias columns with the associated data.

You may have noticed in the Export-CSV example that we used the -NoType switch parameter. This is commonly-used and is shorthand notation for the full parameter name -NoTypeInformation. If you do not specify this switch parameter, the first line of the CSV file will contain a header specifying the .NET Framework type of the object that was exported. This is rarely useful. If you end up with a strange-looking header in one of your reports, remember to run the command again using the -NoTypeInformation switch parameter.

T here's more...

One of the most common problems that Exchange administrators run into with Export-CSV is when exporting objects with multi-valued properties. Let's say we need to run a report that lists each mailbox and its associated e-mail addresses. The command would look something like the following:

Get-Mailbox | 
  Select-Object Name,EmailAddresses | 
    Export-CSV c:\report.csv -NoType

The problem here is that each mailbox can contain multiple e-mail addresses. When we select the EmailAddresses property, a multi-valued object is returned. The Export-CSV cmdlet does not understand how to handle this, and when you open the CSV file in Excel, you'll end up with a report that looks like the following:

From looking at the this screenshot, you can see that on the first line, we have our header names that match the properties selected during the export. In the first column, the Name property for each mailbox has been recorded correctly, but, as you can see, the values listed in the EmailAddresses column have a problem. Instead of the e-mail addresses, we get the .NET Framework type name of the multi-valued property. To get around this, we need to help the Export-CSV cmdlet understand what we are trying to do and specifically reference the data that needs to be exported.

One of the best ways to handle this is to use a calculated property and join each value of the multi-valued property as a single string:

Get-Mailbox | 
  Select-Object Name,@{n="Email";e={$_.EmailAddresses -Join ";"}} | 
    Export-CSV c:\report.csv -NoType

In this example, we've modified the previous command by creating a calculated property that will contain each e-mail address for the associated mailbox. Since we need to consolidate the EmailAddresses property da ta into a single item that can be exported, we use the -Join operator to create a string containing a list, separated by semi-colons, of every e-mail address associated with each mailbox. The command is then piped to the Export-CSV cmdlet, and the report is generated in a readable format that can be viewed in Excel:

As you can see in this screenshot, each e-mail address for a mailbox is now listed in the Email column and is separated using a semi-colon. Each address has an SMTP prefix associated with it. An SMTP prefix in all capital letters indicates that the address is the primary SMTP address for the mailbox. Any remaining secondary addresses use an SMTP prefix in lower case characters. If you do not want to export the pref ixes we can modify our code even further:

Get-Mailbox | 
  select-Object Name,
    @{n="Email";
      e={($_.EmailAddresses | %{$_.SmtpAddress}) -Join ";"}
     } | Export-CSV c:\report.csv -NoType

Here you can see that, within the expression of the calculated property, we're looping through the EmailAddresses collection and retrieving only the SmtpAddress, which does not include the SMTP prefix and returns only the e-mail addresses. Once the data is exported to a CSV file we can review it in Excel:

As you can see here, we now get each e-mail address associated with e ach mailbox without the SMTP prefix within the Email column of our CSV file.

See also