Query Details
Identity Service Principals Only Expired Secret
Query
//Find any Azure AD Service Principals with only incorrect or expired secrets being used and no successful signins. These apps may no longer be in use and can be removed.
//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs
//Microsoft Sentinel query
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(14d)
| summarize
['All Error Codes']=make_set(ResultType)
by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] !has "0" and ['All Error Codes'] has_any ("7000222", "7000215")
//Advanced Hunting query
//Data connector required for this query - Advanced Hunting with Azure AD P2 License
AADSpnSignInEventsBeta
| where Timestamp > ago(14d)
| summarize
['All Error Codes']=make_set(ErrorCode)
by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] !has "0" and ['All Error Codes'] has_any ("7000222", "7000215")Explanation
This query is used to find Azure AD Service Principals that have only incorrect or expired secrets being used and no successful sign-ins. These service principals may no longer be in use and can be removed. The query can be run using either the Azure Active Directory - Service Principal Sign-in Logs data connector or the Advanced Hunting with Azure AD P2 License data connector. The query filters the results based on a time range of the past 14 days and checks for specific error codes ("7000222" and "7000215") in the error codes associated with the service principals.
Details
Matt Zorich
Released: June 17, 2022
Tables
AADServicePrincipalSignInLogsAADSpnSignInEventsBeta
Keywords
AzureADServicePrincipals,Secrets,Signins,Apps
Operators
whereTimeGeneratedagosummarizemake_setbyServicePrincipalIdServicePrincipalName!hashas_any"0""7000222""7000215"AADServicePrincipalSignInLogsAADSpnSignInEventsBetaTimestampErrorCode