Analyze network connection failures by IP: The KQL model to copy

In 80% of cases, a successful cyberattack begins with the compromise of an identity. Before installing malware or exfiltrating financial data, attackers seek to gain valid access to the network. Their preferred methods areBrute Force(test millions of passwords on a single account) or thePassword Spraying(test a few very common passwords on all of a company's employees).

To detect these silent intrusion attempts before they succeed, SOC teams must analyze connection histories. In the Microsoft 365 Defender ecosystem, this translates to querying the tableDeviceLogonEventsvia KQL (Kusto Query Language).

Understanding the DeviceLogonEvents table

The tableDeviceLogonEventslists all authentication and login events occurring on devices managed by the tenant (Tenant). It indicates who connected, from what IP address, at what time, and above all, whether the connection was successful or unsuccessful.

When a hacker launches a brute force attack from an external server (often via an anonymized network or a botnet), he will generate dozens or even hundreds of connection errors in a few minutes.

How to formulate the KQL query?

The goal of the SOC analyst is to isolate external IP addresses that are generating an abnormal volume of failed logins (ActionTypeLogonFailed) on multiple accounts, or repeatedly on a single key account.

Here is the basic KQL query pattern to identify this behavior:

DeviceLogonEvents
| where Timestamp > ago(24h)
| where ActionType == "LogonFailed"
// Exclure les IP locales internes si nécessaire
| where RemoteIPType == "Public" 
| summarize FailedCount = count(), TargetedAccounts = make_set(AccountName) by RemoteIP, DeviceName
| where FailedCount > 10 // Seuil d'alerte pour identifier les attaques automatisées
| sort by FailedCount desc

Explanation of the request:

1. Time filter (Timestamp):We limit the analysis to the last 24 hours to guarantee optimal performance and avoid blocking the API (Rate Limiting).
2. Failure filtering (LogonFailed):We only keep the authentication attempts refused by the system.
3. Aggregation (summarize):This is the most important command. We group the results by remote IP address (RemoteIP) and by machine. We count the number of failures (count()) and we create a unique list of targeted accounts (make_set).
4. Critical threshold (where FailedCount > 10):We filter the background noise (the user who has forgotten his password 2 or 3 times) to isolate the attack machines.

Automating hunting with Akuity SOC

Writing this request manually on the Microsoft Defender portal takes time, especially if you have to adapt it and re-run it for each of your 50 MSSP clients.

With the platformAkuity SOC, this complexity is abstract. The Threat Hunting KQL terminal offers a native template (Template) entitled“Connections Failed”. One click automatically loads optimized KQL logic to list failed network connections by IP and account, and applies it to the selected tenant.

Contextual enrichment and remediation

Once Akuity SOC's dynamic grid returns the list of malicious IPs that triggered more than 10 connection failures, the analyst's work doesn't stop there.

• Assess the threat:The analyst can submit the suspicious IP to the AlienVault OTX assessment engine built into the interface. If the radial gauge indicates a critical score and the “Analyst Ratings” (IA Gemini) confirm that the IP belongs to a known malicious proxy network, there is no longer any room for doubt.
• Counterattack:From the management console, the analyst can inject this Indicator of Compromise (IOC) into Microsoft Defender to preventively block any future communication with this IP for a period of 90 days.

Simplify your network investigations.> Use our ready-to-use KQL templates with ourKQL Multi-Tenant Threat Hunting Consoleand stop brute force attacks before your identities are compromised.