Query Details
Identity Find Inactive Service Principals
Query
//Find Azure AD Service Principals that have not successfully signed on for the last 30 days
//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(180d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by AppId
| project
['Last Successful Logon']=TimeGenerated,
ServicePrincipalName,
ServicePrincipalId,
AppId
| join kind = leftanti (
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by AppId
)
on AppId
| extend ['Days Since Last Logon']=datetime_diff("day", now(), ['Last Successful Logon'])
| project-reorder ['Days Since Last Logon'], ['Last Successful Logon'], ServicePrincipalName, AppId, ServicePrincipalId
| sort by ['Last Successful Logon'] desc Explanation
This query finds Azure AD Service Principals that have not successfully signed on in the last 30 days. It uses the Azure Active Directory - Service Principal Signin Logs data connector.
The query first filters the logs to include only those generated within the last 180 days and with a ResultType of 0 (indicating a successful sign-in). It then groups the logs by AppId and selects the latest log entry for each AppId.
Next, it projects the relevant fields (Last Successful Logon, ServicePrincipalName, ServicePrincipalId, AppId) and performs a left anti-join with another set of logs generated within the last 30 days. This ensures that only Service Principals that have not had a successful sign-on in the last 30 days are included.
The query then calculates the number of days since the last successful logon and reorders the fields. Finally, it sorts the results by the Last Successful Logon date in descending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators