Kusto Query Language, KQL

This is a resource on Kusto Query Language, KQL. It is language generally used for Microsoft cloud products for System performance, user activity, device health, compliance and security. Microsoft security tools such as Microsoft Defender suite of security tools, Microsoft Azure Active Directory, Microsoft Defender suite and many more. KQL is WORM language, write once read many. Microsoft Sentinel SIEM uses KQL for example in SOC environments.

Almost all modern SOC tools use a query language.

KQL → Microsoft Sentinel, Defender XDR

SPL → Splunk

QL → Elastic, Humio, Chronicle, Sumo Logic

Sigma rules → universal detection rules

YARA-L → detection logic on EDR tools

KQL materials.

Azure Data Explorer Free Cluster The Azure Data Explorer free cluster is a no-cost, fully managed environment designed to help users learn and explore Kusto Query Language (KQL). It provides a quick and easy way to run queries, ingest sample data, and experiment with powerful analytics features—without needing to set up or pay for infrastructure. Ideal for beginners and learners, it supports hands-on practice for scenarios like log analytics, telemetry analysis, and interactive data exploration.

https://learn.microsoft.com/en-us/azure/data-explorer/start-for-free-web-ui

📁 Resources

KQL Video Series

I watched this entire YouTube playlist by to learn KQL.

https://www.youtube.com/watch?v=4VezNFqMnpg&list=PLuIShsT8L3sCjndVr4iwT4Uyh6aquB3q2&index=1

Queries Practice (unorganized)

Ep3.
WHERE
TAKE
| = PIPE

Customers
| where FirstName == “Peter”
| where ContinentName == “Europe”
| where Education == “Graduate Degree”

Products
| take 10

cluster(‘help’).database(‘SecurityLogs’).Email
| take 10

cluster(‘help’).database(‘Samples’).PopulationData
| where State == “ARKANSAS”

Ep4. // comment
spaces dont matter
=~ Case insensitive
“” or ‘‘
!= Does not equal

Ep5. contains (case insensitive and include only portions of strings)
has (looks for full strings, but can use delimiters and is not case sensitive)
Delimiters examples: /.[{(,spaces
starts with
ends with
can be used with !
ie; !startswith_cs means doesnot startwith and case sensitive

has vs contains: if search for http, contains would show http and https, whereas if search for http, has only shows http and not https

cluster(‘help’).database(‘SecurityLogs’).InboundBrowsing
| where url contains “envolvelabs”

cluster(‘help’).database(‘SecurityLogs’).InboundBrowsing
| where user_agent contains “Firefox”

cluster(‘help’).database(‘SecurityLogs’).InboundBrowsing
| where url has “envolvelabs”

cluster(‘help’).database(‘SecurityLogs’).InboundBrowsing
| where src_ip startswith “145”

cluster(‘help’).database(‘SecurityLogs’).InboundBrowsing
| where url endswith “home”

cluster(‘help’).database(‘SecurityLogs’).OutboundBrowsing
| where url has “http”

cluster(‘help’).database(‘SecurityLogs’).InboundBrowsing
| where user_agent has “iphone”

Ep6.

cluster(‘help’).database(‘SecurityLogs’).ProcessEvents
| where process_name == ‘excel.exe’
| sort by hostname asc

cluster(‘help’).database(‘SecurityLogs’).ProcessEvents
| where process_name == ‘excel.exe’
| sort by hostname desc

cluster(‘help’).database(‘SecurityLogs’).ProcessEvents
| where process_name == ‘excel.exe’
| sort by timestamp desc

cluster(‘help’).database(‘SecurityLogs’).ProcessEvents
| where process_name == ‘excel.exe’
| sort by parent_process_name asc, process_hash asc

cluster(‘help’).database(‘SecurityLogs’).ProcessEvents
| where process_name == ‘excel.exe’
| order by parent_process_name asc, process_hash asc
| distinct parent_process_name, process_hash

cluster(‘help’).database(‘SecurityLogs’).Employees
| where role == ‘IT associate’
| sort by name asc

cluster(‘help’).database(‘SecurityLogs’).Employees
| where role == ‘IT associate’
| count

cluster(‘help’).database(‘Samples’).nyc_taxi
| count

cluster(‘help’).database(‘Samples’).nyc_taxi
| take 10

cluster(‘help’).database(‘Samples’).nyc_taxi
| where tip_amount == “0”
| count

cluster(‘help’).database(‘ContosoSales’).SalesTable
| take 10

cluster(‘help’).database(‘ContosoSales’).SalesTable
| distinct Country, State
| sort by Country asc, State asc
| count

cluster(‘help’).database(‘ContosoSales’).SalesTable
| distinct Country//, State
//| sort by Country asc, State asc
| count

cluster(‘help’).database(‘SecurityLogs’).Employees
| take 10

cluster(‘help’).database(‘SecurityLogs’).Employees
| sort by role asc, name asc
//| project name, role

Ep7.

https://portal.azure.com/#view/Microsoft_OperationsManagementSuite_Workspace/LogsDemo.ReactView

aka.ms/lademo

log analytics workspace environment

VMComputer
| take 10

project = only shows the fields we want. Use at the end of query

VMComputer
| project TimeGenerated, Hostname, OperatingSystemFamily, Cpus, CpuSpeed

VMComputer
| where OperatingSystemFamily == “windows”
| where Cpus == “4”
| project TimeGenerated, Hostname, OperatingSystemFamily, Cpus, CpuSpeed

VMComputer
| where OperatingSystemFamily == “windows”
| where Cpus >= 2
| project TimeGenerated, Hostname, OperatingSystemFamily, Cpus, CpuSpeed

Microsoft Sentinel

SecurityEvent
| take 10

project-reorder = orders the fields first, and the remaining fields are shown on the right side of the fields

SecurityEvent
| project-reorder Activity, Process, AccountType
| take 10

rename fields

SecurityEvents
| project Process, ProcessCode=Activity, AccountType
| take 10

VMConnection
| take 10

VMConnection
| where DestinationPort == “443”
| project RemoteIP, RemoteCountry

VMConnection
| where DestinationPort == “443”
| where Remotecountry == “Sweden”
//| distinct RemoteCountry
| project RemoteIP, RemoteCountry

Usage
| where Quantity >= 10
| project Quantity, Units=QuantityUnit, DataType

Ep8.
Limit
Top
Boolean (and , or)

| where FirtName == “Peter”
and Education == “High School”
and CityName == “Lieusaint”
| project FirstName, Lastname

VMConnection
| where ProcessName == “python3”
or SourceIP == “127.0.0.1”

WindowsFirewall
| where (destinationPort == ‘80’ and Protocol == ‘TCP’) or DestinationIP startswith ‘20’
project DestinationPort, Protocol, DestinationIP

‘*’ = used for probing

VMConnection
| where * has ‘389’

limit = same like take

VMConnection
| where * has ‘389’
| order by SourceIP asc
| limit 10

top 10 = first 10

take = random records

VMConnection
| where * has ‘389’
| top 10 by TimeGenerated desc

SigninLogs
| top 10 by TimeGenerated desc
//| take 10

VMConnection
| where RemoteLatitude >= 60 or ResponseTimeMax > 1000
| project RemoteLatitude, ResponseTimeMax

WindowsFirewall
| where Destinationport == ‘80’ and Protocol == ‘TCP’
| count

Ep9.

Time

SigninLogs
| where TimeGenerated >= ago(10d)
// means 10 days ago, greater than, which is newer than 10 days ago

d = Day, h = Hour m= Minute s=Second

SigninLogs
| where TimeGenerated < ago(4h)
// means 4 hours ago, less than, which is older than 4 hours ago

SigninLogs
| where TimeGenerated betweeen (datetime(2023-7-5T02:00:00) .. datetime(2023-7-6T04:00:00))
| sort by TimeGenerated desc

SigninLogs
| where TimeGenerated betweeen (datetime(2023-7-3) .. now())
| sort by TimeGenerated desc

SigninLogs
| where TimeGenerated betweeen (datetime(2023-7-3) .. (now() -4h))
| sort by TimeGenerated desc

VMProcess
| take 10

VMConnection
| take 10

VMConnection
| where TimeGenerated >= ago(7d)
and DestinationPort == ‘80’
and Computer == ‘AppArcWin’
| distinct ProcessName

Homework:

Perf
//|take 10
| where TimeGenerated >= ago(3d)
and CounterName contains “Bytes sent”
and CounterValue > 10000
| sort by CounterValue desc
| project TimeGenerated, CounterName, CounterValue

Ep 10.

getschema , extend, search

search in (ContainerLog,)

SigninLogs
| search “Azure Portal”

SigninLogs
| where * has “Azure Portal”

SigninLogs
| search “*Azure Portal”

SigninLogs | search “Azure Portal”

SigninLogs
| search Location: “FR” or AppDisplayName: “Azure”

search “40.78.229.33”
//can see new field, $table

search “40.78.229.33”
| distinct $table

search “40.78.229.33”
| where DestPublicIPs_s contains “40.78.229.33”
| project TimeGenerated, DestPublicIPs_s
//| distinct $table

search “YouTube”
| where TimeGenerated > datetime(2023-07-15)
| sort by TimeGenerated desc

search in (ContainerLog, AVSSyslog) “google”

Usage
| take 10

extend = add a new field

Usage
| extend GB=Quantity/1000
| sort by GB desc

getschema = see DataType

ContainerInventory
| getschema

Homework:

search “185.125.190.23”
| where TimeGenerated >= ago(3d)
| distinct $table

Ep11.

Common errors

Update
| where Product =~ ‘Grub-pc’ or Product == ‘liblxc1’

same as

Update
| where Product in~(‘Grub-pc’, ‘liblxc1’)

homework

Error:

ContainerLog
Where Timegenerated ago(7d)
| where LogEntry in (Done, received all)
| project TimeGenerated, type, LogEntry,

Corrected:

ContainerLog
| where TimeGenerated >= ago(7d)
and LogEntry in (‘Done’, ‘received call’)
| project TimeGenerated, Type, LogEntry

Ep 12.

Debugging practices.

Ep 13.

Quizzes

Ep 14. (last episode)

Quizzes

Conclusion

• Overall learning KQL is similar to SQL. It is easy and straightforward to learn.