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:
Get-Mailbox Test.user1@company.com | Get-MailboxStatistics | Select Alias,Displayname,TotalItemSize,ArchiveStatus | Export-CSV C:\test.csv
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:
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.
$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
Now let’s take a look at how our CSV file looks using this new method (separated into two pictures):
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.
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.
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.
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