KQL Fundamentals – Extend
Level: Beginner | Reading time: 5 minutes
Let’s continue our series on KQL with a focus on Cyber Security. Let’s talk today about how to use the Extend command. Other posts can be seen in our KQL category.
You can use the extent command to create new calculated columns and append them to the result set, this adds a new column to the input result set that will appear as the last column on the right side of the search.
Syntax:
T| extend [ColumnName | (ColumnName[, …])=] Expression [, …]
It is also possible to use extend on multiple columns at the same time.
SecurityAlert
| where TimeGenerated > ago(7d)
| extend severityOrder = case (
AlertSeverity == "High", 3,
AlertSeverity == "Medium", 2,
AlertSeverity == "Low", 1,
AlertSeverity == "Informational", 0,
-1)
Let’s see how this would apply to a Microsoft Sentinel detection rule. Here, we use extend to create two new columns HTTP_Status_Code and Domain:
let timeframe = 1d;
let DomainList = dynamic(["tor2web.org", "tor2web.com"]);
Syslog
| where TimeGenerated >= ago(timeframe)
| where ProcessName contains "squid"
| extend
HTTP_Status_Code = extract("(TCP_(([A-Z]+)…-9]{3}))",8,SyslogMessage),
Domain = extract("(([A-Z]+ [a-z]{4…Z]+ )([^ :\/]*))",3,SyslogMessage)
| where HTTP_Status_Code == "200"
| where Domain contains "."
| where Domain has_any (DomainList)
References:
