Microsoft

How to Combine PowerShell Cmdlet Output in a Single CSV File

Microsoft - The Essential Guide to Microsoft Teams End-User Engagement
The Essential Guide to Microsoft Teams End-User Engagement

We take you through 10 best practices, considerations, and suggestions that can enrich your Microsoft Teams deployment and ensure both end-user adoption and engagement.

Get the Guide

If you have been working with Office 365, you have probably been extracting mailbox data and exporting it to CSV for various reporting tasks. You may have also gone further and attempted to pipeline data from multiple Cmdlets into Export-CSV. Let’s use the following scriptlet as an example:
[sourcecode language=”powershell”]
Get-Mailbox Test.user1@company.com | Get-MailboxStatistics | Select Alias,Displayname,TotalItemSize,ArchiveStatus | Export-CSV C:\test.csv
[/sourcecode]
Now the problem with the previous scriptlet is that the data from the first command Get-Mailbox will not be displayed in the CSV output, and only the Get-MailboxStatistics data is shown. In the following image, notice that the Alias and Archivestatus columns are blank:
tressel_excel
So how can we combine multiple Cmdlets’ output data into one CSV file? The following snippet stores data from Get-Msoluser, Get-MailboxStatistics, and Get-Mailbox Cmdlets using a hash table and an array, before exporting to CSV. For the sake of brevity, I will assume that you already have PowerShell V2, you are connected via Remote PowerShell to Exchange Online, and the Microsoft Online Service Module before using this snippet.

[sourcecode language=”powershell”]
$DataPath = "C:\o365UserData.csv"
$Results = @()
$MailboxUsers = get-mailbox -resultsize unlimited
foreach($user in $mailboxusers)
{
$UPN = $user.userprincipalname
$License = Get-MsolUser -userprincipalname $UPN
$MbxStats = Get-MailboxStatistics $UPN
$Properties = @{
Name = $user.name
UPN = $UPN
Alias = $user.alias
UsageLocation = $user.usagelocation
License = $License.Licenses[0].AccountSkuId
ArchiveStatus = $user.archivestatus
Server = $MbxStats.servername
DatabaseInstructions = $MbxStats.databasename
TotItemSize = $MbxStats.totalitemsize
 }
$Results += New-Object psobject -Property $properties
}
$Results | Select-Object Name,UPN,Alias,UsageLocation,License,ArchiveStatus,Server,DatabaseInstructions,TotItemSize | Export-Csv -notypeinformation -Path $DataPath
[/sourcecode]
Now let’s take a look at how our CSV file looks using this new method (separated into two pictures):
excel5
excel6
You can see that the new CSV file contains the missing data from the first attempt, as well as more data from the 3 Cmdlets.

About the Author

More from this Author

Thoughts on “How to Combine PowerShell Cmdlet Output in a Single CSV File”

  1. Great script!. The first real working one with a .csv output I could find.
    I altered it a little (used a .txt file with a select group of users as input, only needed the alias and the totalitemsize and wanted the output only in MB’s) but it all works fine now. Thnx.

  2. Mines very close, however doesn’t work fully. Do you know if there is an issue using a Win32_Product along side others? I can see information using Win32_ComputerSystem, however not the information using Win32_Product using this method of exporting to a csv. To an unchanged txt file no problem, formatted csv, no worky.

  3. Any idea on why my column for the user’s E-Mail address shows this output:
    Mail
    —-
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    {KFrankiesh2@test.local, d…
    Where as the other columns fully displays:
    SamAccountName Name
    ————– —-
    Outside Users Kevin Frankiesh
    Outside Users David Letterman
    Outside Users Kevin Frankiesh
    Outside Users Dina Pugliese
    STAFF-EMPLOYEES Kevin James
    STAFF-EMPLOYEES Jerry Brown
    STAFF-EMPLOYEES Maria Benedi
    STAFF – PARTY EVENTS Arnold Schwarzenegger
    STAFF – PARTY EVENTS Paul Muller
    STAFF – PARTY EVENTS Stephanie Sebastaini
    STAFF-EXTERNAL Kevin Frankiesh
    STAFF-EXTERNAL Dina Pugliese
    STAFF-EXTERNAL Jerry Brown
    STAFF-EXTERNAL Pamela Anderson
    STAFF-SECURITY Jerry Brown
    STAFF-SECURITY Stephanie Sebastaini
    STAFF-SECURITY Pamela Anderson
    Please advise.
    Thanks,
    Adam

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up
Categories