Aqua Blizzard AV hits - Feb 2022

Original query, unchanged.

let iocs = externaldata(DateAdded:string,IoC:string,Type:string) [@"https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Sample%20Data/Feeds/ActiniumIOC.csv"] with (format="csv", ignoreFirstRecord=True);
let AVHits = (iocs | where Type =~ "AVDetection"| project IoC);
SecurityAlert
| where ProviderName == 'MDATP'
| extend ThreatName_ = tostring(parse_json(ExtendedProperties).ThreatName)
| where ThreatName_ has_any (AVHits)
| extend Directory = tostring(parse_json(Entities)[0].Directory), SHA256 = tostring(parse_json(tostring(parse_json(Entities)[0].FileHashes))[2].Value), FileName = tostring(parse_json(Entities)[0].Name), Hostname = tostring(parse_json(Entities)[6].FQDN)
| extend AccountName = tostring(parse_json(tostring(parse_json(Entities)[6].LoggedOnUsers))[0].AccountName)
| project TimeGenerated, AlertName, ThreatName_, ProviderName, AlertSeverity, Description, RemediationSteps, ExtendedProperties, Entities, FileName,SHA256, Directory, Hostname, AccountName
| extend FileHash = SHA256, FileHashType = "SHA256"
| extend Name = tostring(split(AccountName, "@")[0]), AccountUPNSuffix = tostring(split(AccountName, "@")[1])
| extend Host = tostring(split(Hostname, ".")[0]), DomainIndex = toint(indexof(Hostname, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Hostname, DomainIndex + 1), Hostname)