Using KQL to Identify Suspicious Behavior

Harnessing the Power of KQL for Enhanced Security Monitoring

Identifying and addressing suspicious behavior is critical to protecting sensitive data and organizational assets. The Kusto Query Language (KQL) is a powerful tool that enables security analysts to query large datasets effectively, uncovering patterns of potentially malicious activity. In this post, we'll explore how to use KQL to query for unusual login attempts or failed logins across devices, as well as identify patterns that may indicate potential data exfiltration or unauthorized file access.

NOTE: The following queries are provided as examples only. They are provided to show methods and procedures. Some of the tables my not actually exist.

Querying for Unusual Login Attempts or Failed Logins Across Devices

Unusual login attempts or repeated failed logins are often early indicators of malicious activity, such as brute-force attacks or compromised accounts. KQL provides a structured way to identify these anomalies by analyzing logs from authentication systems.

Basic Query for Failed Logins

To track failed login attempts across devices, you can query the logs for events where the login status indicates failure. Here's an example:

SigninLogs
| where ResultType != 0 // Filter out successful logins
| summarize FailedAttempts = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by FailedAttempts desc

This query:

• Filters out successful login attempts (`ResultType != 0`).

• Groups the results by user, device, and hourly timestamps.

• Counts the number of failed login attempts and sorts them in descending order to highlight the most concerning cases.

Identifying Unusual Login Locations

Attackers often log in from unusual geographic locations. You can use KQL to identify logins from unexpected regions:

SigninLogs
| extend LoginLocation = strcat(LocationDetails.country, ' - ', LocationDetails.state, ', ', LocationDetails.city)
| summarize Count = count() by UserPrincipalName, LoginLocation
| where Count > 1
| order by Count desc

This query extends the dataset by adding a "LoginLocation" field and aggregates login attempts by user and location. Analysts can review the results to spot inconsistencies with known travel patterns or home-office settings.

Detecting Impossible Travel Scenarios

To identify impossible travel scenarios—logins from two distant geolocations within an unreasonably short time—you can use a query like this:

SigninLogs
| summarize LoginTimes = make_set(TimeGenerated, 2), Locations = make_set(LocationDetails, 2) by UserPrincipalName
| where array_length(LoginTimes) == 2
| extend TimeDiff = datetime_diff('minute', todatetime(LoginTimes[0]), todatetime(LoginTimes[1]))
| where TimeDiff < 60
| where Locations[0] != Locations[1]

This query analyzes login timestamps and locations for each user, highlighting cases where two consecutive logins occur within an hour but from different locations.

Identifying Patterns of Data Exfiltration or Unauthorized File Access

Detecting data exfiltration or unauthorized file access requires analyzing file activity logs for unusual patterns. These patterns may include large file downloads, access to sensitive files by unusual users, or spikes in file access activity.

Tracking Large File Downloads

To identify large file downloads, use a query that targets specific thresholds for file size:

DeviceFileEvents
| where ActionType == "FileDownloaded" and FileSize > 10000000 // Files larger than 10 MB
| summarize DownloadCount = count() by InitiatingProcessAccountUpn, FolderPath
| order by DownloadCount desc

This query highlights users and file paths associated with large downloads. It can reveal potential cases of data exfiltration.

Unusual Access to Sensitive Files

For sensitive files, you can create a query to detect unusual users accessing them:

DeviceFileEvents
| where FolderPath has_any ("confidential", "sensitive", "financial")
| summarize AccessCount = count() by InitiatingProcessAccountUpn, FolderPath
| order by AccessCount desc

This query filters events for files containing key terms (e.g., "confidential" or "financial") in their names and summarizes access patterns by user.

Detecting Spikes in File Activity

Unusual spikes in file access activity often precede data exfiltration attempts. Here's how to query for this pattern:

DeviceFileEvents
| where ActionType in ("FileAccessed", "FileModified")
| summarize ActivityCount = count() by bin(Timestamp, 1h), InitiatingProcessAccountUpn
| where ActivityCount > 100
| order by ActivityCount desc

This query aggregates file activity by user and hourly timestamps, flagging instances where the number of file actions exceeds a preset threshold.

TLDR

KQL empowers security analysts to proactively monitor and investigate suspicious behavior across an organization's IT environment. By querying for unusual login attempts, failed logins, and patterns of data exfiltration or unauthorized file access, organizations can detect and mitigate threats early.

The examples provided here offer a starting point for leveraging KQL to enhance your security monitoring. Tailor these queries to your organization's specific needs and log sources for optimal results. In the ongoing battle against cyber threats, understanding how to harness tools like KQL is a vital step toward safeguarding your digital assets.

Learn more

• Must Learn KQL - the blog series, the book, the completion certificate, the video channel, the merch store, the workshop, and much more... https://aka.ms/MustLearnKQL

• The Definitive Guide to KQL: Using Kusto Query Language for operations, defending, and threat hunting https://amzn.to/42JRsCL