Automating a Scheduled Report
In my last post titled “Reporting Options for Office 365”, I mentioned scripting as one of the methods for obtaining data and creating reports for Office 365. One of the first things customers usually ask for is a report on what mailboxes are inactive. This is important for businesses so they don’t need to buy extra licenses until they are actually needed. Many times there is a lag or a disconnect between when an employee is terminated and IT finds out.
By automating a scheduled report delivered to the helpdesk or IT manager, IT can quickly find out what accounts may no longer be needed. Unused accounts can have their Office 365 license revoked and the mailbox can be moved to a “shared mailbox” in order to keep it around for a period of time.
The built-in report for Office 365 only shows a count of active and inactive mailboxes. It doesn’t drill down into WHAT accounts are inactive, and it also doesn’t allow you to filter out shared mailboxes or room/equipment mailboxes which don’t need a license.
I wanted to create a script that could be run in an automated fashion, only pulled data from actual user mailboxes, and be delivered via email on a schedule. I found a promising script on the Microsoft Office TechCenter. This PowerShell script connects to Office 365 and will export a list of all the Office 365 mailbox-enabled users with their last login date and time to a CSV file. If you want to filter the script you can pass it an input TXT file of the accounts you want to query. So this script did most of what I wanted, but it didn’t filter out the shared mailboxes and the room/equipment mailboxes. Also, it had to be run on demand.
I modified the script to remove the code allowing it to be filtered with a TXT file input, set it to only query for user mailboxes, and added a section to email the report when it is finished running.
Use This Code
Here is the code I use:
[powershell]
################################################################################################################################################################
# SCRIPT NAME: GetLastLogonStats.ps1
# SCRIPT TYPE: PowerShell
# CREATED: 4/24/2014
# AUTHOR: Michael Joseph, TecFac Business Services Group (tecfac.net)
# Modified from http://gallery.technet.microsoft.com/office/Export-Office-365-User-7fc0b73f#content
# Credit to Alan Byrne from Cogmotive Reports (cogmotive.com) for the majority of the content in this script
# CREATED FOR: Insert Company Here (company.com)
# PURPOSE: Query Office 365 User Mailboxes (excluding shared and room mailboxes) for the last time mailboxes were logged in to, save the results to
# a .csv file and email the results.
# PREREQUISITES: Windows OS with Powershell and WinRM installed and configured per http://help.outlook.com/en-us/140/cc952755.aspx and
# Office 365 administrative rights to the tenant the script is configured to query
# LAST MODIFIED: 4/24/2014
#
# NOTES:
#
# Script accepts 3 parameters from the command line
#
# Office365Username – Mandatory – Administrator login ID for the tenant we are querying
# Office365Password – Mandatory – Administrator login password for the tenant we are querying
#
#
#
# To run the script
#
# .Get-LastLogonStats.ps1 -Office365Username [email protected] -Office365Password Password123
#
# To run this script on a schedule, use the Windows Task Scheduler
#
################################################################################################################################################################
#Accept input parameters
Param(
[Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true)]
[string] $Office365Username,
[Parameter(Position=1, Mandatory=$true, ValueFromPipeline=$true)]
[string] $Office365Password
)
#Constant Variables
$OutputFile = “C:ReportsLastLogonDate.csv” #The CSV Output file that is created, change for your purposes
#Main
Function Main {
#Remove all existing Powershell sessions
Get-PSSession | Remove-PSSession
#Call ConnectTo-ExchangeOnline function with correct credentials
ConnectTo-ExchangeOnline -Office365AdminUsername $Office365Username -Office365AdminPassword $Office365Password
#Prepare Output file with headers
Out-File -FilePath $OutputFile -InputObject “UserPrincipalName,LastLogonDate” -Encoding UTF8
#Gather all “User” mailboxes from Office 365
$objUsers = Get-Mailbox -RecipientTypeDetails UserMailbox | Get-Mailbox -ResultSize Unlimited | select UserPrincipalName
#Iterate through all users
Foreach ($objUser in $objUsers)
{
#Connect to the users mailbox
$objUserMailbox = get-mailboxstatistics -Identity $($objUser.UserPrincipalName) | Select LastLogonTime
#Prepare UserPrincipalName variable
$strUserPrincipalName = $objUser.UserPrincipalName
#Check if they have a last logon time. Users who have never logged in do not have this property
if ($objUserMailbox.LastLogonTime -eq $null)
{
#Never logged in, update Last Logon Variable
$strLastLogonTime = “Never Logged In”
}
else
{
#Update last logon variable with data from Office 365
$strLastLogonTime = $objUserMailbox.LastLogonTime
}
#Output result to screen for debuging (Uncomment to use)
#write-host “$strUserPrincipalName : $strLastLogonTime”
#Prepare the user details in CSV format for writing to file
$strUserDetails = “$strUserPrincipalName,$strLastLogonTime”
#Append the data to file
Out-File -FilePath $OutputFile -InputObject $strUserDetails -Encoding UTF8 -append
}
#Email the CSV file
$smtpServer = “Enter SMTP server IP address”
$att = new-object Net.Mail.Attachment ($OutputFile)
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From = “From email address”
$msg.To.Add(“To email address”)
$msg.Subject = “Office 365 Last Logon Report”
$msg.Body = “Attached is the latest Office 365 Last Logon Report. Please sort by the LastLogonDate column and determine if any accounts should be changed to shared accounts to free up license(s).”
$msg.Attachments.Add($att)
$smtp.Send($msg)
$att.Dispose()
#Clean up session
Get-PSSession | Remove-PSSession
}
###############################################################################
#
# Function ConnectTo-ExchangeOnline
#
# PURPOSE
# Connects to Exchange Online Remote PowerShell using the tenant credentials
#
# INPUT
# Tenant Admin username and password.
#
# RETURN
# None.
#
###############################################################################
function ConnectTo-ExchangeOnline
{
Param(
[Parameter(
Mandatory=$true,
Position=0)]
[String]$Office365AdminUsername,
[Parameter(
Mandatory=$true,
Position=1)]
[String]$Office365AdminPassword
)
#Encrypt password for transmission to Office365
$SecureOffice365Password = ConvertTo-SecureString -AsPlainText $Office365AdminPassword -Force
#Build credentials object
$Office365Credentials = New-Object System.Management.Automation.PSCredential $Office365AdminUsername, $SecureOffice365Password
#Create remote Powershell session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $Office365credentials -Authentication Basic –AllowRedirection
#Import the session
Import-PSSession $Session -AllowClobber | Out-Null
}
# Start script
. Main
[/powershell]
To set the script to run as a scheduled task, just copy the file to a server or workstation with PowerShell. Call the script to start a program. Enter PowerShell as the program/script, and setup the arguments like this:
-file “C:Location of Script Get-LastLogonStats.ps1” -Office365Username your o365 admin username -Office365Password corresponding password