Query Details
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 desc
Explanation
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