KQL Search

Query Details

Stale On Prem Accounts With Cloud Sign Ins

Query

let OnPremUsers =
IdentityLogonEvents
| where TimeGenerated > ago(90d)
| where Application == "Active Directory"
| where ActionType == "LogonSuccess"
| summarize LastOnPremLogon=max(TimeGenerated) by AccountUpn;
let CloudUsers =
IdentityLogonEvents
| where TimeGenerated > ago(90d)
| where Application != "Active Directory"
| where ActionType == "LogonSuccess"
| summarize LastCloudLogon=max(TimeGenerated) by AccountUpn;
CloudUsers
| join kind=leftouter OnPremUsers on AccountUpn
| where isnull(LastOnPremLogon) or LastOnPremLogon < ago(90d) //null indicates they didnt sign-in onprem during the time range
| join IdentityInfo on AccountUpn //Any account in use will have a record in this table within the timeframe 
| where set_has_element(SourceProviders, "ActiveDirectory")
| where set_has_element(SourceProviders, "AzureActiveDirectory") 
| summarize arg_max(TimeGenerated,*) by AccountUpn
| order by LastOnPremLogon desc

Explanation

This query is designed to identify users who have logged into cloud applications but have not logged into on-premises Active Directory within the last 90 days. Here's a breakdown of what the query does:

  1. OnPremUsers Definition:

    • It filters logon events from the last 90 days where the application is "Active Directory" and the action type is "LogonSuccess".
    • It summarizes the most recent successful logon time for each user (identified by AccountUpn) to Active Directory.

  2. CloudUsers Definition:

    • It filters logon events from the last 90 days where the application is not "Active Directory" and the action type is "LogonSuccess".
    • It summarizes the most recent successful logon time for each user to cloud applications.

  3. Join and Filter:

    • It performs a left outer join between CloudUsers and OnPremUsers on AccountUpn, meaning it keeps all users who have logged into cloud applications, regardless of their on-prem logon status.
    • It filters out users who either have no on-prem logon record (isnull(LastOnPremLogon)) or whose last on-prem logon was more than 90 days ago.

  4. Identity Information Enrichment:

    • It joins the filtered results with the IdentityInfo table to enrich the data with additional identity information.
    • It ensures that the users have records in both "ActiveDirectory" and "AzureActiveDirectory" within the SourceProviders.

  5. Final Output:

    • It summarizes the most recent logon event for each user and orders the results by the last on-prem logon time in descending order.

In simple terms, this query identifies users who have been active in cloud applications but have not logged into the on-premises Active Directory in the past 90 days, providing additional identity information for these users.