Exchange-PowerShell export reports to text and CSV files

Exchange Management Shell One of the benefits is that you can give us a detailed and customizable report. with the number of Exchange Management in the Shell and Windows system PowerShell provides hundreds of get-* commands, and the reporting feature is quite powerful and customizable. In this section, reports are exported through the Exchange environment through commands to CSV and text files.

How do you do it?

export the output to a text file that you can use Out-file command. To generate a mailbox report to a text file under a specific database, you can use the following command:

Get-mailbox–database "Mailbox Database 1014271532" | Select-object Name,alias | Out-file C:\Report01.txt

650) this.width=650; "title=" 01.png "style=" height:121px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m01/59/79/wkiom1ttkxvbflkeaacc9hrr_2o286.jpg "width=" 720 "height=" 121 "alt=" Wkiom1ttkxvbflkeaacc9hrr_2o286.jpg "/>

The command runs successfully to the report path to view the file:

650) this.width=650; "title=" 02.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M02/59/79/ Wkiom1ttkxvz9iybaab7pgxl_ga702.jpg "alt=" Wkiom1ttkxvz9iybaab7pgxl_ga702.jpg "/>

Open the report file:

650) this.width=650; "title=" 03.png "style=" height:364px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m02/59/75/wkiol1ttlajyvmahaaiyccyq6ke732.jpg "width=" 720 "height=" 364 "alt=" Wkiol1ttlajyvmahaaiyccyq6ke732.jpg "/>

Output information can also be output to a CSV in the file:

Get-mailbox–database "Mailbox Database 1014271532" | Select-object Name,alias | Export-csv C:\report01.csv–notype

650) this.width=650; "title=" 04.png "style=" height:115px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m02/59/79/wkiom1ttkxvjn6kuaadb3taav8c607.jpg "width=" 720 "height=" "alt=" Wkiom1ttkxvjn6kuaadb3taav8c607.jpg "/>

View CSV The file information is as follows:

650) this.width=650; "title=" 05.png "style=" height:522px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m00/59/75/wkiol1ttlaisnrg2aaoczysyogk419.jpg "width=" 720 "height=" 522 "alt=" Wkiol1ttlaisnrg2aaoczysyogk419.jpg "/>

However, we should note that the support for Chinese is not good, the follow-up will give how to solve the problem of Chinese incompatibility.

Out-file The command is simply a redirected command that outputs information to a plain-text file. You can also use the -append parameter to attach data to an existing file. This allows you to constantly update files or create persistent logs or reports.

ps : You can use add-content set-content command to append, overwrite, and delete the data in the file.

Export-csv The output of the object is converted through a command, and the data is stored in a comma delimiter CSV file. In the previous example, when we ran the get-mailbox command, the output was filtered, only the Name and Alias properties were selected, These properties will be separate as A column in the CSV file.

in the example above, we may notice the use of the -notype switch parameters. If you do not use this switch parameter, we first look at the effect:

650) this.width=650; "title=" 06.png "style=" height:63px;width:720px; "border=" 0 "hspace=" 0 "src=" http://s3.51cto.com /wyfs02/m00/59/79/wkiom1ttkxzykh7zaab6vvrpy3s881.jpg "width=" 720 "height=" "alt=" Wkiom1ttkxzykh7zaab6vvrpy3s881.jpg "/>

running the CSV File information:

650) this.width=650; "title=" 07.png "style=" height:526px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m01/59/75/wkiol1ttlajsxq42aapvjkqclkw380.jpg "width=" 720 "height=" 526 "alt=" Wkiol1ttlajsxq42aapvjkqclkw380.jpg "/>

can see that in CSV The first line of the file has a header row that specifies the . NET Framework the object of type. Therefore, the attachment -notype parameter can avoid this header information.

More over Command reference:

one of the more common problems is our Exchange administrators are using Export-csv , the output information contains a number of object properties. Here is an example of exporting each mailbox and the associated e-mail address. The command is as follows:

Get-mailbox |

Select-object name,emailaddresses |

Export-csv C:\report03.csv–notype

650) this.width=650; "title=" 08.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M01/59/79/wKiom1TTkxyB1_ Mzaacbejbh_w4258.jpg "alt=" Wkiom1ttkxyb1_mzaacbejbh_w4258.jpg "/>

But now there is a problem, each mailbox can contain multiple e-mail addresses. When we select the emailaddresses property, a multivalued object is returned. The export-csv command does not understand how to handle this problem, so when you open the CSV file, the report you might see is the following information:

650) this.width=650; "title=" 09.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M00/59/75/ Wkiol1ttlctd0zapaaq7dkgv0co985.jpg "alt=" Wkiol1ttlctd0zapaaq7dkgv0co985.jpg "/>

as you can see, the title in the first row is in line with our filter criteria. And in the first column, the name of each mailbox is also the correct record, but the problem is emailaddresses column has a problem, we see not the e-mail address, in order to solve the problem, the command is as follows:

Get-mailbox |

Select-object name,@{n= "Email"; e={$_. Emailaddresses-join ";"}} |

Export-csv C:\report04.csv–notype

650) this.width=650; "title=" 10.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M02/59/75/ Wkiol1ttlcsyalb9aadfnmgod-8362.jpg "alt=" Wkiol1ttlcsyalb9aadfnmgod-8362.jpg "/>

Look at the results shown:

650) this.width=650; "title=" 11.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M02/59/79/ Wkiom1ttkzjctkzuaaoi_mmfity069.jpg "alt=" Wkiom1ttkzjctkzuaaoi_mmfity069.jpg "/>

because CSV show the garbled, it is a little unbearable, it is time to solve the Chinese garbled, I can output CSV The file specifies the encoding format, the command format is as follows:

Get-mailbox |

Select-object name,@{n= "Email"; e={$_. Emailaddresses-join ";"}} |

Export-csv c:\report07.csv-Encoding Utf8–notype

650) this.width=650; "title=" 12.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M00/59/75/ Wkiol1ttlcsjywrbaadchfkqcqw277.jpg "alt=" Wkiol1ttlcsjywrbaadchfkqcqw277.jpg "/>

Look at the output, Chinese has shown normal:

650) this.width=650; "title=" 13.png "style=" height:514px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m00/59/79/wkiom1ttkzihkijoaau2bm8wsjm356.jpg "width=" 720 "height=" 514 "alt=" Wkiom1ttkzihkijoaau2bm8wsjm356.jpg "/>

Below I add a few email addresses in the user account to see the effect:

Add Zhang San this mailbox user a SMTP email address,

650) this.width=650; "title=" 14.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M01/59/75/ Wkiol1ttlcxdzr73aakymshnmsm334.jpg "alt=" Wkiol1ttlcxdzr73aakymshnmsm334.jpg "/>

run the following command to generate CSV File:

Get-mailbox |

Select-object name,@{n= "Email"; e={$_. Emailaddresses-join ";"}} |

Export-csv c:\report08.csv-Encoding Utf8-notype

650) this.width=650; "title=" 15.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M01/59/79/ Wkiom1ttkzigxceoaadrln4oswe493.jpg "alt=" Wkiom1ttkzigxceoaadrln4oswe493.jpg "/>

See CSV File information:

650) this.width=650; "title=" 16.png "style=" height:514px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m02/59/75/wkiol1ttlcxbmsnyaawgf3weyrg424.jpg "width=" 720 "height=" 514 "alt=" Wkiol1ttlcxbmsnyaawgf3weyrg424.jpg "/>

We should be aware of the e-mail column, email address of each mailbox, and our address ( lync ) all appear in email columns, and are separated by semicolons. But each email address is preceded by a smtp prefix. Uppercase smtp SMTP e-mail address, other e-mail prefixes are lowercase smtp prefix, if you do not want to export the prefix, we can use the following code:

Get-mailbox |

Select-object Name,

@{n= "Email";

e={($_. emailaddresses | %{$_. SMTPAddress})-join ";"}

} | Export-csv c:\report09.csv-Encoding Utf8–notype

650) this.width=650; "title=" 17.png "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M02/59/79/ Wkiom1ttkznd4fqsaaeoxymac3w011.jpg "alt=" Wkiom1ttkznd4fqsaaeoxymac3w011.jpg "/>

See CSV information, no prefix information has been

650) this.width=650; "title=" 18.png "style=" height:554px;width:720px; "border=" 0 "hspace=" 0 "src=" http:// S3.51cto.com/wyfs02/m00/59/75/wkiol1ttlcxy1midaasxpadypre223.jpg "width=" 720 "height=" 554 "alt=" Wkiol1ttlcxy1midaasxpadypre223.jpg "/>

This article from "Robin's Home" blog, declined reprint!

Exchange-PowerShell export reports to text and CSV files