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

%3CLINGO-SUB%20id%3D%22lingo-sub-2546941%22%20slang%3D%22en-US%22%3EUsing%20PowerShell%20in%20Microsoft%20365%2C%20find%20all%20users%20who%20have%20not%20logged%20in%20in%20the%20last%20180%20days!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2546941%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Azure%20%2F%20Microsoft365%20friends%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20a%20recent%20project%2C%20I%20was%20allowed%20to%20take%20on%20the%20following%20task.%20Search%20for%20all%20users%20(active%20users%2C%20not%20deactivated%20accounts)%20who%20had%20not%20logged%20in%20in%20the%20last%20180%20days.%20Clearly%20a%20case%20for%20PowerShell%2C%20right%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20used%20the%20PowerShell%20ISE%20for%20this%20configuration.%20But%20you%20are%20also%20very%20welcome%20to%20use%20Visual%20Studio%20Code%2C%20just%20as%20you%20wish.%20Please%20start%20with%20the%20following%20steps%20to%20begin%20the%20deployment%20(the%20Hashtags%20are%20comments)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23The%20first%20two%20lines%20have%20nothing%20to%20do%20with%20the%20configuration%2C%20but%20make%20some%20space%20below%20in%20the%20blue%20part%20of%20the%20ISE.%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESet-Location%20C%3A%5C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EClear-Host%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Install%20MSOline%20Module%3CBR%20%2F%3E%3CSTRONG%3EInstall-Module%20-Name%20MSOnline%20-AllowClobber%20-Force%20-Verbose%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Import%20MSOline%20Module%3CBR%20%2F%3E%3CSTRONG%3EImport-Module%20-Name%20MSOnline%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23This%20connects%20to%20Microsoft%20365%3CBR%20%2F%3E%3CSTRONG%3EConnect-MsolService%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23We%20need%20the%20module%20(without%20the%20parameter%20for%20a%20specific%20version)%3CBR%20%2F%3E%3CSTRONG%3EInstall-Module%20-Name%20ExchangeOnlineManagement%20-Force%20-Verbose%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23More%20specific%20if%20you%20want%3CBR%20%2F%3E%3CSTRONG%3EInstall-Module%20-Name%20ExchangeOnlineManagement%20-RequiredVersion%202.0.5%20-Force%20-Verbose%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Update%20the%20module%20(if%20necessary)%3CBR%20%2F%3E%3CSTRONG%3EUpdate-Module%20-Name%20ExchangeOnlineManagement%20-Verbose%20-Force%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Let's%20import%20the%20module%3CBR%20%2F%3E%3CSTRONG%3EImport-Module%20ExchangeOnlineManagement%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Check%20the%20version%20(if%20you%20have%20not%20selected%20a%20version)%3CBR%20%2F%3E%3CSTRONG%3EGet-InstalledModule%20-Name%20ExchangeOnlineManagement%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Variable%20for%20the%20Credential%3CBR%20%2F%3E%3CSTRONG%3E%24UserCredential%20%3D%20Get-Credential%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Now%20we%20connect%20to%20Exchange%20Online%3CBR%20%2F%3E%3CSTRONG%3EConnect-ExchangeOnline%20-Credential%20%24UserCredential%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Another%20way%20to%20connect%20(choose%20one%20or%20the%20other)%3CBR%20%2F%3E%3CSTRONG%3EConnect-ExchangeOnline%20-UserPrincipalName%20tom%40wechsler.cloud%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Set%20admin%20UPN%3CBR%20%2F%3E%24UPN%20%3D%20'tom%40wechsler.cloud'%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Time%20range%3CBR%20%2F%3E%3CSTRONG%3E%24startDate%20%3D%20(Get-Date).AddDays(-180).ToString('MM%2Fdd%2Fyyyy')%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24endDate%20%3D%20(Get-Date).ToString('MM%2Fdd%2Fyyyy')%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23We%20are%20looking%20for%20accounts%20that%20are%20active%20-%20not%20deactivated%3CBR%20%2F%3E%3CSTRONG%3E%24allUsers%20%3D%20%40()%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24allUsers%20%3D%20Get-MsolUser%20-All%20-EnabledFilter%20EnabledOnly%20%7C%20Select%20UserPrincipalName%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23We%20search%3CBR%20%2F%3E%3CSTRONG%3E%24loggedOnUsers%20%3D%20%40()%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24loggedOnUsers%20%3D%20Search-UnifiedAuditLog%20-StartDate%20%24startDate%20-EndDate%20%24endDate%20-Operations%20UserLoggedIn%2C%20PasswordLogonInitialAuthUsingPassword%2C%20UserLoginFailed%20-ResultSize%205000%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Create%20the%20list%3CBR%20%2F%3E%3CSTRONG%3E%24inactiveInLastThreeMonthsUsers%20%3D%20%40()%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24inactiveInLastThreeMonthsUsers%20%3D%20%24allUsers.UserPrincipalName%20%7C%20where%20%7B%24loggedOnUsers.UserIds%20-NotContains%20%24_%7D%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23We%20get%20a%20result%3CBR%20%2F%3E%3CSTRONG%3EWrite-Output%20%22The%20following%20users%20have%20no%20logged%20in%20for%20the%20last%20180%20days%3A%22%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%23written%20to%20the%20screen%3CBR%20%2F%3E%3CSTRONG%3EWrite-Output%20%24inactiveInLastThreeMonthsUsers%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Export%20list%20to%20CSV%3CBR%20%2F%3E%3CSTRONG%3E%24inactiveInLastThreeMonthsUsers%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24inactiveInLastThreeMonthsUsers%20%26gt%3B%20%22C%3A%5CTemp%5CInactiveUsers.csv%22%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%23Remove%20the%20session%3CBR%20%2F%3E%3CSTRONG%3EDisconnect-ExchangeOnline%20-Confirm%3A%24false%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20we%20have%20a%20list%20in%20CSV%20format%20with%20all%20users%20who%20have%20not%20logged%20in%20during%20the%20last%20180%20days.%20Great!%26nbsp%3BI%20know%20that%20wasn't%20super%20fancy%20at%20all.%20But%20I%20really%20wanted%20to%20share%20my%20experience%20with%20you.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20hope%20this%20article%20was%20useful.%20Best%20regards%2C%20Tom%20Wechsler%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EP.S.%20All%20scripts%20(%23PowerShell%2C%20Azure%20CLI%2C%20%23Terraform%2C%20%23ARM%2C%20etc.)%20that%20I%20use%20can%20be%20found%20on%20github!%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Ftomwechsler%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2Ftomwechsler%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2546941%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2547954%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20PowerShell%20in%20Microsoft%20365%2C%20find%20all%20users%20who%20have%20not%20logged%20in%20in%20the%20last%20180%20days!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2547954%22%20slang%3D%22en-US%22%3EThis%20is%20great%20and%20something%20I've%20been%20looking%20to%20create%20for%20a%20while.%20One%20scenario%20I%20want%20to%20exclude%20from%20the%20results%20are%20new%20accounts%20that%20have%20been%20created%20recently%20but%20not%20used%20yet.%20So%20maybe%20we%20need%20to%20include%20all%20active%20accounts%20that%20have%20not%20been%20logged%20into%20for%20180%20days%2C%20but%20exclude%20accounts%20that%20have%20been%20created%20within%20the%20last%2014%20days%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2550631%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20PowerShell%20in%20Microsoft%20365%2C%20find%20all%20users%20who%20have%20not%20logged%20in%20in%20the%20last%20180%20days!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2550631%22%20slang%3D%22en-US%22%3EIt's%20a%20pleasure!%20Will%20think%20about.%3C%2FLINGO-BODY%3E
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

 

 

2 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?