Must Learn KQL Part 1: Tools and Resources
Chapter 1
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.
…
After hearing that our customers’ largest barrier to using things like Defender, Microsoft Sentinel and even reporting for Intune is KQL, the query language, that was a wake-up call for me. And, of course, (if you know me) I want to do something about it. KQL is a beautifully simple query language to learn. And, believe me – if I can learn it, there’s no question that you can learn it. I feel bad that there’s just not enough knowledge around it because I’ve taken for granted that everyone already had the proper resources to become proficient. But, that’s not the case.
Internally, plans are being developed now to make KQL learning a bigger focus and you’ll see new education around this query language start to take shape in various areas on the Microsoft properties and elsewhere. So, that’s good news for everyone.
There’s bits and pieces already scattered about the Internet, but they are seemingly now difficult to identify and locate.
So, as a first step in a series that I’ll be writing called “Must Learn KQL“, I want to supply some good resources that can be used to accomplish the other things I’ll talk about going forward. Some of these I use everyday. Some I use only when the need arises, but they’re valuable nonetheless. This is a working document, so expect updates over time. This is not a definitive list by any means, so if you have other resources not listed here that you find valuable and believe others would benefit, let me know and I’ll add them in.
Stay tuned as I map out this series. Of course, since my area of forte at Microsoft is security, the series will be security focused. So, the knowledge you gain will help you with our security platforms but also anything data centric that utilizes KQL.
One last tidbit of a tip… I use Microsoft Edge’s Collections feature quite a bit. This is an extremely useful tool for capturing and grouping topics. If you find any of the links below valuable, I suggest using Edge Collections so you can always come back to them later.
Reference
The code repository for this series (GitHub)
Kusto Query Language Reference Guide
Azure Monitor Logs table reference
Marcus Bakker’s Kusto Query Language (KQL) – cheat sheet
Splunk to Kusto Query Language map
Kusto Query Language in Microsoft Sentinel
Useful resources for working with Kusto Query Language in Microsoft Sentinel
Practice Environments
Write your first query with Kusto Query Language (Learn module)
KQL Playground – only need a valid Microsoft account to access.
Data Explorer – not security focused. Contains things like geographical data and weather patterns. Exercises for this can be found in the Learn Azure Sentinel book below.
Actual Books
Learn Azure Sentinel: Integrate Azure security with artificial intelligence to build secure cloud systems – this book uses Data Explorer (see above) for hands-on exercises.
Microsoft Sentinel in Action: Architect, design, implement, and operate Microsoft Sentinel as the core of your security solutions – this book is the next edition of the one just above and also used Data Explorer for hands-on examples.
Tools
Kusto.Explorer – a rich desktop application that enables you to explore your data using the Kusto Query Language in an easy-to-use user interface.
Kusto CLI – a command-line utility that is used to send requests to Kusto, and display the results.
Visual Studio Code with the Kusto extensions pack
Real-Time KQL – eliminates the need to ingest data first before querying by processing event streams with KQL queries as events arrive, in real-time
getschema operator – As I noted in Part 5 of this series: this is the Rosetta stone of KQL operators. When used, getschema displays the Column Name, Column Ordinal, Data Type, and Column Type for a table. This is important information for filtering data. Part 5 talks about this.
Blogs, Websites, and Social
#MustLearnKQL – the official Twitter hashtag of this series
The #365daysofkql hashtag on Twitter
The KQL Cafe = podcast and community
Video
TeachJing’s KQL Tutorial Series
Recon your Azure resources with Kusto Query Language (KQL)
Azure Sentinel webinar: KQL part 1 of 3 – Learn the KQL you need for Azure Sentinel
Azure Sentinel webinar: KQL part 2 of 3 – KQL hands-on lab exercises
Azure Sentinel webinar: KQL part 3 of 3 – Optimizing Azure Sentinel KQL queries performance
Querying Azure Log Analytics (with KQL)
GitHub Query Examples
My GitHub repo for Microsoft Sentinel KQL
The official Microsoft Sentinel repo
Clive Watson’s KQL queries and workbooks
Matt Zorich’s (the originator of the #365daysofkql Twitter hashtag) KQL queries
I'll be taking a look at all these. Thanks for sharing. I must say, the word 'simplicity' wouldn't be the first word I'd use to describe KQL. Hideous might be better. Especially if you look at some of the built in Azure firewall queries.
I've been putting off learning it for years, partly because finding decent material is so difficult. So this will be useful.