Get a Report of All Inactive Office 365 Mailbox Users

Use a PowerShell script to find all inactive Office 365 mailboxes and save them to a csv file.

This PowerShell script will give you a report of all of your Office 365 mailbox users who haven’t logged in for any given number of days. Once you’ve saved the script to a .ps1 file on your workstation or server, you can run it with just the script name or you can include the number of days inactive as a command-line option. the command-line option is especially handy for a scheduled task if you want the report emailed automatically every week, day, or whatever.

You can run it as…

C:\Windows> Get_InactiveMailboxes.ps1

or…

C:\Windows> Get_InactiveMailboxes.ps1 90

…where “90” is the number of days inactive.

You will need to modify the $Outputpath variable to whatever is appropriate for your environment.

# xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
# Filename   :    Get_InactiveMailboxes.ps1
# Purpose    :    Creates a new resource mailbox.
# Syntax     :    Get_InactiveCloudMailboxes 90 \\This exports a report of all 
#                 Office 365 mailboxes that have not been logged into for at 
#                 least 90 days. If no value is supplied for the inactivity  
#                 threshold, the user will be prompted for one.
# Created    :    March 6, 2017. Copyright Jay Carper. You are free to reuse &
#                 adapt this code for your own purposes, but not to redistribute
#                 it, whether for free or profit.

# Accept command line input of a number of days for an inactivity threshold.
Param([string]$Threshold)
 
# If no threshold was entered, prompt for it.
do {
    if (-not $Threshold) {
        $Threshold = (Read-Host "Inactivity threshold in days")
    }
}
while (-not $Threshold)

# If PowerShell is not already connected to Exchange Online, prompt for 
# credentials and connect it.
if (-not (Get-Module MSOnline)) {
    $UserCredential = Get-Credential
    $Session = New-PSSession -ConfigurationName Microsoft.Exchange `
        -ConnectionUri https://outlook.office365.com/powershell-liveid/ `
        -Credential $UserCredential -Authentication Basic -AllowRedirection
    Import-PSSession $Session
    Import-Module MSOnline
    Connect-MsolService -Credential $UserCredential
}

# Get necessary variables
$Today = Get-Date
$Days = New-TimeSpan -Days $Threshold
$Output = @()

# ***** Modify this path to whatever is appropriate for your environment.
$Outpath = "C:\Temp\InactiveOffice365.csv"

# Retrieve all user mailboxes that were created before the threshhold 
# limit and store in this variable.
$AllUserMailboxes = (Get-Mailbox -ResultSize Unlimited `
    -RecipientTypeDetails UserMailbox | ?{$_.WhenMailboxCreated `
    -lt ($Today - $Days)})

# Get the mailbox statistics for the selected mailboxes.
$AllUserMailboxesStats = ($AllUserMailboxes | Get-MailboxStatistics)

# Filter the selected statistics based on last logons older than the 
# given threshold or with no last logon.
$InactiveMailboxes  = ($AllUserMailboxesStats | ?{($_.LastLogonTime `
    -lt ($Today - $Days)) -or ($_.LastLogonTime -eq $Null)})

# Add a line to the output for each inactive mailbox.
foreach ($InactiveMailbox in $InactiveMailboxes) {
    
    # Retrieve the mailbox information from the collection of 
    # mailboxes stored earlier.
    $Mailbox = ($AllUserMailboxes | ?{$_.DisplayName `
        -eq $InactiveMailbox.DisplayName})
    
    # If the mailbox has never been logged into, set the inactivity days 
    # equal to the mailbox age.
    if ($InactiveMailbox.LastLogonTime -eq $Null) {
        $DaysInactive = ($Today - ($Mailbox.WhenMailboxCreated)).days
    }
    
    # Otherwise, set the inactivity days equal to today's date minus the 
    # last logon date.
    else {
        $DaysInactive = ($Today - $InactiveMailbox.LastLogonTime).days
    }
    
    # Create a hashtable with the fields, UserPrincipalName, PrimarySmtpAddress, 
    # and Inactive. The first two fields are calculated from the mailbox data,  
    # while the third is calculated in the If/Else statement above. I am using  
    # the UserPincipalName instead of SamAccountName because it is useful for 
    # both all-cloud and hybrid deployments, where the SamAccountName would be 
    # less useful for hybrid deployments.)
    $Instance = ($InactiveMailbox | Select `
        @{Label="UserPrincipalName";Expression={$Mailbox.UserPrincipalName}}, `
        @{Label="PrimarySmtpAddress";Expression={$Mailbox.PrimarySmtpAddress}}, `
        @{Label="Inactive";Expression={$DaysInactive}})
    $Output += $Instance
}

# Save the resulting data to the file specified in $Outpath.
$Output | Export-Csv $Outpath -NoTypeInformation

The resulting output will look like this:

UserPrincipleName PrimarySmtpAddress Inactive
 user1@logondomain.com  user1@emaildomain.com  95
 user2@logondomain.com  user2@emaildomain.com  110
 user3@logondomain.com  user3@emaildomain.com  92

You can then use this information to track down the inactive users to see whether or not they should still have accounts.

(Update 3/8/2017: Corrected a couple of missing close braces.)

3 responses to “Get a Report of All Inactive Office 365 Mailbox Users”

  1. Daniel Pipe says:

    Thanks but I get the following error. A CSV is created but doesn’t seem to contain enough users.
    Import-Module : The specified module ‘MSOnline’ was not loaded because no valid module file was found in any module
    directory.
    At C:\Temp\Inactive 365 Users.ps1:32 char:5
    + Import-Module MSOnline
    + CategoryInfo : ResourceUnavailable: (MSOnline:String) [Import-Module], FileNotFoundException
    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand
    Connect-MsolService : The term ‘Connect-MsolService’ is not recognized as the name of a cmdlet, function, script file,
    or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and
    try again.
    At C:\Temp\Inactive 365 Users.ps1:33 char:5
    + Connect-MsolService -Credential $UserCredential
    + FullyQualifiedErrorId : CommandNotFoundException

    • jay c says:

      Hi Daniel. Make sure you have downloaded and installed the MSOnline PowerShell module. You should be able to get it by running this cmdlet as a local administrator on your computer:

      Install-Module msonline

  2. Robert says:

    The below GUI tool gives you inactive users on all Office 365 services and also you can find the inactive users by their last activity type such as last mail read time, last mail sent time, last team message sent, last yammer post time etc.

    https://gallery.technet.microsoft.com/office/Office-365-Reporting-Tool-7987b4c2

    Following are the some of the inactive Office 365 user reports which are available in the above tool.

    Inactive Exchange Users by Last Mail Read Date
    Inactive Exchange Users by Last Mail Sent Date
    Inactive Exchange Users by Last Mail Received Date
    Inactive Exchange Mailboxes by Last Login Time
    Last Active Time of Users by Office 365 Services
    Daily Active User Count by Office 365 Services
    and more…

Leave a Reply

Your email address will not be published. Required fields are marked *