KQL Search

This query is designed to identify Internet Service Providers (ISPs) that have only experienced failed sign-in attempts over the past 30 days. It helps in detecting potentially malicious ISPs and their associated IP addresses. Here's a breakdown of what the query does:

1. Data Source: It starts by looking at the IdentityLogonEvents table.
2. Time Frame: It filters the data to include only events from the last 30 days.
3. Data Selection: It selects (or projects) the columns for ISP, Location, IPAddress, and FailureReason.
4. Summarization: It summarizes the data by counting:
   • valid: The number of successful sign-ins (where FailureReason is empty or contains "Success").
   • failure: The number of failed sign-ins (where FailureReason is not empty and does not contain "Success").
   • It also collects a set of unique IP addresses associated with each ISP and Location.
5. Ordering: It orders the results by the number of failed attempts.
6. Filtering: It filters the results to show only those ISPs where:
   • There are no successful sign-ins (valid == 0).
   • There are more than 5 failed sign-in attempts (failure > 5).

In simple terms, this query finds ISPs that have only failed login attempts, with more than five failures, to help identify suspicious activity.