Exchange Online: see mailboxsize and added mobile devices with PowerShell

Lot of customers ask me if it is possible to get a statistic of mailbox size in Exchange Online. I say, of course it’s possible with PowerShell. Some time ago I created a short script to get sizes as output to shell. Not that usable. The last days I spent some time to create a CSV output with Mailbox size but also with ActiveSync device information. With the following script it’s possible to see active sync devices with their OS and also when they were added.

The CSV looks like this:

You can import this CSV to Excel and sort or can do whatever you want. Full script is available at the bottom

Befor you start analyzing the statistics of mailboxes you need a powershell connection to exchange online.
If you have no idea see my tutorial for Exchange Online Powershell Module
Additional information about PowerShell Module Exchange Online
Here is a short summary for the lazy guys 😉

#install module for all users on pc (once)
Install-Module ExchangeOnlineManagement # -Verbose -Scope AllUsers -Force
#import module 
Import-Module ExchangeOnlineManagement 
#connecto to exchange online (admin credentials needed)
Connect-ExchangeOnline 

After conneting successfully to exchange online, you should be able to execute powershell cmdlets in exchange online.

Of course you need to define the path to export a csv file see line 3

[string]$timestamp = (Get-Date -Format yyyy-MM-dd-HH-mm) 
###### insert path to csv export
$csvpath = "C:\temp\exon-stats-$timestamp.csv"
######

Four output reasons there has to be an array to variable mbstats and you need to get all mailboxes in variable $mbs

#see statistics of mailboxes
$mbs = Get-Mailbox -ResultSize Unlimited | Sort-Object name
#create an array for all statistics
$mbstats = @()

After getting all mailboxes from exchange online, we’re looping with foreach through them to find the information

foreach($mb in $mbs){
  $dn = (($mb).identity)
  $activesync = (Get-CASMailbox -Identity "$dn").hasactivesyncdevicepartnership
  if ($activesync -eq $true){
    #fetching active sync device information
    $activesyndevice = Get-MobileDevice -Mailbox "$dn" | Select-Object DeviceType,DeviceOS,DeviceUserAgent,WhenCreated
    $devicemodel = @()
    $devicemodel = ($activesyndevice) | Select-Object DeviceType -ExpandProperty DeviceType 
    $devicemodel = $devicemodel -join '||' 
    $deviceos = @()
    $deviceos = ($activesyndevice) | Select-Object DeviceOS -ExpandProperty DeviceOS 
    $deviceos = $deviceos -join '||'
    $deviceuagent = @()
    $deviceuagent =  ($activesyndevice) | Select-Object DeviceUserAgent -ExpandProperty DeviceUserAgent
    $deviceuagent = $deviceuagent -join '||'
    $devicecreated = @()
    $devicecreated = ($activesyndevice) | Select-Object WhenCreated -ExpandProperty WhenCreated
    $devicecreated = $devicecreated -join '||'
    #putting everything together (active sync devices and mailbox statistics)
    $mbstat = Get-Mailbox -Identity "$dn" | Get-MailboxStatistics | Select-Object DisplayName,MailboxGuid,`
    @{name="TotalItemSize (MB)"; expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2)}},`
    ItemCount,@{name="ActiveSync";expression={"True"}}, @{name="DeviceType";expression={"$devicemodel"}},@{name="DeviceOS";expression={"$deviceos"}},`
    @{name="DeviceAgent";expression={"$deviceuagent"}},@{name="DeviceFirstConnected";expression={"$devicecreated"}}`
    #adding to array 
    $mbstats += $mbstat
  }
  else{
    #get statistics for non active sync users
    $mbstat = Get-Mailbox -Identity "$dn" | Get-MailboxStatistics | Select-Object DisplayName,MailboxGuid, @{name="TotalItemSize (MB)"; expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2)}},ItemCount,@{name="ActiveSync";expression={"False"}} 
    #adding to array
    $mbstats += $mbstat
  }
} 

Variable $mbstats was filled with information from the mailboxes, so the last thing is to export this information to CSV

#export information to csv with delimiter 
$mbstats | Export-Csv -Path "$csvpath" -Encoding UTF8 -Delimiter ";"

Importing the CSV to excel is really easy and you can filter and analyze stuff as you want.

Full script with exchange online connection

[string]$timestamp = (Get-Date -Format yyyy-MM-dd-HH-mm) 
###### insert path to csv export
$csvpath = "C:\temp\exon-stats-$timestamp.csv"
######

#install module for all users on pc (once)
Install-Module ExchangeOnlineManagement # -Verbose -Scope AllUsers -Force
#import module 
Import-Module ExchangeOnlineManagement 
#connecto to exchange online (admin credentials needed)
Connect-ExchangeOnline 
#see statistics of mailboxes
$mbs = Get-Mailbox -ResultSize Unlimited | Sort-Object name
#create an array for all statistics
$mbstats = @()
foreach($mb in $mbs){
  $dn = (($mb).identity)
  $activesync = (Get-CASMailbox -Identity "$dn").hasactivesyncdevicepartnership
  if ($activesync -eq $true){
    #fetching active sync device information
    $activesyndevice = Get-MobileDevice -Mailbox "$dn" | Select-Object DeviceType,DeviceOS,DeviceUserAgent,WhenCreated
    $devicemodel = @()
    $devicemodel = ($activesyndevice) | Select-Object DeviceType -ExpandProperty DeviceType 
    $devicemodel = $devicemodel -join '||' 
    $deviceos = @()
    $deviceos = ($activesyndevice) | Select-Object DeviceOS -ExpandProperty DeviceOS 
    $deviceos = $deviceos -join '||'
    $deviceuagent = @()
    $deviceuagent =  ($activesyndevice) | Select-Object DeviceUserAgent -ExpandProperty DeviceUserAgent
    $deviceuagent = $deviceuagent -join '||'
    $devicecreated = @()
    $devicecreated = ($activesyndevice) | Select-Object WhenCreated -ExpandProperty WhenCreated
    $devicecreated = $devicecreated -join '||'
    #putting everything together (active sync devices and mailbox statistics)
    $mbstat = Get-Mailbox -Identity "$dn" | Get-MailboxStatistics | Select-Object DisplayName,MailboxGuid,`
    @{name="TotalItemSize (MB)"; expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2)}},`
    ItemCount,@{name="ActiveSync";expression={"True"}}, @{name="DeviceType";expression={"$devicemodel"}},@{name="DeviceOS";expression={"$deviceos"}},`
    @{name="DeviceAgent";expression={"$deviceuagent"}},@{name="DeviceFirstConnected";expression={"$devicecreated"}}`
    #adding to array 
    $mbstats += $mbstat
  }
  else{
    #get statistics for non active sync users
    $mbstat = Get-Mailbox -Identity "$dn" | Get-MailboxStatistics | Select-Object DisplayName,MailboxGuid, @{name="TotalItemSize (MB)"; expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2)}},ItemCount,@{name="ActiveSync";expression={"False"}} 
    #adding to array
    $mbstats += $mbstat
  }
} 
#export information to csv with delimiter 
$mbstats | Export-Csv -Path "$csvpath" -Encoding UTF8 -Delimiter ";"

Have fun with this little script analyzing your exchange online environment.