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! 🙂
dcorso
Mar 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.