Must Learn KQL Part 4: Search for Fun and Profit
Chapter 4
This post is part of an ongoing series to educate about the simplicity and power of the Kusto Query Language (KQL). If you’d like the 90-second post-commercial recap that seems to be a standard part of every TV show these days…
The full series index (including code and queries) is located here:
The book version (pdf) of this series is located here:
https://github.com/rod-trent/MustLearnKQL/tree/main/Book_Version
The book will be updated when each new part in this series is released.
…
Now that we have some understanding of the workflow (from Part 3) under our belts, I’m going to deviate from that for a brief minute in this post and then I’ll bring it back together in Part 5 and combine Parts 4 and 5 to provide something extra meaningful to show you how it all fits together like an unsolved Hardy Boys mystery novel. Hopefully, you’re starting to see that my efforts here are logical and designed to accumulate enough knowledge that is necessary to move to the next plane of understanding.
What I want to do in this post, is give you something you can actually use today. When I’m done here, you should be able to take the knowledge and the query snippets to do your own hunting – or, rather, look inside your own environment to get an understanding of what is happening that’s worth exposing and investigating.
One of the easiest ways to get started with KQL is the search operator. In Part 3, I talked through the structure and workflow of a search query. In this post, I’ll talk about the search operator (or command) and how it could be the most powerful KQL operator in the universe but will always be the best tool in the toolbelt to start any search operation.
Search is the first operator I reach for when trying to verify if something exists within the environment. In fact, our whole goal for using KQL as a security tool is to answer the following questions:
Does it exist?
Where does it exist?
Why does it exist?
BONUS: There’s a final question to this that’s not part of this KQL series, but one that’s important to the total equation and one that should be part of your SOC processes. That question is: How do we respond?
If you click or tap the image to open it in a larger view, you’ll see how the power of the search operator enables you to answer these questions.
Its starts with an idea or theory that “something” exists in the environment. You may have gotten this idea from a dream or nightmare that someone in your organization is performing nefarious activities. But, most likely, the idea came from a news report or a post on social media from a trusted source about a nation-state actor being active with a new kind of ransomware.
Once these reports are available, someone (like Microsoft) will supply the Indicators of Compromise (IOCs) so you can search your environment to see if they exist. IOCs could be a number of things including filenames, file hashes, IP addresses, domain names, and more.
If they don’t exist, you move on. If any of them do exist, you start to dig deeper to figure out where they exist, so you can, for example, quarantine systems or users, or block IP addresses or domains.
And, then you need to determine why they exist. Did a specific user click on something they shouldn’t have clicked on in an email? Or did a threat actor successfully compromise a Domain Controller through control over a service or elevate user account? Could it be that there is more impact on your environment than you originally thought?
All of this can be exposed through the simple process of search using the search operator.
Let’s walk through this together with a few simple queries that you can take and use to test your own environment. (click or tap the image to open the larger version in a new browser tab to following along)
In step 1 in the image, I’m performing a simple search for a username. In this case, it’s an ego search – I’m searching in my own environment for my own activity. This could be an IOC that you want to search for. Just replace my name with the string of text you want to expose in the results.
search "rodtrent"As you can see in the image, my search produced results, telling me that this thing I searched for does exist in my environment.
Since it does exist, I want to understand where it exists. I do this by making a simple adjustment to my original query by adding a line that tells the query engine to just show me the specific tables that my IOC exists in. This will give me a good indication of what type of activity it was. Step 2 shows…
search "rodtrent"
| distinct $tableLet’s assume that I’m looking for user activity because the reported threat is malware. I know that user activity is most generally recorded and contained in a few places including Microsoft Office and Defender for Endpoint.
In my example in Step 3 in the image, I’ve adjusted my search operator query to focus only on the OfficeActivity table. Here what that looks like:
search in (OfficeActivity) "rodtrent"Now that I have my results of rodtrent’s activity in the OfficeActivity table, I can begin sifting through the rows and columns of data to learn more about the occurrence and to start to tune my query even more.
When we come back for Part 5, I’ll show you how to turn your search query into a workflow like I talked about in Part 3.
One last thing for this post. I mentioned that user activity is generally reported from the Microsoft Office and Defender for Endpoint tables. I’ve given you examples for searching the OfficeActivity table. But Defender for Endpoint is more than one table. In fact, Defender for Endpoint consists of the following 10 tables: DeviceEvents, DeviceFileCertificatelnfo, DeviceFileEvents, DevicelmageLoadEvents, Devicelnfo, DeviceLogonEvents, DeviceNetworkEvents, DeviceNetworklnfo, DeviceProcessEvents, and DeviceRegistryEvents.
Fortunately, the KQL search operator supports the wildcard character. So, you can search for those IOCs across the entire Defender for Endpoint solution by doing the following:
search in (Device*) "rodtrent"And, incidentally, if you have the Defender for 365 Data Connector enabled for Microsoft Sentinel and you enable the Microsoft Defender for Office 365 logs, the OfficeActivity table isn’t the only Microsoft Office data you can query. Enabling these logs gives you access to EmailEvents, EmailUrlInfo, EmailAttachmentInfo, and EmailPostDeliveryEvents tables which means you can take advantage of the search operator’s wildcard capability here, too.
All of the query code in this post is contained in the series’ GitHub repo here: https://aka.ms/MustLearnKQL
P.S. Enjoying this series? Share it with someone!