KQL Search

This query is designed to analyze and summarize Active Directory Federation Services (ADFS) extranet lockout events over the past 30 days. Here's a simple breakdown of what it does:

1. Data Source: It uses data from the ADFSSignInLogs, which is part of the Azure Active Directory data connector.

2. Exclusion: It excludes any IP addresses that are on a predefined allowlist (trusted IPs).

3. Time Frame: It focuses on events that occurred in the last 30 days.

4. Event Type: It specifically looks for events with a ResultType of 396083, which indicates extranet lockout events.

5. Aggregation: For each user (identified by their UserPrincipalName), it calculates:

   • The total number of lockout events (LockoutCount).
   • The number of unique IP addresses involved (UniqueIPs).
   • A list of up to 25 IP addresses that were involved (IPs).
   • A list of up to 10 countries from which the lockout attempts originated (Countries).
   • A list of up to 10 applications that were targeted (Apps).
   • The first and last time the lockout events were seen (FirstSeen and LastSeen).
   • The number of days on which lockout events occurred (ActiveDays).

6. Sustained Campaign Detection: It flags users who experienced lockout events on three or more different days as being potentially targeted by a sustained brute-force campaign (SustainedCampaign).

7. Ordering: The results are ordered by the number of lockout events in descending order, highlighting users with the most lockout activity.

Overall, this query helps identify users who might be under attack from brute-force attempts by analyzing lockout patterns and excluding known safe IPs.