Using the Where and Sort Operators
Currently in progress
Last updated
Currently in progress
Last updated
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.
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()).
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.
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).
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.
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.
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.
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.
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.
