Query Details
Identity Find Inactive Managed Identities
Query
//Find Managed Identity service principals that have not successfully signed in in the last 30 days, for each Managed Identity list the Azure resources it has accessed
//Hopefully it means the resource has already been decommissioned, if not, check to see if it still requires the access it has been granted
//Data connector required for this query - Azure Active Directory - Managed Identity Signin Logs
//First find any Managed Identities that haven't successfully signed on for 30 days
AADManagedIdentitySignInLogs
| where TimeGenerated > ago(365d)
| where ResultType == "0"
| summarize arg_max(TimeGenerated, *) by AppId
| extend ['Days Since Last Signin'] = datetime_diff("day", now(), TimeGenerated)
| project
['Last Sign in Time']=TimeGenerated,
ServicePrincipalName,
ServicePrincipalId,
['Days Since Last Signin'],
AppId
| where ['Days Since Last Signin'] > 30
//Join that list of Managed Identities back to the sign in data and retrieve the Azure resources (such as Key Vault or Storage) it has accessed
| join kind=inner (
AADManagedIdentitySignInLogs
| where TimeGenerated > ago(365d)
| where ResultType == "0"
| summarize ['Resources Accessed']=make_set(ResourceDisplayName) by AppId)
on AppId
| project-reorder
['Last Sign in Time'],
['Days Since Last Signin'],
ServicePrincipalName,
ServicePrincipalId,
AppId,
['Resources Accessed']
| order by ['Days Since Last Signin'] desc
Explanation
This query is used to find Managed Identity service principals that have not successfully signed in within the last 30 days. For each Managed Identity, it lists the Azure resources it has accessed. The purpose is to determine if the resource has been decommissioned or if it still requires the access it has been granted.
To execute this query, a data connector called "Azure Active Directory - Managed Identity Signin Logs" is required.
The query first filters the Managed Identities that haven't signed in successfully in the last 30 days. It then calculates the number of days since the last sign-in and projects relevant information such as the last sign-in time, service principal name, service principal ID, and app ID.
Next, it joins this list of Managed Identities with the sign-in data to retrieve the Azure resources (e.g., Key Vault or Storage) that each Managed Identity has accessed. The result is ordered by the number of days since the last sign-in in descending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators