Identity Summarize Guest Inactivity
Query
//Group your Azure AD guest accounts into the month they last signed in
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago (360d)
| where UserType == "Guest" or UserPrincipalName contains "#ext#"
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| project TimeGenerated, UserPrincipalName
| summarize ['Inactive Guest Accounts']=make_set(UserPrincipalName) by Month=startofmonth(TimeGenerated)
| sort by Month descExplanation
This query groups Azure AD guest accounts based on the month they last signed in. It uses the Azure Active Directory - Signin Logs data connector.
First, it filters the logs to include only the past 360 days and guest accounts. It then further filters the results to include only successful sign-ins.
Next, it finds the latest sign-in time for each guest account using the arg_max function and groups the results by the UserPrincipalName.
It then selects the TimeGenerated and UserPrincipalName columns and summarizes the data by creating a set of inactive guest accounts for each month, based on the start of the month of the sign-in time.
Finally, it sorts the results by the month in descending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators