• KQL Language
  • Overview
  • Getting Started Queries
  • Tabular operators
    • summarize operator
    • distinct operator
    • project operator
    • count operator
  • aggregation function
    • count aggregation function
    • count_distinct aggregation function
  • Scalar functions
    • isnotempty scalar functions
• KQL Queries
  • LAW Table Usage
  • Get Watch List
• Manage KQL Queries
  • Query Packs
  • Export and Import Saved Queries
• Accessing KQL
• Misc KQL References and Resources

KQL Language

Overview

Kusto
KQL
KQL Quick Reference
KQL Learning Resources
Learn Common Operators
SQL to KQL Cheat Sheet
KQL Regex
KQL Timezones

Syntax conventions for reference documentation - brings the menu to the right place for browsing
Best practices for Kusto Query Language queries

Getting Started Queries

https://learn.microsoft.com/en-au/azure/azure-monitor/logs/get-started-queries

• Structure a query
• Sort query results
• Filter query results
• Specify a time range
• Include or exclude columns in query results - also see project operator
• Define and use custom fields
• Aggregate and group results

Tabular operators

summarize operator

StormEvents
| summarize by EventType

https://learn.microsoft.com/en-au/kusto/query/summarize-operator?view=azure-monitor

distinct operator

StormEvents
| distinct EventType

https://learn.microsoft.com/en-au/kusto/query/distinct-operator?view=azure-monitor

project operator

Allows limiting of columns

StormEvents
| project State

https://learn.microsoft.com/en-au/kusto/query/project-operator?view=azure-monitor

Other project related operators:

• project-away-operator
• project-keep-operator
• project-rename-operator
• project-reorder-operator

count operator

StormEvents | count

https://learn.microsoft.com/en-au/kusto/query/count-operator?view=azure-monitor

aggregation function

count aggregation function

StormEvents
| summarize Count=count() by State

CommonSecurityLog
| summarize Count=count() by DeviceVendor

https://learn.microsoft.com/en-au/kusto/query/count-aggregation-function?view=azure-monitor

count_distinct aggregation function

StormEvents
| summarize UniqueEvents=count_distinct(EventType) by State
| top 5 by UniqueEvents

https://learn.microsoft.com/en-au/kusto/query/count-distinct-aggregation-function?view=azure-monitor

Scalar functions

isnotempty scalar functions

StormEvents
| where isnotempty(BeginLat) and isnotempty(BeginLon)

CommonSecurityLog
| where isnotempty(DeviceVendor)

https://learn.microsoft.com/en-au/kusto/query/isnotempty-function?view=azure-monitor

KQL Queries

LAW Table Usage

union withsource=["$TableName"] *
| summarize Count=count() by TableName=["$TableName"]
| render barchart

Get Watch List

_GetWatchlist('NetworkAddresses')
| extend IPSubnet = ["IP Subnet"]
| extend RangeName = ["Range Name"]
| project IPSubnet,RangeName

Manage KQL Queries

Query Packs

https://learn.microsoft.com/en-us/azure/azure-monitor/logs/query-packs

Export and Import Saved Queries

https://techcommunity.microsoft.com/discussions/microsoftsentinel/export-and-import-saved-queries-and-functions-from-one-sentinel-workspace-to-ano/1910930

Exporting ARM Template: If the query packs are accessible in the Azure Portal site, try exporting the ARM template

Accessing KQL

• Kusto.Explorer
  • Installing Kusto.Explorer
• Az.Operational Insights PowerShell Module - namely:
  • Invoke-AzOperationalInsightsQuery
  • Get-AzOperationalInsightsSavedSearch
  • New-AzOperationalInsightsSavedSearch
• Azure PowerShell - see Access methods Azure VM Tips
• Log Analytics Workspaces in Azure Portal - where I do most of my stuff
• Log Analytics REST API Reference
• Azure Data Explorer - Web based client (there is also a downloadable client)
• Advanced Hunting in Microsoft Defender Portal

Using KQL in PowerShell

Misc KQL References and Resources

• Notes about KQL for Transformations: Supported KQL features in Azure Monitor transformations
• https://github.com/rod-trent/MustLearnKQL - Highly Recommended
• https://github.com/reprise99/Sentinel-Queries - Highly Recommended
• https://github.com/reprise99/awesome-kql-sentinel
• https://github.com/Bert-JanP/Hunting-Queries-Detection-Rules https://www.kqlsearch.com/