Need a query to discover all Azure applications and registrations that haven't signed in greater than 90 days

Hi @Jeff Taylor (CE CEN) ,

To design a KQL query to find service principals that haven't had sign-ins in more than 90 days, you could look at the service principal sign-in logs and AAD audit logs and use a query like this:

AADServicePrincipalSignInLogs
| where TimeGenerated > ago (90d)

Doing some digging, I found a few KQL queries written by Matthew Zorich from the Sentinel team. These examples look pretty close to what you are looking for.

This sample finds any Azure AD service principals that have been granted any .All access in the last year that haven't signed in for 30 days.

And this example summarizes 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   

-
Please Accept the answer if the information helped you. This will help us and other community members as well.