Using PowerShell in Microsoft 365, find all users who have not logged in in the last 180 days!

MVP

 

Hi Azure / Microsoft365 friends,

 

In a recent project, I was allowed to take on the following task. Search for all users (active users, not deactivated accounts) who had not logged in in the last 180 days. Clearly a case for PowerShell, right?

 

I used the PowerShell ISE for this configuration. But you are also very welcome to use Visual Studio Code, just as you wish. Please start with the following steps to begin the deployment (the Hashtags are comments):

 

#The first two lines have nothing to do with the configuration, but make some space below in the blue part of the ISE.

Set-Location C:\
Clear-Host

 

#Install MSOline Module
Install-Module -Name MSOnline -AllowClobber -Force -Verbose

 

#Import MSOline Module
Import-Module -Name MSOnline

 

#This connects to Microsoft 365
Connect-MsolService

 

#We need the module (without the parameter for a specific version)
Install-Module -Name ExchangeOnlineManagement -Force -Verbose

 

#More specific if you want
Install-Module -Name ExchangeOnlineManagement -RequiredVersion 2.0.5 -Force -Verbose

 

#Update the module (if necessary)
Update-Module -Name ExchangeOnlineManagement -Verbose -Force

 

#Let's import the module
Import-Module ExchangeOnlineManagement

 

#Check the version (if you have not selected a version)
Get-InstalledModule -Name ExchangeOnlineManagement

 

#Variable for the Credential
$UserCredential = Get-Credential

 

#Now we connect to Exchange Online
Connect-ExchangeOnline -Credential $UserCredential

 

#Another way to connect (choose one or the other)
Connect-ExchangeOnline -UserPrincipalName tom@wechsler.cloud

 

#Set admin UPN
$UPN = 'tom@wechsler.cloud'

 

#Time range
$startDate = (Get-Date).AddDays(-180).ToString('MM/dd/yyyy')
$endDate = (Get-Date).ToString('MM/dd/yyyy')

 

#We are looking for accounts that are active - not deactivated
$allUsers = @()
$allUsers = Get-MsolUser -All -EnabledFilter EnabledOnly | Select UserPrincipalName

 

#We search
$loggedOnUsers = @()
$loggedOnUsers = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -Operations UserLoggedIn, PasswordLogonInitialAuthUsingPassword, UserLoginFailed -ResultSize 5000

 

#Create the list
$inactiveInLastSixMonthsUsers = @()
$inactiveInLastSixMonthsUsers = $allUsers.UserPrincipalName | where {$loggedOnUsers.UserIds -NotContains $_}

 

#We get a result
Write-Output "The following users have no logged in for the last 180 days:"

#written to the screen
Write-Output $inactiveInLastSixMonthsUsers

 

#Export list to CSV
$inactiveInLastSixMonthsUsers
$inactiveInLastSixMonthsUsers > "C:\Temp\InactiveUsers.csv"

 

#Remove the session
Disconnect-ExchangeOnline -Confirm:$false

 

Now we have a list in CSV format with all users who have not logged in during the last 180 days. Great! I know that wasn't super fancy at all. But I really wanted to share my experience with you.

 

I hope this article was useful. Best regards, Tom Wechsler

 

P.S. All scripts (#PowerShell, Azure CLI, #Terraform, #ARM, etc.) that I use can be found on github! https://github.com/tomwechsler

 

 

11 Replies
This is great and something I've been looking to create for a while. One scenario I want to exclude from the results are new accounts that have been created recently but not used yet. So maybe we need to include all active accounts that have not been logged into for 180 days, but exclude accounts that have been created within the last 14 days?

@TomWechsler Thank you for the script, is something a lot of people search for. 

 

Still, for companies with lots of users, this script is not reliable  as the 5000 records returned by Search-UnifiedAuditLog will cover only couple of days of logins and users will be reported as inactive even if they have logged in.  

@TomWechsler 

 

This would only pull users who have mailboxes right? I'm struggling to find this same data on users who have no activity the last 30 days, many who are unlicensed (the accounts were just for SSO purposes and had no license requirement)

@TomWechsler 

 

Hi, 

after execution gets a list of all users. And in my organization, Microsfot Teams services are actively used. What is the reason for this?

It seems I get the exact opposite of the result I expected. Instead of a list of users who did not logged in in the last 180 days, I get all the users who did. Then again the code seems to be doing exactly date : get a list of all users between $startDate (today - 180 days) and $endDate (today) for the operation "UserLoggedIn".

Am I missing something?
Write-ErrorMessage : Cannot process argument transformation on parameter 'StartDate'. Cannot convert value "11/17/2022" to type
"Microsoft.Exchange.ExchangeSystem.ExDateTime". Error: "String was not recognized as a valid DateTime."

:(
for some reason it doesn't work:
$loggedOnUsers = Search-UnifiedAuditLog -StartDate "01/04/2023 12:00:00 AM" -EndDate "16/05/2023 12:00:00 AM" -Operations UserLoggedIn, PasswordLogonInitialAuthUsingPassword, UserLoginFailed -ResultSize 5000
because it returns nulla

@Marc BeaudoinI think you want to change -"NotContains" to "Contains"

I've seen this error on some versions of PowerShell. Try a different version.
Works on 5.1 ok