Query Details
Identity Inactive Guest Accounts
Query
//Find guest accounts that haven't signed in for a period of time, this example uses 45 days
//Data connector required for this query - Azure Active Directory - Signin Logs
let timerange=180d;
let timeframe=45d;
SigninLogs
| where TimeGenerated > ago(timerange)
| where UserType == "Guest" or UserPrincipalName contains "#ext#"
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| join kind = leftanti
(
SigninLogs
| where TimeGenerated > ago(timeframe)
| where UserType == "Guest" or UserPrincipalName contains "#ext#"
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
)
on UserPrincipalName
| project UserPrincipalName
Explanation
This query finds guest accounts that haven't signed in for a period of time (in this case, 45 days). It uses the Azure Active Directory - Signin Logs data connector.
It first filters the SigninLogs to include only entries within a specified time range. Then, it further filters the results to include only guest accounts or accounts with a UserPrincipalName containing "#ext#". It also filters for successful sign-ins (ResultType == 0).
Next, it uses the summarize operator to group the results by UserPrincipalName and keep only the entry with the latest TimeGenerated for each user.
After that, it performs a left anti-join with another set of SigninLogs entries within a shorter timeframe (45 days). This join ensures that only guest accounts that haven't signed in within the specified timeframe are included in the final result.
Finally, it projects (displays) only the UserPrincipalName column in the output.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators