KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Find Appswith No Signins

Query

//Find Azure AD applications that have had no signins for over 30 days. May be a sign of an app no longer in use or users bypassing SSO.

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (365d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, *) by AppId
| project
    AppDisplayName,
    ['Last Logon Time']=TimeGenerated,
    ['Days Since Last Logon']=datetime_diff("day", now(), TimeGenerated)
| where ['Days Since Last Logon'] > 30

Explanation

This query finds Azure AD applications that have not had any sign-ins for over 30 days. This could indicate that the app is no longer in use or that users are bypassing single sign-on. The query requires the Azure Active Directory - Signin Logs data connector. It filters the sign-in logs for the past year, selects successful sign-ins, groups them by application ID, and calculates the last logon time and the number of days since the last logon. Finally, it filters for applications with more than 30 days since the last logon.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogs

Keywords

AzureADapplications,SigninLogs,TimeGenerated,ResultType,AppId,AppDisplayName,LastLogonTime,DaysSinceLastLogon

Operators

| where>ago==summarizearg_maxbyprojectdatetime_diff

Actions