Top 10 Basic Security KQL Queries for Microsoft Sentinel

Microsoft Sentinel is a powerful, cloud-native security information and event management (SIEM) solution that provides intelligent security analytics and threat intelligence across the enterprise. One of the key features of Microsoft Sentinel is its use of Kusto Query Language (KQL) for querying and analysing data. In this blog post, we will highlight the top 10 basic security related KQL queries that security analysts can use with Microsoft Sentinel, explaining what each query does and how it can be beneficial in a security operations centre (SOC). Additionally, we will provide examples of each query’s output to give you a better understanding of the results you can expect.

1. Failed Login Attempts

This query helps identify failed login attempts, which can be an indicator of brute-force attacks or unauthorised access attempts.

SigninLogs
| where ResultType == "50126" or ResultType == "50053"
| summarize Count = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

UserPrincipalName	TimeGenerated	Count
[email protected]	2025-05-12 13:00:00	15
[email protected]	2025-05-12 12:00:00	10

This output shows the number of failed login attempts by user and time.

2. Successful Login Attempts

Monitoring successful login attempts is crucial for understanding user behaviour and detecting potential account compromises.

SigninLogs
| where ResultType == "0"
| summarize Count = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

UserPrincipalName	TimeGenerated	Count
[email protected]	2025-05-12 13:00:00	20
[email protected]	2025-05-12 12:00:00	18

This output shows the number of successful login attempts by user and time.

3. Brute-Force Attacks

Detecting brute-force attacks is essential for preventing unauthorised access to systems.

SigninLogs
| where ResultType == "50126" or ResultType == "50053"
| summarize Count = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where Count > 10
| order by Count desc

Example Output:

UserPrincipalName	TimeGenerated	Count
[email protected]	2025-05-12 13:00:00	25
[email protected]	2025-05-12 12:00:00	12

This output identifies users with more than 10 failed login attempts within an hour.

4. Account Lockouts

Account lockouts can be a sign of repeated failed login attempts or potential malicious activity.

SigninLogs
| where ResultType == "50053"
| summarize Count = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where Count > 5
| order by Count desc

Example Output:

UserPrincipalName	TimeGenerated	Count
[email protected]	2025-05-12 13:00:00	8
[email protected]	2025-05-12 12:00:00	6

This output identifies users with more than 5 account lockouts within an hour.

5. User Account Changes

Tracking changes to user accounts helps in auditing and detecting unauthorised modifications.

AuditLogs
| where OperationName == "Add member to role" or OperationName == "Remove member from role"
| summarize Count = count() by TargetResources, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

TargetResources	TimeGenerated	Count
[email protected]	2025-05-12 13:00:00	5
[email protected]	2025-05-12 12:00:00	3

This output shows the number of role changes by target resources and time.

6. Privileged Account Usage

Monitoring privileged account usage is critical for detecting potential misuse of administrative privileges.

SigninLogs
| where UserPrincipalName has "admin"
| summarize Count = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

UserPrincipalName	TimeGenerated	Count
[email protected]	2025-05-12 13:00:00	10
[email protected]	2025-05-12 12:00:00	8

This output shows the number of login attempts by privileged accounts and time.

7. Suspicious IP Addresses

Identifying login attempts from suspicious IP addresses can help in detecting potential threats.

SigninLogs
| where IPAddress in ("192.168.1.1", "10.0.0.1")
| summarize Count = count() by IPAddress, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

IPAddress	TimeGenerated	Count
192.168.1.1	2025-05-12 13:00:00	5
10.0.0.1	2025-05-12 12:00:00	3

This output shows the number of login attempts from specific IP addresses and time.

8. Unusual Login Locations

Detecting login attempts from unusual locations can help identify compromised accounts.

SigninLogs
| where Location not in ("US", "UK")
| summarize Count = count() by Location, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

Location	TimeGenerated	Count
China	2025-05-12 13:00:00	7
Russia	2025-05-12 12:00:00	4

This output shows the number of login attempts from unusual locations and time.

9. Malware Detection

Detecting malware activity is crucial for preventing infections and mitigating damage.

SecurityEvent
| where EventID == 1116
| summarize Count = count() by Computer, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

Computer	TimeGenerated	Count
computer1	2025-05-12 13:00:00	3
computer2	2025-05-12 12:00:00	2

This output shows the number of malware detection events by computer and time.

10. Data Exfiltration

Monitoring for data exfiltration attempts helps in preventing data breaches.

NetworkSession
| where BytesSent > 1000000
| summarize Count = count() by SourceIP, bin(TimeGenerated, 1h)
| order by Count desc

Example Output:

SourceIP	TimeGenerated	Count
192.168.1.2	2025-05-12 13:00:00	2
10.0.0.2	2025-05-12 12:00:00	1

This output shows the number of data exfiltration attempts by source IP and time.

Conclusion

These top 10 basic security KQL queries provide a comprehensive toolkit for security analysts using Microsoft Sentinel. By leveraging these queries, analysts can enhance their ability to detect threats, respond to incidents, and maintain a secure environment. Whether you are monitoring login attempts, tracking account changes, or detecting malware, these queries offer valuable insights and help in proactive threat hunting.

Feel free to customise these queries to fit your specific environment and security needs. Happy querying!