Using the Where and Sort Operators

Currently in progress

KQL Operators

As a security analyst or engineer, understanding different KQL operators is essential. It would enable you to seamlessly filter, manipulate, and analyze large amounts of logs quickly and efficiently. Different operators perform different functions. Some are comparison operators that help you pinpoint specific events, and some are aggregation operators that allow you to summarize events. Whether monitoring system performance or hunting for threats, mastering KQL is critical to fully utilizing ingested logs in Microsoft Sentinel.

Okay, let us take a closer look at some of these key operators. In the upcoming tasks, including this one, we will focus primarily on various operators and how they are used in queries.

Where Operator

The where operator filters logs based on specific conditions. It allows you to selectively retrieve rows from a table that meet certain criteria, making it an essential tool for narrowing down the scope of your analysis.

Conditions with the where operator can include comparison operators (e.g., equals, not equals, greater than, less than), logical operators (e.g., AND, OR), and even functions (e.g., startswith(), contains()).

Examples

Finding All the Security Events in the Past 3 Hours

This query will give an output of all the security events in the past 3 hours.

SecurityEvent
| where TimeGenerated > ago(3h)

Finding All Security Events for a Particular Computer

The query below returns all the security events associated with the computer defined. Since no duration is determined, it uses the time range value at the top of the query editor (24 hours).

SecurityEvent
| where Computer == "DC11.na.contosohotels.com"

Finding All Virtual Machine Connections From a Particular Location

The below query returns all virtual machine connections from Ireland in the past 48 hours from the VMConnection table. Scroll to the right to see more columns.

VMConnection
| where RemoteCountry == "Ireland"

Sort Operator

The sort operator arranges the rows of the output table in a specific order based on one or more columns, either ascending or descending. A similar operator is the order by operator, which is essentially an alias for the sort by operator and is used for the same purpose.

Examples

Arrange an Output by a Specific Column in Ascending Order

The query below retrieves failed logon attempts from the SecurityEvent table, filters them by event ID, and sorts the output by the account column in ascending order.

SecurityEvent
| where EventID == 4625
| sort by Account asc

Arrange an Output by a Specific Column in Descending Order

The query below retrieves machines with potential malware detected from the ProtectionStatus table and sorts them by computer in descending order. The first where filters the logs by the TimeGenerated column, in this case, 1 hour ago, while the second where filters the ThreatStatus column with the entry "No threats detected". Scroll to the side for more column details.

ProtectionStatus
| where TimeGenerated > ago(1h)
| where ThreatStatus != "No threats detected"
| sort by Computer desc

Using the "Order By" Operator to Sort a Query Output

The query filters the VMConnection table for entries where the Computer column is "DC11.na.contosohotels.com" and the RemoteCountry column is "France," then sorts the results in ascending order by the ProcessName column.

VMConnection
| where Computer == "DC11.na.contosohotels.com"
| where RemoteCountry == "France"
| order by ProcessName asc