KQL Challenge Solutions

Challenge #1; Create a query that uses a watchlist

let attack=_GetWatchlist(‘attack’);
SecurityAlert
|extend Severity = “T1040”
|join attack on $left.Severity == $right.Tactic
|distinct Defense

Challenge #2: Create a timechart based query

Event
| where Source == “Microsoft-Windows-Sysmon”
|where EventID <> 255
| where TimeGenerated >= ago(180d)
| summarize count() by bin(TimeGenerated, 1d)
|render columnchart

Chalenge #3: use mv-expand to view the entities fields

SecurityAlert
| extend Entities = iff(isempty(Entities), todynamic(‘[{“dummy” : “”}]’), todynamic(Entities))
|mv-expand Entities
|extend HostName_ = tostring(Entities.HostName)
|where HostName_ <> “”
|where HostName_ contains “{Hostname}”
|project HostName_

Bonus challenge: use GeoIP to map to country

“let IP_Data = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
[‘https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv’%5D;
let IPs =
CommonSecurityLog
|where DeviceVendor == “”Fortinet””
//filter out private networks
|where not(ipv4_is_private(SourceIP)) and not(ipv4_is_private(DestinationIP))
|summarize by SourceIP
;
IPs
| evaluate ipv4_lookup(IP_Data, SourceIP, network, return_unmatched = true)”