O Auth Summarize Service Principal Inactivity
Query
//Summarize your Azure AD service principals by the last time they signed in, grouped by month
//Data connector required for this query - Azure Active Directory - Service Principal Signin Logs
AADServicePrincipalSignInLogs
| project TimeGenerated, AppId, ResultType, ServicePrincipalName
| where TimeGenerated > ago (360d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by AppId
| summarize ['Application List']=make_set(ServicePrincipalName) by Month=startofmonth(TimeGenerated)
| sort by Month ascExplanation
This query summarizes the Azure AD service principals based on the last time they signed in, grouped by month. It uses the Azure Active Directory - Service Principal Signin Logs data connector. The query filters the data to include only the last 360 days and only successful sign-ins. It then finds the latest sign-in time for each service principal and groups them by the application ID. Finally, it creates a list of service principal names for each month and sorts the results by month in ascending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
AADServicePrincipalSignInLogs
Keywords
AADServicePrincipalSignInLogsTimeGeneratedAppIdResultTypeServicePrincipalNameMonth
Operators
projectwheresummarizearg_maxmake_setMonthstartofmonthsort