Must Learn KQL Part 2: Just Above Sea Level
Chapter 2
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.
…
For this post, it’s not yet necessary to utilize the resources provided in Part 1, but make sure you don’t miss those as they’ll be needed in future posts.
…
To start the journey learning KQL in this Must Learn KQL series, its helpful to understand where the name KQL came from and why the reference makes so much sense. Once you understand the idea behind the query language, a lightbulb should go off and prepare you for the rest of the series through an expanded scope of learning capability.
Plus, not everyone knows about this, so you’ll be the cool kid. And, if you ever play Trivial Pursuit and this question comes up, you’ll win the pie piece and possibly the entire game. How can that not be good knowledge?
The question?
Where does the name Kusto come from? (from Kusto Query Language)To help explain this, I harken back to my childhood. Bear with me for a minute…
Growing up, my family was one-of-those-families that attended church anytime the church doors were open. As such, the majority of my parents’ friends were at church. This meant that they would spend time before and after church services catching up with their friends, sometimes in a local restaurant where they’d all gather to have pie and coffee. Of course, Facebook didn’t exist then, so in-person connections were even more important. Well…and there was pie. My mom, in particular, wanted to catch up with everyone she hadn’t seen in a few days so this meant that our round-trip from home to church and back could take 3-4 hours.
On Sunday nights this was particularly problematic for me in that I wanted to rush home to catch TV shows like the Six Million Dollar Man, The Magical World of Disney, Mutual of Omaha’s Wild Kingdom, and the TV show that’s the topic of our discussion here: The Undersea World of Jacques Cousteau…
That’s right. KQL is named after the undersea pioneer, Jacques Cousteau.
I loved this TV series. It was absolutely enthralling to me to understand that an entire world existed beneath the ocean waves and this unknown world was being brought to me by this wonderful, thick-accented explorer each week who dedicated his life to discovering what existed beneath the surface depths.
So, as you can imagine, I tried my dead-level best every Sunday night to rush my Mom along. It didn’t always work and was mostly just annoying, and you can bet I caught a few groundings from my insistence. But, still, this topic of discovering the undiscoverable drove me to concoct every type of machination imaginable to get home sooner on Sunday nights. I can’t tell you the number of times I faked illness on Sunday afternoon in attempt to stay home Sunday night. And, as you can imagine my Mom quickly caught on and instituted a policy that if I stayed home on Sunday nights, I couldn’t go to school on Monday. Which…at the time…I truly loved school, so that halted that plan. Give me a few years, and that wouldn’t have worked. Timing is everything.
So, KQL is named after Jacques Cousteau. Even today, you can find evidences of this in our own Azure Monitor Docs. If you go to the datatable operator page right now, you’ll still find a reference to Mr. Cousteau in an example that lists his date of birth, the date he entered the naval academy, when he published his first book entitled “The Silent World: A Story of Undersea Discovery and Adventure,” and the date of his passing.
So, I hope you’re catching on to this. If not, what is it that we are trying to accomplish when we query data tables for security purposes? What is that we’re trying to accomplish though Hunting exercises and operations?
The answer? We are exploring the depths of our data. We are attempting to surface the critical and necessary security information that will tell us about potential exposure through simple, powerful queries.
Much like the story of the failed voyage of the Titanic. It wasn’t the beautiful, pristine, easy-to-see and avoid iceberg mass that existed above the surface of the ocean that sunk the unsinkable ship and sent over 1,500 people to their grave. No, it was the huge mass under the surface that the captain and crew couldn’t see and couldn’t swerve to avoid that doomed the luxury passenger liner.
And, like that, its the information that exists underneath the viewable rows and columns of data in our tables that we need to expose to identify threats and compromise and use to guard the gates. Just the initial rows and columns of exposed data isn’t enough. We have to delve into the depths of the data to find actionable information. And, we need to do it quickly.
I hope all this makes sense.
Its as important to know why we do things, sometimes, as how to do them. Like Jacque Cousteau, security folks are explorers. We are mining the depths of the data no one sees to protect the environment against ever-growing and constantly evolving threats. We are discovering the undiscoverable.
KQL is an amazing and important piece of this capability. KQL was developed to take advantage of the power of the cloud through clustering and compute. Using this capability, KQL is designed as a well-performing tool to help surface critical data quickly. This a big part of why it works so well and outshines many other query languages like it. KQL was built for the cloud and to be used against large data sets.
As a security person, you know that if a threat exists in the environment, you are on the clock to discover it, report it, investigate it, and remediate. A poorly performing query language can be the biggest barrier to that and actually become a security flaw. I’ve sat with customers who use other query languages and other SIEM-like tools that thought it was status quo that query results would take hours or sometimes days. When I showed that KQL produced those same results in seconds, they were astonished. So, the technology and infrastructure behind the query language is also critically important.
In the next post, I’ll talk about the actual structure of a query. Even though the structure can deviate, understanding a common workflow of a KQL query can have powerful results and help you develop the logic needed to build your own workflows when its time to create your own queries. In addition to being well-performing to enhance efficiency, the query language itself is simple to use and learn which, in turn, makes for more efficiency.
So, while we’re Just Above Sea Level in this post (I hope you now appreciate the reference), we’ll be using KQL as the sonar and diving bell to search the depths of our data.
Stay tuned…
Like this series so far? Tell others about it.