Categorie: Kusto Query KQL

Regex match on IP value

let IP = "11.1.1.1";
print IP
| extend IsIP = iif((IP matches regex @"\b(?:(?:2(?:[0-4][0-9]|5[0-5])|[0-1]?[0-9]?[0-9]).){3}(?:(?:2([0-4][0-9]|5[0-5])|[0-1]?[0-9]?[0-9]))\b"),"True", "False")
| project IP, IsIP

IP Watchlist check example

//Check for allowed IP usages
//Set lookup time
let dt_lookBack = 7d;
// Get Watchlist data
_GetWatchlist('Allowed_LAN_IPs')
//Search for IP's that do not exist within the watchlist
| join kind = rightanti (imNetworkSession
| where EventProduct == 'TP Link'
| where TimeGenerated >= ago(dt_lookBack)
| where isnotempty(SrcIpAddr)
// renaming time column so it is clear the log this came from
| extend DevLog_TimeGenerated = TimeGenerated)
on $left.IP == $right.SrcIpAddr
//project the requiered fields within the right table
| extend DvcHostname = iff(isnotempty(Dvc_dynamic), Dvc_dynamic, Dvc_string)
| project TimeGenerated, SrcIpAddr, DvcHostname, DstIpAddr, SyslogMessage

TI IP match based on imNetworkSession ASIM parser

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
ThreatIntelligenceIndicator
| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
| where Active == true
// Picking up only IOC's that contain the entities we want
| where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
// As there is potentially more than 1 indicator type for matching IP, taking NetworkIP first, then others if that is empty.
// Taking the first non-empty value based on potential IOC match availability
| extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)
| join (imNetworkSession
| where TimeGenerated >= ago(dt_lookBack)
| where isnotempty(DstIpAddr)
// renaming time column so it is clear the log this came from
| extend DevLog_TimeGenerated = TimeGenerated)
on $left.TI_ipEntity == $right.DstIpAddr
//| where DevLog_TimeGenerated >= TimeGenerated and DevLog_TimeGenerated < ExpirationDateTime
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| extend DvcHostname = iff(isnotempty(Dvc_dynamic), Dvc_dynamic, Dvc_string)
| project LatestIndicatorTime, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,
DevLog_TimeGenerated, TI_ipEntity, DvcHostname

Count number of email recipients from same sender within the last 3 hours

let timeframe = ago(3h);

let threshold = 2;

EmailEvents

| where Timestamp > timeframe

| where DeliveryAction == "Delivered"

| where isempty(SenderObjectId)

| summarize StartTime = min(Timestamp), EndTime = max(Timestamp), NumOfRecipients = dcount(RecipientEmailAddress)

  by SenderFromAddress

Simple custom parser TPLink

let TPlinkfilter=
Syslog
| where ProcessName == 'TL-ER604W(UN)'
| where SyslogMessage has ('Detected stationary source icmp flood attack')
;
let TPLinkParse=
union TPlinkfilter
| extend EventEndTime = extract("(.?)<",1,SyslogMessage), DstIpAddr = Computer, SrcIpAddr = extract(" attack source: (.?) .",1,SyslogMessage)
;
let TPLinkNetwork=
union TPLinkParse
| extend
EventType = 'NetworkSession',
EventStartTime = EventEndTime,
EventCount = int(1),
EventVendor = 'Microsoft',
EventSchemaVersion = '0.2.0',
EventSchema = 'NetworkSession',
EventProduct = 'TP Link',
EventResult = 'Success',
EventSeverity = 'Informational',
DvcOs = 'Linux',
//Protocol = toupper(Protocol),
EventOriginalType = '3' // Set with a constant value to avoid parsing
| project-rename
DvcIpAddr = Computer,
DvcHostname = ProcessName
| extend // aliases
Dvc = DvcHostname,
IpAddr = SrcIpAddr,
Src = SrcIpAddr,
Dst = DstIpAddr
;
TPLinkNetwork

Simple syslog parser 2

Syslog
| where ProcessName == 'TL-ER604W(UN)'
| extend ip_lease = extract("address (.?) for",1,SyslogMessage) | extend wan_ip = extract("IP addr:(.?),",1,SyslogMessage)
| extend wan_mask = extract("mask:(.?),",1,SyslogMessage) | extend wan_num = extract(" : (.?):DHCP",1,SyslogMessage)

Query ADX example

adx("adxdemosentinel.westeurope/SentinelDB").DiagnosticLogs | take 100

IFF example

| extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, TI_ipEntity)