Query Details
Identity Application Access Review
Query
//Query to find users who have access to an application but haven't signed in for 90 days //Data connector required for this query - Azure Active Directory - Signin Logs //Data connector required for this query - Microsoft Sentinel UEBA let signins= SigninLogs | where TimeGenerated > ago (90d) | where AppDisplayName has "Application Name" | project TimeGenerated, UserPrincipalName, AppDisplayName; IdentityInfo | where TimeGenerated > ago (21d) | summarize arg_max(TimeGenerated, *) by AccountUPN | extend UserPrincipalName = AccountUPN | where GroupMembership contains "Group that gives access to Application" | join kind=leftanti signins on UserPrincipalName | project UserPrincipalName
Explanation
This query is looking for users who have access to a specific application but have not signed in for the past 90 days. It uses data connectors for Azure Active Directory - Signin Logs and Microsoft Sentinel UEBA.
The query first retrieves signin logs from the past 90 days for the specified application. It then retrieves identity information from the past 21 days and filters for users who are members of a specific group that gives access to the application.
Next, it performs a left anti-join with the signin logs to find users who have not signed in. Finally, it projects the UserPrincipalName of the users who meet the criteria.
Details
Matt Zorich
Released: June 17, 2022
Tables
SigninLogsIdentityInfo
Keywords
Users,ApplicationName,SigninLogs,SentinelUEBA,TimeGenerated,UserPrincipalName,AppDisplayName,IdentityInfo,AccountUPN,GroupMembership
Operators
whereagohasprojectsummarizearg_maxbyextendcontainsjoinkindleftanti