KQL Resources

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 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.

 

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