KQL Search

Query Details

Stale Cloud Accounts With Sign Ins On Prem

Query

let OnPremUsers =
IdentityLogonEvents
| where TimeGenerated > ago(30d)
| where Application == "Active Directory"
| where ActionType == "LogonSuccess"
| summarize LastOnPremLogon=max(TimeGenerated) by AccountUpn;
let CloudUsers =
IdentityLogonEvents
| where Application != "Active Directory"
| where ActionType == "LogonSuccess"
| summarize LastCloudLogon=max(TimeGenerated) by AccountUpn;
OnPremUsers
| join kind=leftouter CloudUsers on AccountUpn
| where isnull(LastCloudLogon) or LastCloudLogon < ago(90d)
| 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") 
| project AccountUpn, LastOnPremLogon, LastCloudLogon
| order by LastOnPremLogon desc

Explanation

This query is designed to identify user accounts that have logged into an on-premises Active Directory environment but have not logged into any cloud applications recently. Here's a breakdown of what the query does:

  1. OnPremUsers Definition:

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

  2. CloudUsers Definition:

    • It filters logon events where the application is not "Active Directory" and the action type is "LogonSuccess".
    • It summarizes these events to find the most recent logon time for each user in cloud applications.

  3. Join and Filter:

    • It performs a left outer join between OnPremUsers and CloudUsers based on the user account (AccountUpn).
    • It filters the results to include only those users who either have never logged into a cloud application (isnull(LastCloudLogon)) or have not logged into a cloud application in the last 90 days.

  4. Identity Verification:

    • It joins the filtered results with the IdentityInfo table to ensure that the account is recognized by both Active Directory and Azure Active Directory.

  5. Output:

    • It projects the user account (AccountUpn), the last on-premises logon time (LastOnPremLogon), and the last cloud logon time (LastCloudLogon).
    • Finally, it orders the results by the most recent on-premises logon time in descending order.

In simple terms, this query identifies users who have recently logged into on-premises systems but have not accessed cloud services in a while, ensuring these accounts are valid in both Active Directory and Azure Active Directory.