Query Details
Identity Service Principal Expired Secret
Query
//Find Azure AD service principals that have both successful and failed sign ins because of an expired secret. For both results, find the IP addresses.
//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs
//If you are seeing results then the old secret is still in use somewhere and requires updating.
//Microsoft Sentinel query
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(1d)
| summarize
['All Error Codes']=make_set(ResultType),
['Successful IP Addresses']=make_set_if(IPAddress, ResultType == 0),
['Failed IP Addresses']=make_set_if(IPAddress, ResultType == "7000222")
by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] has_all ("0", "7000222")
//Advanced Hunting query
//Data connector required for this query - Advanced Hunting with Azure AD P2 License
AADSpnSignInEventsBeta
| where Timestamp > ago(1d)
| summarize
['All Error Codes']=make_set(ErrorCode),
['Successful IP Addresses']=make_set_if(IPAddress, ErrorCode == 0),
['Failed IP Addresses']=make_set_if(IPAddress, ErrorCode == "7000222")
by ServicePrincipalId, ServicePrincipalName
| where ['All Error Codes'] has "0" and ['All Error Codes'] has "7000222"Explanation
This query is used to find Azure AD service principals that have both successful and failed sign-ins due to an expired secret. It retrieves the IP addresses associated with both successful and failed sign-ins. If there are results, it indicates that the old secret is still being used and needs to be updated.
For the Microsoft Sentinel query, the data connector required is "Azure Active Directory - Service Principal Signin Logs." It filters the logs based on the time generated within the last 24 hours. It then summarizes the data by grouping it based on the service principal ID and name. It creates sets of all error codes, successful IP addresses (if the result type is 0), and failed IP addresses (if the result type is "7000222"). Finally, it filters the results to include only those service principals that have both error codes 0 and "7000222".
For the Advanced Hunting query, the data connector required is "Advanced Hunting with Azure AD P2 License." It filters the events based on the timestamp within the last 24 hours. It then summarizes the data by grouping it based on the service principal ID and name. It creates sets of all error codes, successful IP addresses (if the error code is 0), and failed IP addresses (if the error code is "7000222"). Finally, it filters the results to include only those service principals that have both error codes 0 and "7000222".
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators