Improvement: TI Feed - ThreatviewioIP-High-Confidence-Feed.md

[benscha]

Hi Bert-Jan

i love your KQLs ♥ With the latest i tested (TI Feed - ThreatviewioIP-High-Confidence-Feed.md ) i had some Issues. (Error message: Relop semantic error: SEM0023: The total size of the arguments exceeded the allowed limit of 0.95 MB ...)

After tilting the head a little more to the left, I was able to carry out the following optimisation.

instead of the ‘has_any’ function I join the table and can execute the query without errors. I also have to limit the timestamp, otherwise I trigger a CPU usage error.

let ThreatIntelFeed = externaldata(Domain: string)[@"https://threatview.io/Downloads/DOMAIN-High-Confidence-Feed.txt"] with (format="txt", ignoreFirstRecord=True) | project Domain = tolower(Domain); let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}'; DeviceNetworkEvents | where Timestamp > ago(4h) | join kind=inner ThreatIntelFeed on $left.RemoteUrl == $right.Domain | extend GeoIPInfo = geo_info_from_ip_address(RemoteIP) | extend country = tostring(parse_json(GeoIPInfo).country), state = tostring(parse_json(GeoIPInfo).state), city = tostring(parse_json(GeoIPInfo).city), latitude = tostring(parse_json(GeoIPInfo).latitude), longitude = tostring(parse_json(GeoIPInfo).longitude) | project-reorder Timestamp, DeviceName, RemoteIP, RemotePort, InitiatingProcessAccountName

as an Addition i also check in EmailUrlInfo if there are any Emails with Hits and move them to the Junk Folder 🤘

let ThreatIntelFeed = externaldata(Domain: string)[@"https://threatview.io/Downloads/DOMAIN-High-Confidence-Feed.txt"] with (format="txt", ignoreFirstRecord=True) | project Domain = tolower(Domain); EmailUrlInfo //| where Timestamp > ago(1d) | join kind=inner ThreatIntelFeed on $left.Url == $right.Domain | join EmailEvents on NetworkMessageId

thanks for your great work!

best regards from switzerland ✌️
Benjamin

[Bert-JanP]

Thank you for mentioning this Benjamin! I will have a look at it later this week (hopefully ;)) and sort it. Good addition to also check the Email Information!

Bert-Jan