Kusto Query Language (KQL)#
Kusto Query Language (KQL) is the query language used by Microsoft security products for querying events. On premisis, this is Defender, and in the cloud it’s Sentinel. Being a DB query language, you’ll feel right at home if you have any experience in SQL. You have tables, rows, columns, joins etc etc. That being said, you also have to change your mindset for Kusto.
Key Features#
Sequential Queries#
Now, we all know that computers are sequential under the hood, but I found this was more explicit with KQL. The whole structure of KQL is pipes (|) between commands, where each command is a simple task.
When learning SQL, you may have come across a layout that looked a lot like this
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
In KQL, We are always querying historic data. Therefore we are always selecting, it doesnt need to be defined. We simply have the table name
MyTable
As the tables are likely quite large, we need to structure our queries to efficiently achieve our goals. We have selected our table, but there may be many columns in a table (depending on your structure). The FROM command has already been done as our first command. To select specific tables we must used the PROJECT command. To me, the project command could mean two things, we are either building a project, or projecting certain columns. Pick your favourite as long as it helps you remember things. The project command is also used for calculating new columns. If we want to select name & age, we would have
MyTable
| Project Name, Age
If you wanted only adults (I’m using 21 here), we would then use the where command. This would be the equivilant of “Select Name, Age from MyTable where Age > 20”
MyTable
| Project Name, Age
| Where Age > 20
As you can see, we are very strictly sequential, with small commands per row. We are still querying database, but the way we structure our commands has changed.
Practice#
A great tool for learning KQL is the “Kusto Detective Agency”. It is a free resource provided by Microsoft in partnership with AMD, and it’s fully online. Like a lot of cyber study, it is a gamified series where you are given challenges to solve along a theme and story, with increasing levels of difficulty and complexity. The first challenge takes you through setting up your environment, so even if you haven’t worked with Azure or Data Explorer before you can get started. I highly recommend the series and will be walking through it in further chapters of this book.
https://detective.kusto.io/