Query Details
Identity Summarize Account Inactivity
Query
//Summarize accounts (both members and guests) that haven't signed on for over 60 days, and summarize them into the month they last signed in
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago (365d)
| where ResultType == 0
| where isnotempty(UserType)
| summarize arg_max(TimeGenerated, *) by UserPrincipalName
| where TimeGenerated < ago(60d)
| summarize
['Inactive Account List']=make_set(UserPrincipalName),
['Count of Inactive Accounts']=dcount(UserPrincipalName)
by UserType, Month=startofmonth(TimeGenerated)
| sort by Month desc, UserType asc Explanation
This query is summarizing accounts (both members and guests) that haven't signed on for over 60 days. It uses the Azure Active Directory - Signin Logs data connector.
First, it filters the signin logs to include only data from the past 365 days and where the result type is successful. It also filters out any empty user types.
Then, it uses the arg_max function to find the latest signin time for each user.
Next, it filters out any accounts that have signed in within the last 60 days.
Finally, it summarizes the data by user type and the month they last signed in. It creates a list of inactive accounts and counts the number of inactive accounts for each user type and month. The results are sorted by month in descending order and user type in ascending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators