Forum Discussion
Script to export to CSV all Mailboxes and SharedMailboxes
Hi,
I'm trying to make a new script in Powershell to display (for a report) the following attributes in a CSV table like this:
DisplayName UserPrincipalName Licenses RecipientTypeDetails TotalItemSize
I need to be displayed the UserMailboxes and SharedMailboxes.
I made separated scripts at first but i've to join all information on a unique file and it's a mess.
Can anyone help me?
- kathyBrass Contributor
You can try Export Office 365 Mailboxes with Licenses script that will report needed attributes(DisplayName, UPN, Licenses, RecipientTypeDetails). You need to add TotalItemSize alone. To get TotalItemSize, add below line in 110th line of script.
$TotalItemSize=(Get-MailboxStatistics -Identity $upn).TotalItemSize.
Additionally,
- This script shows 'Friendly Name of Licenses. For eg, ‘Office 365 Enterprise E3’ rather than ‘ENTERPRISEPACK’.
- Result can be filtered based on user / all mailbox type.
- The script can be executed with MFA enabled account.
Those are pretty much the most commonly asked for attributes, do a search online and use one of the dozens of already available scripts.
- Andrew PriceBrass ContributorHi
As Vasil mentioned there is plenty of scripts out there to assist with this issue. This issue is also a great step stone for building your own solid powershell scripts.
Sharing your current code would help people like myself and Vasil guide on how to get your script into the place you would like it to be.
I also suggest looking at PowerShell ISE Steriods, it's a minor cost but helps massively improve your scripting. - suvom3171Copper ContributorHi Vasil,
How add department column with this below powershell:
Get-Mailbox -ResultSize Unlimited | Select DisplayName,UserPrincipalName,RecipientTypeDetails,TotalItemSize,@{Name='Licenses';Expression={(Get-MsolUser -UserPrincipalName $_.UserPrincipalName | Select -ExpandProperty Licenses).AccountSkuID}} | Export-CSV -NoTypeInformation $env:USERPROFILE\Desktop\File.CSV
- Dean_GrossSilver Contributorlots of good resources for learning how to do this are at https://absolute-sharepoint.com/2018/03/resources-to-learn-powershell-for-office-365.html
- jerome317Brass Contributor
Here you go!
Get-Mailbox -ResultSize Unlimited | Select DisplayName,UserPrincipalName,RecipientTypeDetails,TotalItemSize,@{Name='Licenses';Expression={(Get-MsolUser -UserPrincipalName $_.UserPrincipalName | Select -ExpandProperty Licenses).AccountSkuID}} | Export-CSV -NoTypeInformation $env:USERPROFILE\Desktop\File.CSV
- rootwontfallCopper Contributor
I tried the script but that's the same problem that I had. Exporting that kind of information it only displays the following attributes right:
DisplayName UserPrincipalName RecipientTypeDetails
The other two columns (TotalItemSize and Licenses) are all blank.
To get the TotalItemSize I've to use get-mailboxstatistics am I right?
- jerome317Brass Contributor
That's right... woops, I only tried displayname and licenses and didn't confirm the rest. I'd say do the same thing with TotalItemSize using the format when I retrieve the licenses.
It does help if you can post your actual command 😉
@{Name='Licenses';Expression={(Get-MsolUser -UserPrincipalName $_.UserPrincipalName | Select PropertyHere)
- jerome317Brass Contributor
rootwontfallConfirmed this one has worked... just change the ResultSize to Unlimited
Get-Mailbox -ResultSize 10 | select displayname,UserPrincipalName,RecipientTypeDetails,@{Name='Mailbox Size';Expression={Get-MailboxStatistics $_.UserPrincipalName | Select TotalItemSize}},@{Name='Licenses';Expression={(Get-MsolUser -UserPrincipalName $_.UserPrincipalName | Select -ExpandProperty Licenses).AccountSKUID}} |Export-Csv -NoTypeInformation .\Desktop\Test.csv
- rootwontfallCopper Contributor
The licenses column still blank and I can't understand why.
I got one script (the one I used before) to give me that kind of information just like this:
Get-MsolUser -All |
Select DisplayName,UserPrincipalName,@{n="Licenses";e={$_.Licenses.AccountSKUid}} | Sort-Object DisplayName |
Export-Csv -Path C:\temp\MailboxesLicenciadas.csv -NoTypeInformationI tried it like this:
Get-Mailbox -ResultSize:Unlimited | select displayname,UserPrincipalName,RecipientTypeDetails,@{Name='Mailbox Size';Expression={Get-MailboxStatistics $_.UserPrincipalName | Select TotalItemSize}},@{Name='Licenses';Expression={Get-MsolUser -UserPrincipalName $_.UserPrincipalName | Select -ExpandProperty Licenses.AccountSKUID}} |Export-Csv -NoTypeInformation C:\temp\FullExport.csv
But I can't make it work in your script. Do you've any idea why?
- jerome317Brass Contributor
rootwontfallYou're missing a parenthesis, order of operations.
(Get-MsolUser -UserPrincipalName $_.UserPrincipalName | Select -ExpandProperty Licenses).AccountSKUID
- PavelDyachukCopper ContributorHi!
I just need UserPrincipalName in csv and couldn't find any appropriate answer.
So I created it by myself.
Get-Mailbox -Filter {recipienttypedetails -eq "SharedMailbox"} | select UserPrincipalName | Export-Csv D:\sharedmailboxes.csv