KQL queries used in Azure and Defender for Endpoint logs and alerts

 

KQL Queries with Explanations and Use Cases

Here are 10 common KQL queries used during a data breach due to phishing and malware, including user activities, network traffic, and system events, using data from Azure Active Directory logs and Defender for Endpoint alerts. Each query includes placeholders for malicious indicators and step-by-step explanations.

1. Suspicious Logins by User

SigninLogs
| where TimeGenerated >= ago(24h)
| where IPAddress in ('{MaliciousIP}')
| summarize LoginCount = count() by UserPrincipalName, IPAddress

Explanation:

• SigninLogs: Table containing login records.
• TimeGenerated >= ago(24h): Filter to the last 24 hours.
• IPAddress in ('{MaliciousIP}'): Filter by malicious IP addresses.
• summarize LoginCount = count() by UserPrincipalName, IPAddress: Summarize login counts by user and IP address.

Use Case: Identifies suspicious login attempts from known malicious IP addresses.

2. Malware Alerts by Device

DeviceEvents
| where Timestamp >= ago(24h)
| where FileHash in ('{MaliciousHash}')
| summarize AlertCount = count() by DeviceName, FileHash

Explanation:

• DeviceEvents: Table containing device-related events.
• Timestamp >= ago(24h): Filter to the last 24 hours.
• FileHash in ('{MaliciousHash}'): Filter by malicious file hashes.
• summarize AlertCount = count() by DeviceName, FileHash: Summarize alert counts by device and file hash.

Use Case: Tracks devices affected by known malware.

3. Failed Login Attempts

SigninLogs
| where TimeGenerated >= ago(24h)
| where ResultType == "50126" or ResultType == "50076"
| summarize FailedAttempts = count() by UserPrincipalName

Explanation:

• SigninLogs: Table containing login records.
• TimeGenerated >= ago(24h): Filter to the last 24 hours.
• ResultType == "50126" or ResultType == "50076": Filter for specific failed login codes.
• summarize FailedAttempts = count() by UserPrincipalName: Summarize failed login attempts by user.

Use Case: Detects accounts targeted by brute-force attacks.

4. Suspicious URL Access

DeviceNetworkEvents
| where Timestamp >= ago(24h)
| where RemoteUrl in ('{MaliciousURL}')
| summarize AccessCount = count() by DeviceName, RemoteUrl

Explanation:

• DeviceNetworkEvents: Table containing network events.
• Timestamp >= ago(24h): Filter to the last 24 hours.
• RemoteUrl in ('{MaliciousURL}'): Filter by malicious URLs.
• summarize AccessCount = count() by DeviceName, RemoteUrl: Summarize access counts by device and URL.

Use Case: Identifies devices accessing known phishing or malicious sites.

5. Admin Activities

AuditLogs
| where TimeGenerated >= ago(24h)
| where OperationName in ("Add member to role", "Reset password", "Delete user")
| summarize ActivityCount = count() by InitiatedBy

Explanation:

• AuditLogs: Table containing audit logs.
• TimeGenerated >= ago(24h): Filter to the last 24 hours.
• OperationName in ("Add member to role", "Reset password", "Delete user"): Filter for specific admin activities.
• summarize ActivityCount = count() by InitiatedBy: Summarize activity counts by initiator.

Use Case: Monitors potentially malicious administrative activities.

6. Elevated Privileges

AADSignInLogs
| where TimeGenerated >= ago(24h)
| where UserPrincipalName contains "admin"
| where ResultType == 0
| summarize PrivilegedLogins = count() by UserPrincipalName, AppDisplayName

Explanation:

• AADSignInLogs: Table containing Azure AD sign-in logs.
• TimeGenerated >= ago(24h): Filter to the last 24 hours.
• UserPrincipalName contains "admin": Filter for admin accounts.
• ResultType == 0: Filter for successful logins.
• summarize PrivilegedLogins = count() by UserPrincipalName, AppDisplayName: Summarize privileged logins by user and application.

Use Case: Detects unusual or suspicious logins by admin accounts.

7. Unusual Data Transfers

DeviceFileEvents
| where Timestamp >= ago(24h)
| where ActionType == "FileCopied" or ActionType == "FileMoved"
| summarize TransferCount = count() by InitiatingProcessAccountName, DestinationDeviceName

Explanation:

• DeviceFileEvents: Table containing file events.
• Timestamp >= ago(24h): Filter to the last 24 hours.
• ActionType == "FileCopied" or ActionType == "FileMoved": Filter for file transfer actions.
• summarize TransferCount = count() by InitiatingProcessAccountName, DestinationDeviceName: Summarize transfer counts by account and destination device.

Use Case: Identifies potential data exfiltration activities.

8. Phishing Email Clicks

EmailEvents
| where Timestamp >= ago(24h)
| where Url in ('{MaliciousURL}')
| summarize ClickCount = count() by RecipientEmailAddress, Url

Explanation:

• EmailEvents: Table containing email events.
• Timestamp >= ago(24h): Filter to the last 24 hours.
• Url in ('{MaliciousURL}'): Filter by malicious URLs.
• summarize ClickCount = count() by RecipientEmailAddress, Url: Summarize click counts by recipient and URL.

Use Case: Tracks users who clicked on phishing links.

9. Endpoint Alert Summary

SecurityAlert
| where TimeGenerated >= ago(24h)
| where AlertSeverity in ("High", "Medium")
| summarize AlertCount = count() by CompromisedEntity, AlertSeverity

Explanation:

• SecurityAlert: Table containing security alerts.
• TimeGenerated >= ago(24h): Filter to the last 24 hours.
• AlertSeverity in ("High", "Medium"): Filter for high and medium severity alerts.
• summarize AlertCount = count() by CompromisedEntity, AlertSeverity: Summarize alert counts by compromised entity and severity.

Use Case: Provides a summary of significant security alerts.

10. Suspicious Processes

DeviceProcessEvents
| where Timestamp >= ago(24h)
| where InitiatingProcessFileName in ("powershell.exe", "cmd.exe", "{MaliciousProcess}")
| summarize ProcessCount = count() by DeviceName, InitiatingProcessFileName

Explanation:

• DeviceProcessEvents: Table containing process events.
• Timestamp >= ago(24h): Filter to the last 24 hours.
• InitiatingProcessFileName in ("powershell.exe", "cmd.exe", "{MaliciousProcess}"): Filter for specific processes.
• summarize ProcessCount = count() by DeviceName, InitiatingProcessFileName: Summarize process counts by device and process.

Use Case: Detects potentially malicious processes running on devices.

Interpreting Summary Statistics

For each query, the summary statistics provide a count of events grouped by relevant categories (e.g., user, device, IP address). Here's how to interpret them:

1. Login Counts: Higher counts of logins from malicious IPs may indicate a targeted attack.
2. Alert Counts: Frequent alerts related to specific devices or files can signal compromised endpoints.
3. Failed Attempts: A high number of failed logins can suggest brute-force attacks.
4. Access Counts: Repeated access to malicious URLs can indicate phishing activity.
5. Activity Counts: Unusual admin activities may point to privilege escalation.
6. Privileged Logins: Multiple logins by admin accounts need to be verified for legitimacy.
7. Transfer Counts: Unusual data transfer activity might be a sign of data exfiltration.
8. Click Counts: Users clicking on phishing links should be warned and investigated.
9. Alert Severity: A summary of high and medium alerts helps prioritize incident response.
10. Process Counts: Monitoring suspicious processes can reveal malware behavior.

This comprehensive set of queries and guidance should assist you in effectively identifying and responding to phishing and malware-related breaches using Azure and Microsoft Defender.

Resources 

https://www.kqlsearch.com/

https://www.kqlsearch.com/queryassistant

https://www.kqlsearch.com/querygenerator

https://www.kqlsearch.com/querylab

https://sigconverter.io/

https://tdm.socprime.com/login

KQL Security Sources - 2024 Update - Kusto Query Language | KQLQuery.com