Retrieve M365 mailbox statistics and persist in SQL Server

Introduction

I needed to collect usage data to prepare for a mailbox migration and do some capacity planning. To facilitate this, I collected all mailboxes and their statistics, like current size and item count, and persisted them in a SQL database, that I created for this purpose.

In a first step, I connect to Exchange Online, using Connect-ExchangeOnline, then retrieve all mailboxes in the tenant, using Get-EXOMailbox. For each of those mailboxes, I then collect the statistics, using Get-EXOMailboxStatistics and format them in a custom object, that I am then writing to a staging table using Write-SqlTableData. This cmdlet will take care of the table and schema creation, if it does not exist, therefore I’m only using it for the staging tables. I’m also dropping already existing staging tables beforehand.

In a last step, I persist the data from staging, using a MERGE query. If previous mailbox statistics exist to a mailbox, the delta to the last run is also stored.

 

Sample Code

Here is the code, I used to run this process. 

First, create the table in SQL Server, where we persist the data:

CREATE TABLE [dbo].[MailboxDetails](

       [ExternalDirectoryObjectId] [nvarchar](max)  NULL,

       [UserPrincipalName]         [nvarchar](1024) NOT NULL,

       [Alias]                     [nvarchar](max)  NULL,

       [Name]                      [nvarchar](max)  NULL,

       [DisplayName]               [nvarchar](max)  NULL,

       [DistinguishedName]         [nvarchar](max)  NULL,

       [PrimarySmtpAddress]        [nvarchar](max)  NULL,

       [OrganizationId]            [nvarchar](max)  NULL,

       [Guid]                      [nvarchar](max)  NULL,

       [ItemCount]                 [bigint]         NULL,

       [SizeInGB]                  [numeric](20, 0) NULL,

       [SizeInBytes]               [numeric](20, 0) NULL,

       [DeltaItems]                [bigint]         NULL,

       [DeltaBytes]                [numeric](20, 0) NULL

)

 

Then the PowerShell code

Import-Module SqlServer 

Import-Module ExchangeOnlineManagement

$SQLserver   = "MySQLServerName"

$SQLdatabase = "M365"

 

#Build credential object and authenticate

$cred = Get-Credential

Connect-ExchangeOnline -Credential $cred

 

#drop staging table, if it exists

$DropScript = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[exo].[MailboxDetails]') AND type in (N'U')) DROP TABLE [exo].[MailboxDetails]"

SqlServer\Invoke-Sqlcmd -Query $DropScript -ServerInstance $SQLserver -Database $SQLdatabase

 

#get all mailboxes

$MBs = Get-EXOMailbox -ResultSize unlimited

 

foreach($MB in $MBs)

{

    #get mailbox statistics, write to staging

    $MBstat = Get-EXOMailboxStatistics -Identity $MB.UserPrincipalName

    [PSCustomObject]@{

        ExternalDirectoryObjectId = $MB.ExternalDirectoryObjectId

        UserPrincipalName = $MB.UserPrincipalName

        Alias = $MB.Alias

        Name = $MB.Name

        DisplayName = $MB.DisplayName

        DistinguishedName = $MB.DistinguishedName

        PrimarySmtpAddress = $MB.PrimarySmtpAddress

        OrganizationId = $MB.OrganizationId

        Guid = $MB.Guid.Guid

        ItemCount = $MBstat.ItemCount

        SizeInGB = $MBstat.TotalItemSize.Value.ToGB()

        SizeInBytes = $MBstat.TotalItemSize.Value.ToBytes()

    } | Write-SqlTableData -ServerInstance $SQLserver -DatabaseName $SQLdatabase -Force -SchemaName exo -TableName MailboxDetails

}

 

#persist statistics from staging and calculate delta to previous run

$UpdateScript = @"

MERGE INTO [$SQLdatabase].[dbo].[MailboxDetails] AS tgt  

USING (

SELECT [ExternalDirectoryObjectId]

      ,[UserPrincipalName]

      ,[Alias]

      ,[Name]

      ,[DisplayName]

      ,[DistinguishedName]

      ,[PrimarySmtpAddress]

      ,[OrganizationId]

      ,[Guid]

      ,[ItemCount]

      ,[SizeInGB]

      ,[SizeInBytes]

  FROM [$SQLdatabase].[exo].[MailboxDetails]

) as src

ON tgt.[UserPrincipalName] = src.[UserPrincipalName]

WHEN MATCHED THEN  

UPDATE SET  [DeltaItems]  = src.[ItemCount] - tgt.[ItemCount],

            [DeltaBytes]  = src.[SizeInBytes] - tgt.[SizeInBytes],

            [ItemCount]   = src.[ItemCount],

            [SizeInGB]    = src.[SizeInGB],

            [SizeInBytes] = src.[SizeInBytes]

WHEN NOT MATCHED BY TARGET THEN  

INSERT ([ExternalDirectoryObjectId],[UserPrincipalName],[Alias],[Name],[DisplayName],[DistinguishedName],[PrimarySmtpAddress],[OrganizationId],[Guid],[ItemCount],[SizeInGB],[SizeInBytes])

VALUES ([ExternalDirectoryObjectId],[UserPrincipalName],[Alias],[Name],[DisplayName],[DistinguishedName],[PrimarySmtpAddress],[OrganizationId],[Guid],[ItemCount],[SizeInGB],[SizeInBytes]);

"@

SqlServer\Invoke-Sqlcmd -Query $UpdateScript -ServerInstance $SQLserver -Database $SQLdatabase

 

As next steps, I will collect similar statistics on OneDrive usage. In another post, I will add credential handling, so the script can run on a schedule, without credential input.