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! 🙂
Joshua Bines
Jan 04, 2022Iron Contributor
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-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