Query Details
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 asc
Explanation
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
AADServicePrincipalSignInLogs,TimeGenerated,AppId,ResultType,ServicePrincipalName,Month
Operators
projectwheresummarizearg_maxmake_setMonthstartofmonthsort