Forum Discussion
JakobRohde
Sep 22, 2017Iron Contributor
List all users' last login date
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 ne...
NicolasHon
Dec 29, 2021Brass Contributor
YES, This is possible!
But you need to do a little trick because it is only accessible via the Graph API. If you want to achieve that by PowerShell, you need to create an application, with a secret, that has access with the permission AuditLog.Read.All and User.Read.All and call this application with Graph command to do your query.
You can see my PowerShell script bellow:
#Replace [tenant_id], [Application_ID], [client_secret] with your own values
#Provide your Office 365 Tenant Id or Tenant Domain Name
$TenantId = "[tenant_id]"
#Provide Azure AD Application (client) Id of your app.
#You should have granted Admin consent for this app to use the application permissions "AuditLog.Read.All and User.Read.All" in your tenant.
$AppClientId="[Application_ID]"
#Provide Application client secret key
$ClientSecret = "[client_secret]"
$RequestBody = @{client_id=$AppClientId;client_secret=$ClientSecret;grant_type="client_credentials";scope="https://graph.microsoft.com/.default";}
$OAuthResponse = Invoke-RestMethod -Method 'Post' -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Body $RequestBody
$AccessToken = $OAuthResponse.access_token
#Form request headers with the acquired $AccessToken
$headers = @{'Content-Type'="application\json";'Authorization'="Bearer $AccessToken"}
#This request get users list with signInActivity.
$ApiUrl = "https://graph.microsoft.com/beta/users?`$select=displayName,userPrincipalName,signInActivity,userType,assignedLicenses,mail,createdDateTime&`$top=999"
$Result = @()
While ($ApiUrl -ne $Null){ #Perform pagination if next page link (odata.nextlink) returned.
$Response = Invoke-WebRequest -Method 'GET' -Uri $ApiUrl -ContentType "application\json" -Headers $headers | ConvertFrom-Json
if($Response.value){
$Users = $Response.value
ForEach($User in $Users){
#Filter only Guests
if ($User.userType -eq 'Guest'){
$Result += New-Object PSObject -property $([ordered]@{
UserID = $User.id
DisplayName = $User.displayName
ExternalDomain = if ($User.mail) {$User.mail.Split("@")[1]} else {$null}
Email = $user.mail
UserPrincipalName = $User.userPrincipalName
CreationDateTime = if($User.createdDateTime) {[DateTime]$User.createdDateTime} else {$null}
LastSignInDateTime = if($User.signInActivity.lastSignInDateTime) { [DateTime]$User.signInActivity.lastSignInDateTime } else {$null}
IsLicensed = if ($User.assignedLicenses.Count -ne 0) { $true } else { $false }
IsGuestUser = if ($User.userType -eq 'Guest') { $true } else { $false }
URL = "https://portal.azure.com/#blade/Microsoft_AAD_IAM/UserDetailsMenuBlade/Profile/userId/"+$user.id
})
}
}
}
$ApiUrl=$Response.'@odata.nextlink'
}
#Export to local computer
$Result | Export-CSV "C:\Temp\LastLoginDateReport.CSV" -NoTypeInformation -Encoding UTF8
Enjoy! 🙂
But you need to do a little trick because it is only accessible via the Graph API. If you want to achieve that by PowerShell, you need to create an application, with a secret, that has access with the permission AuditLog.Read.All and User.Read.All and call this application with Graph command to do your query.
You can see my PowerShell script bellow:
#Replace [tenant_id], [Application_ID], [client_secret] with your own values
#Provide your Office 365 Tenant Id or Tenant Domain Name
$TenantId = "[tenant_id]"
#Provide Azure AD Application (client) Id of your app.
#You should have granted Admin consent for this app to use the application permissions "AuditLog.Read.All and User.Read.All" in your tenant.
$AppClientId="[Application_ID]"
#Provide Application client secret key
$ClientSecret = "[client_secret]"
$RequestBody = @{client_id=$AppClientId;client_secret=$ClientSecret;grant_type="client_credentials";scope="https://graph.microsoft.com/.default";}
$OAuthResponse = Invoke-RestMethod -Method 'Post' -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -Body $RequestBody
$AccessToken = $OAuthResponse.access_token
#Form request headers with the acquired $AccessToken
$headers = @{'Content-Type'="application\json";'Authorization'="Bearer $AccessToken"}
#This request get users list with signInActivity.
$ApiUrl = "https://graph.microsoft.com/beta/users?`$select=displayName,userPrincipalName,signInActivity,userType,assignedLicenses,mail,createdDateTime&`$top=999"
$Result = @()
While ($ApiUrl -ne $Null){ #Perform pagination if next page link (odata.nextlink) returned.
$Response = Invoke-WebRequest -Method 'GET' -Uri $ApiUrl -ContentType "application\json" -Headers $headers | ConvertFrom-Json
if($Response.value){
$Users = $Response.value
ForEach($User in $Users){
#Filter only Guests
if ($User.userType -eq 'Guest'){
$Result += New-Object PSObject -property $([ordered]@{
UserID = $User.id
DisplayName = $User.displayName
ExternalDomain = if ($User.mail) {$User.mail.Split("@")[1]} else {$null}
Email = $user.mail
UserPrincipalName = $User.userPrincipalName
CreationDateTime = if($User.createdDateTime) {[DateTime]$User.createdDateTime} else {$null}
LastSignInDateTime = if($User.signInActivity.lastSignInDateTime) { [DateTime]$User.signInActivity.lastSignInDateTime } else {$null}
IsLicensed = if ($User.assignedLicenses.Count -ne 0) { $true } else { $false }
IsGuestUser = if ($User.userType -eq 'Guest') { $true } else { $false }
URL = "https://portal.azure.com/#blade/Microsoft_AAD_IAM/UserDetailsMenuBlade/Profile/userId/"+$user.id
})
}
}
}
$ApiUrl=$Response.'@odata.nextlink'
}
#Export to local computer
$Result | Export-CSV "C:\Temp\LastLoginDateReport.CSV" -NoTypeInformation -Encoding UTF8
Enjoy! 🙂
- dcorsoMar 23, 2022Copper Contributor
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.
- DeletedJan 05, 2022Hi 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- NicolasHonJan 06, 2022Brass ContributorHi 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.
- mdiloretoSep 12, 2022Copper Contributor
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!!
- Joshua BinesJan 04, 2022Iron ContributorNicolasHon - 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-reports
- NicolasHonJan 05, 2022Brass ContributorThanks for this information. I never had this issue.
- Joshua BinesJan 18, 2022Iron ContributorHiya 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