1

I’m looking for a way to list all mailboxes and save it as excel sheet. What I need to list is

  • user id’s and their mailboxes
  • group id’s and their mailboxes
  • additional info to be able to filter the sheet by country

I tried this command (I know, there are more parameters for test only) and almost everything is there except
get-mailbox | select-object displayname, SamAccountName, database, WhenMailboxCreated, OrganizationalUnit, EmailAddresses, GrantSendOnBehalfTo, HiddenFromAddressListsEnabled, PrimarySmtpAddress, RecipientType, RecipientTypeDetails |export-csv -path d:_temp\SG1.csv

EmailAddresses returns “Microsoft.Exchange.Data.ProxyAddressCollection”
GrantSendOnBehalfTo returns “Microsoft.Exchange.Data.Directory.ADMultiValuedProperty`1[Microsoft.Exchange.Data.Directory.ADObjectId]”
I don’t how to include user country into this export.

5 Spice ups
2

Here what I use on a server basis

Get-MailboxServer -Identity | Get-MailboxStatistics | select-object servername, databasename, DisplayName, @{expression={$_.TotalItemSize.Value.ToMB()}},StorageLimitStatus,LastLogonTime, LastLoggedOnUserAccount,ItemCount, DeletedItemCount | export-csv c:\exchangeuserstats.csv

2 Spice ups
3

Which I totally ripped off from anther thread!

4

Try the following, the one big problem with CSVs and some of the fields you’re collecting data from are that the Display name, email addresses and send on behalf fields are comma separated. I export it to a txt file, you can then open it using Excel and use the ~ character as you separator and it’s work for you. This requires Quests ad tools for powershell in addition to the exchange tools, but worked great on my system.

Add-PSSnapin Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue
$OutFile = “C:\SG1.txt”
del $outfile
Add-Content -Path $OutFile -Value “Lastname~Firstname~SamAccountName~database~WhenMailboxCreated~OrganizationalUnit~GrantSendOnBehalfTo~HiddenFromAddressListsEnabled~PrimarySmtpAddress~RecipientType~RecipientTypeDetails~Country~EmailAddresses”
$users = get-qaduser -sizelimit 0 -includedProperties lastname~firstname~samaccountname~co~email | where {$_.email -ne $null}
foreach ($User in $users){
$Country = $user.co
$UserMail = get-mailbox $user.samaccountname
$Outinfo = $User.Lastname +“~”+ $User.Firstname +“~”+ $UserMail.SamAccountName +“~”+ $UserMail.database +“~”+ $UserMail.WhenMailboxCreated +“~”+ $UserMail.OrganizationalUnit +“~”+ $UserMail.GrantSendOnBehalfTo +“~”+ $UserMail.HiddenFromAddressListsEnabled +“~”+ $UserMail.PrimarySmtpAddress +“~”+ $UserMail.RecipientType +“~”+ $UserMail.RecipientTypeDetails +“~”+ $Country +“~”+ $UserMail.EmailAddresses
add-content -Value $Outinfo -Path $OutFile
}

1 Spice up
5

It’s some time but I finally managed to look on this request again. I slightly modified your script to have better output and to work without the Quest snap in. It’s freeware but still, this way it’s not necessary to install the files.

$OutFile = “D:_temp\SG1.txt”
del $outfile
Add-Content -Path $OutFile -Value “Lastname~Firstname~SamAccountName~database~WhenMailboxCreated~OrganizationalUnit~GrantSendOnBehalfTo~HiddenFromAddressListsEnabled~PrimarySmtpAddress~RecipientType~RecipientTypeDetails~Country~EmailAddresses~LastLogonTime”
$users = get-mailbox -resultsize unlimited
foreach ($User in $users){
$UserInfo = get-user $user.samaccountname
$UserMail = get-mailbox $user.samaccountname
$UserStat = get-mailboxStatistics $user.samaccountname
$Outinfo = $UserInfo.Lastname +“~”+ $UserInfo.Firstname +“~”+ $UserMail.SamAccountName +“~”+ $UserMail.database +“~”+ $UserMail.WhenMailboxCreated +“~”+ $UserMail.OrganizationalUnit +“~”+ $UserMail.GrantSendOnBehalfTo +“~”+ $UserMail.HiddenFromAddressListsEnabled +“~”+ $UserMail.PrimarySmtpAddress +“~”+ $UserMail.RecipientType +“~”+ $UserMail.RecipientTypeDetails +“~”+ $UserInfo.CountryOrRegion +“~”+ $UserMail.EmailAddresses +“~”+ $UserStat.LastLogonTime
add-content -Value $Outinfo -Path $OutFile
}

2 Spice ups
6

Great post… Thanks for that author.

Modified it a bit to get user’s personal archive status. Hopefully this be help to someone too.

$OutFile = “E:\Temp\SG1.txt”
del $outfile
Add-Content -Path $OutFile -Value “SamAccountName PrimarySmtpAddress ItemCount ItemSize”
$users = get-mailbox -resultsize unlimited
foreach ($User in $users){
$UserInfo = get-user $user.samaccountname
$UserMail = get-mailbox $user.samaccountname
$UserStat = get-mailboxStatistics $user.samaccountname -archive
$Outinfo = $UserMail.SamAccountName " " $UserMail.PrimarySmtpAddress " " $UserStat.ItemCount " " $UserStat.TotalItemSize
add-content -Value $Outinfo -Path $OutFile
}

1 Spice up