Script to export to CSV all Mailboxes and SharedMailboxes

[rootwontfall]

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?

[#2]

Those are pretty much the most commonly asked for attributes, do a search online and use one of the dozens of already available scripts.

[#3]

Hi

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.

[#4]

lots 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

[#5]

@rootwontfall 

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

[#6]

@rootwontfall 

 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.

[#7]

@jerome317 

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?

[#8]

@rootwontfall 

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)

[#9]

@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

[#10]

@jerome317 

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 -NoTypeInformation

I 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?

[#11]

@rootwontfallYou're missing a parenthesis, order of operations.

(Get-MsolUser -UserPrincipalName $_.UserPrincipalName | Select -ExpandProperty Licenses).AccountSKUID

[#12]

@jerome317 

Even making the following command:

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 .\Desktop\Test.csv

It still gives me all columns right unless the column called "Licenses".

I've already tried to change the script but the result is the same...

I can't export licenses of each mailboxes.

Thanks for your help @jerome317!

[#13]

Weird. Exact script I ran and shows results, License is blank if it is a Shared/Resource mailbox.

So if you run a separate command using this, does it show licenses?

Get-MsolUser -UserPrincipalName YOUR_UPN@contoso.com | select DisplayName,Licenses

[#14]

@rootwontfall 

Did you use Connect-MsolService to be able to run Get-MsolUser part of the cmd ?

I used the same set of cmdlets and was able to get required data.

Deepak

[#15]

@DeepakRandhawa and @jerome317 

That's it!

With the complete script I forgot to write at first Connect-MsolServie to get all users licenses.

Thank you so much!

[#16]

Hi!
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

[#17]

Hi 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