Getting last logon date of all Office 365 Mailbox enabled users is one of the important task to track user logon activity and find inactive users to calculate the Exchange Online license usage. We can use the Exchange Online powershell cmdlet Get-MailboxStatistics to get last logon time, mailbox size, and other mailbox related statistics data.
Before proceed, first we need to connect Remote Exchange Online powershel module by running below command:
$LiveCred = Get-Credential $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $LiveCred -Authentication Basic -AllowRedirection Import-PSSession $Session
Run the following command to get mailbox statistics for a single Office 365 user
Get-MailboxStatistics -Identity <name or upn of user>
To find last logon time for all the Office 365 users, first we need to get all mailboxes by using Get-Mailbox cmdlet and pipe the results to Get-MailboxStatistics.
Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Select-Object DisplayName,LastLogonTime
Using above script, you can get output only from the cmdlet Get-MailboxStatistics and you can’t fetch any result from Get-Mailbox. If you want to read detail from Get-Mailbox command like UserPrincipalName, you need to merge output of two cmdlets.
$mailboxes = Get-Mailbox -ResultSize Unlimited $mailboxes | ForEach-Object { $mbx = $_ $mbs = Get-MailboxStatistics -Identity $mbx.UserPrincipalName | Select LastLogonTime if ($mbs.LastLogonTime -eq $null){ $lt = "Never Logged In" }else{ $lt = $mbs.LastLogonTime } New-Object -TypeName PSObject -Property @{ UserPrincipalName = $mbx.UserPrincipalName LastLogonTime = $lt } }
Export Last Logon Time of Office 365 Users to CSV file
You can also export all Exchange Online user’s last logon time to csv file by using below script
$Result=@() $mailboxes = Get-Mailbox -ResultSize Unlimited $totalmbx = $mailboxes.Count $i = 1 $mailboxes | ForEach-Object { $i++ $mbx = $_ $mbs = Get-MailboxStatistics -Identity $mbx.UserPrincipalName | Select LastLogonTime if ($mbs.LastLogonTime -eq $null){ $lt = "Never Logged In" }else{ $lt = $mbs.LastLogonTime } Write-Progress -activity "Processing $mbx" -status "$i out of $totalmbx completed" $Result += New-Object PSObject -property @{ Name = $mbx.DisplayName UserPrincipalName = $mbx.UserPrincipalName LastLogonTime = $lt } } $Result | Export-CSV "C:\O365-LastLogon-Info.csv" -NoTypeInformation -Encoding UTF8
Performance fix – Get Last Logon date for large numbers of users in Office 365
As you see in the above the script, we are fetching all mailboxes first and we are iterating every user mailbox to find its mailbox statistics, so the process holds all the objects in memory. This kind of approach may leads some performance problem when you run the commands against bulk number of mailboxes (ex: over 20k users). So, if you face any performance issue, you can simply run the below command to get desired result. Here we are using Invoke-Command method, this command just pass the entire script block to Office 365 server and get only the result from server side.
Invoke-Command -Session (Get-PSSession) -ScriptBlock {Get-Mailbox -RecipientTypeDetails UserMailbox -Resultsize Unlimited | Get-MailboxStatistics | Select-Object DisplayName,LastLogonTime} | Export-CSV "C:\O365-LastLogon-Info.csv" -NoTypeInformation -Encoding UTF8
Great work. It has helped me a lot
Get-Mailbox -ResultSize Unlimited | Select DisplayName,PrimarySmtpAddress,HiddenFromAddressListsEnabled,@{n="LastLogonTime";e={(Get-MailboxStatistics $_.Identity).LastLogonTime}} | Export-CSV "C:UsersExchange_Report.csv"
Hi – bit disappointed by the title. It should be “last logon time for exchange online users”. The article is only useful if the users have a mailbox – and many of ours don’t (they only have an account for MFA purposes). Thanks
Sorry for the disappointment. For your case, you can use Microsoft Graph API. The user details now support the signInActivity property. But it requires an Azure AD Premium P1/P2 license and the AuditLog.Read.All permission.
https://docs.microsoft.com/en-us/graph/api/user-list?view=graph-rest-beta&tabs=http#example-5-list-the-last-sign-in-time-of-users-in-a-specific-time-range
https://graph.microsoft.com/v1.0/users?$select=displayName,userPrincipalName,signInActivity
We will also write a detailed article in future.
This might work for some people and be less intensive then storing the objects in an array, although slow because now I query for the user to make sure they have what Im looking for, an E3 license, and if they do I want the lastActionTime because the lastlogontime was inaccurate
Import-Module ExchangeOnlineManagement
Import-module AzureADPreview
Connect-ExchangeOnline -UserPrincipalName “[email protected]”
$outfile = “.\O365-LastLogon-Info.csv”
$n = {} | select “DisplayName”, “UserPrincipalName”, “LastUserActionTime”, “AccountEnabled” | Export-Csv $outfile
$csvFile = Import-Csv -Path $outFile
$mailboxes = Get-Mailbox -ResultSize Unlimited
$totalMailboxes = $mailboxes.Count
$i = 0
#https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference?msclkid=60a2e2ddc18911ec888a87517ae6abf0
$OfficeE3SkuId = “6fd2c87f-b296-42f0-b197-1e91e994b900”
$mailboxes | ForEach-Object {
$i++
$mailboxObject = $_ # Object of each mailbox and all properties foreach iteration
Write-Progress -activity “Processing $($mailboxObject.DisplayName)” -status “$i out of $totalMailboxes completed”
$user = Get-AzureADUser -ObjectId $mailboxObject.UserPrincipalName
if(($user | select -expandProperty AssignedLicenses | select -ExpandProperty SkuId) -contains $OfficeE3SkuId){
$mailboxStatistics = Get-MailboxStatistics -Identity $mailboxObject.UserPrincipalName | Select LastUserActionTime
$lastActionTime = “”
if ($mailboxStatistics.LastUserActionTime -eq $null){
$lastActionTime = “Never Logged In”
}else{
$lastActionTime = $mailboxStatistics.LastUserActionTime
}
# This is a great feature. Look more into it
Write-Progress -activity “Processing $($mailboxObject.DisplayName)” -status “$i out of $totalMailboxes completed”
$csvFile.DisplayName = $mailboxObject.DisplayName
$csvFile.UserPrincipalName = $mailboxObject.UserPrincipalName
$csvFile.LastUserActionTime = $lastActionTime
$csvFile.AccountEnabled = $user.AccountEnabled
$csvFile | Export-Csv $outfile -Append
}else{
write-output “$($mailboxObject.displayName) ”
}
}
this is very good. Thanks