Posted by on March 6, 2017

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.)

Comments

Be the first to comment.

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*