KQL in SecOps (2) - Mastering Filtering, Projection, and Sorting

GK
Jan 5
4 min read

Welcome to Part 2 of our KQL in Security Operations Series. Last time, we introduced Kusto Query Language (KQL) and explored why it is critical for SOC analysts using Microsoft Sentinel.

We’ll build on that foundation and dive into the fundamentals of querying data—filtering logs, selecting the right fields, and sorting results. Mastering these skills is essential for writing efficient, actionable KQL queries that power threat hunting, alert investigation, and detection engineering.

By the end of this post, you’ll be able to write your first meaningful Sentinel queries and start analyzing security events like a pro.

Why Filtering, Projection, and Sorting Matter

In a typical SOC, analysts face millions of logs per day. Without narrowing the data, queries can:

Run slowly or time out
Return overwhelming results
Make threat detection difficult

KQL provides three fundamental operators to manage data efficiently:

Filtering (where) – Limit logs to relevant events
Projection (project) – Select only the columns you need
Sorting (sort) – Organize results for analysis

These operations are the building blocks for all KQL queries, from simple lookups to complex detection rules.

Step 1: Filtering Data with where

The where operator is used to filter rows in a table based on conditions. It’s typically the first operator in a query.

Filtering by Event Type

Example: Find failed login attempts in Windows Security Events:

SecurityEvent
| where EventID == 4625

SecurityEvent is the table containing Windows logs
EventID == 4625 filters only failed logon events

Filtering by Time

Security investigations often focus on a specific time window. KQL provides the TimeGenerated column and ago() function for relative filtering:

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625

This query retrieves failed logons in the last 24 hours.

Filtering by String Values

You can filter by usernames, IP addresses, or other string fields:

SigninLogs
| where UserPrincipalName == "jdoe@company.com"

For partial matches, use:

contains – matches anywhere in the string
startswith – matches the beginning
endswith – matches the end

Example: Find sign-ins containing “admin”:

SigninLogs
| where UserPrincipalName contains "admin"

Multiple Conditions

Combine conditions with logical operators:

and – both must be true
or – either can be true
not – negates a condition

Example: Failed logins for a specific account on a specific computer:

SecurityEvent
| where EventID == 4625
| where Account == "jdoe" and Computer == "SERVER01"

Step 2: Selecting Columns with project

By default, KQL queries return all columns in a table, which can be overwhelming. Use project to select only relevant fields:

SecurityEvent
| where EventID == 4625
| project TimeGenerated, Account, Computer, IpAddress

TimeGenerated – timestamp of the event
Account – the user account
Computer – the device name
IpAddress – source IP

You can also rename columns for clarity:

| project Timestamp = TimeGenerated, User = Account, Device = Computer

Excluding Columns

Use project-away to remove unnecessary columns:

SecurityEvent
| project-away EventData, LogonType

This simplifies output without manually listing all columns.

Step 3: Sorting Results with sort and top

Sorting makes it easier to spot anomalies and trends.

Sort by Timestamp

SecurityEvent
| where EventID == 4625
| project TimeGenerated, Account, Computer
| sort by TimeGenerated desc

desc – descending (most recent first)
asc – ascending (oldest first)

Top N Results

To quickly identify the most active accounts or devices, use top:

SecurityEvent
| where EventID == 4625
| summarize FailedLogins = count() by Account
| top 5 by FailedLogins desc

This query shows the 5 users with the most failed logins, a common brute-force indicator.

Combining Filtering, Projection, and Sorting

Here’s a full example:

SecurityEvent
| where EventID == 4625
| where TimeGenerated > ago(24h)
| where Account contains "admin"
| project TimeGenerated, Account, Computer, IpAddress
| sort by TimeGenerated desc

Filters by failed logons in the last 24 hours
Only includes admin accounts
Shows relevant columns
Orders results by most recent first

This query is fast, readable, and actionable—exactly what SOC analysts need for investigations.

Practical Security Use Cases

1. Detecting Suspicious Sign-ins

Identify accounts with multiple failed logins:

SigninLogs
| where ResultType != 0
| summarize FailedAttempts = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where FailedAttempts > 5

bin() groups events into hourly intervals
This helps detect brute-force or credential-stuffing attempts

2. Investigating Endpoint Activity

Look for recent administrative logons on a critical server:

SecurityEvent
| where EventID == 4624
| where Account startswith "admin"
| where Computer == "SERVER01"
| project TimeGenerated, Account, Computer, LogonType
| sort by TimeGenerated desc

3. Preparing Data for Threat Hunting

Select only the necessary fields to feed into advanced queries:

DeviceProcessEvents
| where ProcessName contains "powershell"
| project TimeGenerated, DeviceName, Account, CommandLine
| sort by TimeGenerated desc

This query is a starting point for hunting malicious PowerShell activity.

Common Beginner Mistakes to Avoid

Not filtering by time – querying all logs can slow queries and return irrelevant data
Selecting too many columns – makes analysis harder
Ignoring case sensitivity – string comparisons in KQL are case-sensitive by default
Failing to combine operators logically – can lead to incomplete results
Not testing queries incrementally – build queries step by step for accuracy

Tips for Writing Efficient Queries

Always filter early to reduce the number of rows processed
Only project necessary columns
Use summarize and bin() when grouping events
Test queries interactively before turning them into alerts
Document your queries for future SOC team members

Looking Ahead: Part 3 Preview

Next month, we’ll expand on aggregation and summarization in KQL, including:

Counting events by user, device, or IP
Time-based grouping with bin()
Using summarize for SOC metrics
Detecting anomalies and trends in security events

By Part 3, you’ll be able to analyze security patterns across time and build actionable alerts.

Conclusion

Filtering, projection, and sorting are the cornerstones of effective KQL queries. They allow SOC analysts to narrow down massive log volumes, focus on the relevant data, and uncover security threats quickly.

In this part, you learned:

How to filter events by type, time, or string values
How to project only the necessary columns
How to sort and highlight critical events
Practical security use cases to start using these techniques today

Mastering these fundamentals prepares you for Part 3, where we will dive into aggregation, summarization, and time-based analysis—a crucial step for threat hunting and SOC reporting.

✅ Key Takeaways for Part 2

Filtering with where is your first step in every query.
Project only the columns you need for clarity and speed.
Sort and summarize data to identify anomalies efficiently.
Test queries incrementally and document them for team use.
Combining filtering, projection, and sorting is the foundation of all KQL queries.