This is something I’ve been meaning to write for some time. In what seems like forever ago, I wrote a post about how Kusto is becoming the PowerShell of query languages. In this page I want to provide a list of resources for each service that is using KQL as well as repos, and blog posts. Many of these resources I keep in a Edge collection and I find them useful, so you may too.
As of this writing Kusto is found in these products:
- Log Analytics:
- Azure Monitor
- Azure Sentinel
- Azure Security Center
- Application Insights
- Azure Data Explorer
- Azure Resource Graph
- M365 Advanced Threat Protection
- CMPivot
Official Docs
Note: Not all services that use Kusto use the entire language. Azure Resource Graph and CMPivot for instance use a subset of KQL
Azure Data Explorer, Log Analytics, and Advanced Threat Protection all link to the KQL docs here https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/
Azure Resource Graph https://docs.microsoft.com/en-us/azure/governance/resource-graph/concepts/query-language
CMPivot https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/cmpivot
Official Training Material
MS Learn: Write Your First KQL Query https://docs.microsoft.com/en-us/learn/modules/write-first-query-kusto-query-language/
Official and Unofficial Community Repos
Azure Monitor
https://github.com/microsoft/AzureMonitorCommunity
Azure Sentinel
https://github.com/Azure/Azure-Sentinel
https://github.com/ashwin-patil/blue-teaming-with-kql/
Azure Resource Graph
https://github.com/sharegate/azure-quickstart-resource-graph
https://github.com/tyconsulting/AzureResourceGraph
https://github.com/scautomation/AzureResourceGraph-Examples
CMPivot
https://github.com/microsoft/configmgr-hub
https://systemcenterdudes.com/sccm-cmpivot-query/
Blogs and Video content by Others
KQL Cheat Sheet https://github.com/marcusbakker/KQL/blob/master/kql_cheat_sheet_v01.pdf
PDF download cheat sheet
KQL Internals https://security-tzu.com/2020/08/07/become-a-kql-ninja/
is a PDF Download that takes you through KQL
KQL From Scratch from Pluralsight – https://www.pluralsight.com/partners/microsoft/azure-data-explorer
KQL Tutorial Series from TeachJing https://www.youtube.com/watch?v=UwcBvVkTCpc
My KQL Posts
The other part of this post that I wanted to do was to index my example posts. If you’ve read any of my Kusto posts before, you know I have a hard time translating some of the examples in the official docs to real world use, so I like to use real world data in my examples. With each post I will list what operators are used, what tables it uses, and or what product its using. I will also add new posts at the top as I come out with them.
Building Azure Monitor Alert Reports with Azure Resource Graph https://www.cloudsma.com/2022/03/azure-monitor-alert-reports/
KQL Operators: mv-expand, iff, case, isnull, isempty, project, join, summarize, pack_all
Tables: resources, alertsmanagementresources
Azure Resource Graph: Zero to Hero https://techcommunity.microsoft.com/t5/itops-talk-blog/azure-resource-graph-zero-to-hero/ba-p/2303572
KQL Operators: mv-expand, tolower, tostring, iff, isempty, where, summarize, distinct, extend, project
Tables: resources
Kusto Make-Series vs Summarize https://www.cloudsma.com/2021/04/kusto-make-series-vs-summarize/
KQL operators: summarize, countif, sumif, avg, startofyear, startofmonth, startofweek, now, make-series, join, series_stats_dynamic, series_decompose_forecast, mv-expand
Tables: custom logs
Find Orphaned Azure Resources https://www.cloudsma.com/2021/02/find-orphaned-azure-resources/
KQL operators: where, has, !has, extend, tostring, project, isnull
Tables: Resources in Azure Resource Graph
Evaluate Operator https://www.cloudsma.com/2020/12/kusto-evaluate-operator-plugins/
KQL operators: Evaluate Pivot, Evaluate Bag_Unpack, Evaluate Narrow, project, todynamic, summarize
Tables: AzureActivity, Perf, ConfigurationData
examples in Log Analytics
Has, Contains and In Operators https://www.cloudsma.com/2020/07/log-analytics-operators-has-contains-and-in/
KQL Operators: Has, Contains, In, where
Tables: Custom Logs, AzureDiagnostics, Syslog
examples in Log Analytics
Calculate FSLogix Profile Load Time https://www.cloudsma.com/2020/09/calculate-wvd-profile-load-time/
KQL Operators: Join, summarize, project, where, sort, extend, parse, replace
Tables: WVDConnections, Event, SecurityEvent examples in Log Analytics
Substring and Trim https://www.cloudsma.com/2019/02/log-analytics-substring-trim/
KQL Operators: Substring, Trim, extend, project
Tables: Custom Logs
examples in Log Analytics
Extracting Nested Fields in Kusto https://www.cloudsma.com/2020/01/extracting-nested-fields-kusto/
KQL Operators: Evaluate Bag_unpack, Parse_XML, project, extend
Tables: Resources, SigninLogs
examples in Log Analytics and Azure Resource Graph
Curious Capabilities of Case https://www.cloudsma.com/2019/07/curious-capabilities-case-scalar-function/
KQL Operators: Case, strcat, summarize, extend, where, project
Tables: SecurityEvent, Perf examples in Log Analytics
Log Analytics Heartbeat Table https://www.cloudsma.com/2019/12/log-analytics-heartbeat-table/
KQL Operators: Join, distinct, summarize, max, where
Tables: Heartbeat, ConfigurationData
examples in Log Analytics
Working with Time in Log Analytics https://www.cloudsma.com/2019/05/working-with-time-log-analytics/
KQL Operators: DateTime_Diff, between, totimespan, time, print, extend
Tables: Custom Logs
examples in Log Analytics
Azure Monitor Service Monitoring https://www.cloudsma.com/2018/11/log-analytics-service-monitor/
KQL Operators: distinct, where, project
Tables: ConfigurationData in Log Analytics
Log Analytics Server Performance Report https://www.cloudsma.com/2018/09/azure-log-analytics-server-performance-report/
KQL Operators: Let, join, summarize, evaluate pivot, where, extend, project-away, bin
Tables: Perf in Log Analytics
Azure Log Analytics Summarize Operator https://www.cloudsma.com/2018/09/azure-log-analytics-summarize-operator/
KQL Operators: Summarize, distinct, bin
Tables: Perf in Log Analytics
Azure Log Analytics Project Operator https://www.cloudsma.com/2018/08/azure-log-analytics-project-operator/
KQL Operators: Project, where, project-away
Tables: Perf in Log Analytics
Format TimeGenerated Field Azure Log Analytics https://www.cloudsma.com/2018/08/format-the-timegenerated-field-log-analytics/
KQL Operators: format_datetime, summarize, bin, project
Tables: Perf in Log Analytics
Find High CPU Processes Azure Log Analytics https://www.cloudsma.com/2018/07/cpu-processes-azure-log-analytics/
KQL Operators: Let, join, summarize, project, where, top, extend, sort
Tables: Perf in Log Analytics
Converting, Joining and Summarizing Data in Azure Log Analytics https://www.cloudsma.com/2018/07/converting-summarizing-data-azure-log-analytics/
KQL Operators: Let, join, extend, top, project, where
Tables: Custom Logs in Log Analytics
Azure Log Analytics Distinct Operator https://www.cloudsma.com/2018/06/azure-log-analytics-distinct-operator/
KQL Operators: Distinct, where
Tables: Perf, UpdateSummary in Log Analytics