Exporting reports to text and CSV files
One of the added benefits of the Exchange Management Shell is the ability to run very detailed and customizable reports. With the hundreds of Get-* cmdlets provided between Windows PowerShell and the Exchange Management Shell, the reporting capabilities are almost endless. In this recipe, we'll cover how to export command output to plain text and CSV files that can be used to report on various resources throughout your Exchange environment.
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.
Tip
You can also use the Add-Content, Set-Content, and Clear-Content cmdlets to add, replace, or remove data from files.
By your command, the Export-CSV cmdlet converts the object's output 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 inpidual 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 a 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.
There's more...
One of the most common problems that Exchange administrators run into with Export-CSV is when exporting objects with multivalued 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 multivalued object is returned. The Export-CSV cmdlet does not understand how to handle this, and when you import the CSV file in PowerShell, you'll end up with a CSV file that looks like the following:
In the preceding 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, there is a problem with the values listed in the EmailAddresses column. Instead of the e-mail addresses, we get the .NET Framework type name of the multivalued 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 multivalued property as a single string:
Get-Mailbox | ` Select-Object Name,@{n="Email";e={$_.EmailAddresses -Join ";"}}` | Export-CSV c:\report1.csv -NoType
In this example, we 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 data into a single item that can be exported, we use the -Join operator to create a string containing a list, separated by semicolons, 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 using the Import-CSV cmdlet:
As you can see in the preceding screenshot, each e-mail address for a mailbox is now listed in the Email column and is separated using a semicolon. 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 will use an SMTP prefix in lowercase characters. If you do not want to export the prefixes, we can make further modifications to our code as follows:
Get-Mailbox | ` select-Object Name, ` @{n="Email"; ` e={($_.EmailAddresses | %{$_.SmtpAddress}) -Join ";"} ` } | Export-CSV c:\report2.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 cmdlet, 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 using the Import-CSV cmdlet:
As you can see here, we now get each e-mail address associated with each mailbox, without the SMTP prefix within the Email column of our CSV file.