List all users' last login date

[Jakob Rohde]

Is it possible, using PowerShell, to list all AAD users' last login date (no matter how they logged in)? I have found a couple of scripts that check the last mailbox login, but that is not what we need, because we also want to list unlicensed users.

[#2]

@NicolasHon - FYI AuditLog.Read.All may not work as expected the work around is to use Directory.Read.All permissions. https://docs.microsoft.com/en-us/graph/known-issues#license-check-errors-for-azure-ad-activity-repor...

[#3]

Thanks for this information. I never had this issue.

[#4]

Hi @NicolasHon, does on have to create an app still? I see in the docs that one can authenticate directly in powershell with an admin account by using graph SDK (https://docs.microsoft.com/en-us/graph/powershell/get-started) Or have I misunderstood?

Could something like this work with only direct commands in powershell?

Install-Module Microsoft.Graph

Connect-MgGraph -Scope "User.Read.All"

Select-MgProfile -Name "beta"

Get-MgUser -Filter "signInActivity/lastSignInDateTime le 2021-09-30T00:00:00Z$select=id,displayName,userType"

$Result | Export-CSV "C:\Temp\LastLoginDateReport.CSV" -NoTypeInformation -Encoding UTF8

[#5]

Hi Niklask, yes you could also achieve that using this way. The thing is that you need to have an admin role to approve Microsoft Graph module when running it from PowerShell. It is less practical as I don't have a permanent admin so I need to go in PIM, activate my admin role, and then run the script. With the app this is not the case as I don't use Graph commands in PowerShell and just do Graph API's calls. The app allow you also to schedule this script in a scheduled task or to access it from a Power Automate Flow to do periodic report.

[#6]

Hiya guys, if you want some more reporting information and automate the removal process this may also help you.

https://theinformationstore.com.au/remove-staleguests/

https://github.com/JBines/Remove-StaleGuests

[#7]

@NicolasHonThanks for this it worked as is.  I was close but was having trouble with the actual lastsignindate being output.  I think the OP originally wanted all users not just Guest users but should be easy to figure that out. 

[#8]

@Jakob Rohde @Vasil Michev 

The Microsoft Graph API now supports the resource property signInActivity in users end-point, this resource exposes the lastSignInDateTime property which shows the last time a user made a successful sign-in. Fetching signInActivity property requires an Azure AD Premium P1/P2 license and the AuditLog.Read.All permission. The following request retrieves user details along with signInActivity property.

#GET Request
https://graph.microsoft.com/beta/users?$select=displayName,signInActivity

Before Microsoft Graph supports this property, we need to either get the mailbox last logon time using the Get-MailboxStatistics cmdlet or we need to crawl the Azure AD sign-in logs or the Unified audit logs in the Security and Compliance Center. 

You can refer the below post to know more details about how to find and export Last login date for all Azure AD Users using PowerShell.

• https://morgantechspace.com/2021/09/find-last-login-date-for-all-azure-ad-users-using-powershell.htm...

[#9]

@Kevin Morgan license? ;) the lastsignin property is a static value that is populated for all user accounts including guest accounts (see thread) back to apr 2020.  

Edit: I'm guessing you mean the tenant has 'any' aad p1/p2. Haven't tested this maybe you are right? 

[#10]

@Joshua Bines 

Yes the value may get populated for all users. But we need the license to retrieve the lastlogin value through Microsoft Graph API. The Microsoft's document itself still indicates the same and so my testing. Hope still there is no other API or PowerShell cmdlet to get the details without Azure AD Premium P1/P2 license. Can you please point me if you know any resources?

[#11]

Yep quite right... looks like it's a premium feature for the tenant (not the user object) I haven't run into any such issues myself but good to note thanks!

[#12]

Yup, Sign-in events are behind a P1 paywall, so are some of the "identity" reports. Tried arguing that point with MS on numerous occasions, no luck...

[#13]

This is great! how would you set the script to run aganst a specific dynamic cloud security group?

[#14]

I wrote a detailed document on this. The solution returns, DisplayName, UserPrincipleName, LastLogin.

$mailboxes = Get-EXOMailbox -ResultSize Unlimited
$mailboxes | ForEach-Object {
 $mbx = $_
 $mbs = Get-EXOMailboxStatistics -Identity $mbx.UserPrincipalName | Where-Object LastLogonTime -LE (Get-Date).AddDays(-90) | Select DisplayName, LastLogonTime
  if ($mbs.LastLogonTime -ne $null){
        $lt = $mbs.LastLogonTime
  }else{
        $lt = "Normal User" }
 
    New-Object -TypeName PSObject -Property @{
        DisplayName = $mbx.DisplayName
        UserPrincipalName = $mbx.UserPrincipalName
        LastLogonTime = $lt}
}

You can read the complete from my blog: Get Last Logon Time of Office 365 Users PowerShell

[#15]

LastLogonTime may not be the best attribute to target according to below and I think it is also limited to logins for exchange online which will not help for non mailbox enabled accounts. In the thread you will see a more robust solution that said thank you for your efforts. Sharing is always appreciated! https://o365reports.com/2019/06/18/office-365-users-last-logon-time-incorrect/

[#16]

@Joshua Bines, Yes agree to you on some extend. Do you have any alternative fix to the script? Will be highly appreciated. Secondly and Importantly, the commercial issue is two or three years old. Thank You

[#17]

Have a look at the comment in this thread NicolasHon replied to Jakob Rohde ‎Dec 29 2021 11:19 AM

You will only need to remove this filter clause in the script to obtain the real logintimstamp for all users in the tenant.

#Filter only Guests
if ($User.userType -eq 'Guest'){}

[#18]

Hi @Joshua Bines, Yes Thank you for the feedback as I am also in processes to created these scripts for my environment and my blog as well. I will add this section as well. Thank You

[#19]

Hello! @NicolasHon, how are you? Hope fine. 

I extend you and anyone who knows the following question: 

I'm getting some users with the LastSignInDateTime empty or with the following date (i reviewed the raw csv, and is not a excel format issue) 1/1/0001 21:00:00

I assume that the empty date means that they never log in to 365 but dont know if the date is the same.

Do you know who this means? 

thanks in advance!! 

[#20]

Hi @Jakob, Did you finally get the updated script?

[#21]

@Jakob Rohde 

This can be done using AzureADPreview

Import-Module AzureADPreview
$UsersUPN = (Get-AzureADUser -Top 20000).UserPrincipalName

foreach($user in $UsersUPN)
    {
        Get-AzureADAuditSignInLogs -Filter "UserPrincipalName eq '$user'" -Top 1| `
        select CreatedDateTime, UserPrincipalName, IsInteractive, AppDisplayName, IpAddress, TokenIssuerType, @{Name = 'DeviceOS'; Expression = {$_.DeviceDetail.OperatingSystem}}
        Start-Sleep -Seconds 5
    }

 A Start-Sleep -Seconds 5 delay should be added in order to bypass the "Too many requests" error code