KQL Search

// All TLDs and number of connections from our devices
// Block entire TLDs with Windows Firewall settings in endpoint.microsoft.com
let AllTLDs = (externaldata(response: string) [@"https://data.iana.org/TLD/tlds-alpha-by-domain.txt"] with (format="txt"))
| where response !startswith "#"
| project response
| extend data = parse_csv(response)
| extend TLD = tolower(tostring(data[0]))
| project-away response, ['data'];
let ContactedTLDs = DeviceNetworkEvents
| where RemoteIP !startswith "127."
| extend FQDN = trim_end("(:|\\?).*", tostring(split(trim_start('http(.|)://', RemoteUrl), "/")[0]))
| where FQDN contains "."  // exclude singular hostnames used in local name resolution
| extend TLD = tostring(split(FQDN, ".")[-1])
| where gettype(parse_json(TLD)) != "long"  // TLD is a number because URL was an IP address
| summarize Connections = count(), UniqueSites = count_distinct(FQDN) by TLD
| sort by Connections asc, UniqueSites asc;
union ContactedTLDs, AllTLDs

// Investigate TLD connections
let TLDs = dynamic(["tcs", "box", "jetpack", "auction", "olk", "valinhos", "storage", "ec", "date"]);
DeviceNetworkEvents
| extend FQDN = trim_end("(:|\\?).*", tostring(split(trim_start('http(.|)://', RemoteUrl), "/")[0]))
| where FQDN contains "."  // local name resolution
| extend TLD = tostring(split(FQDN, ".")[-1])
| where gettype(parse_json(TLD)) != "long"  // TLD is a number because URL was an IP address
| where TLD in~ (TLDs)
| join IdentityInfo on $left.InitiatingProcessAccountSid == $right.OnPremSid
| project Timestamp, DeviceName, ActionType, FQDN, InitiatingProcessFileName, InitiatingProcessCommandLine, Username=InitiatingProcessAccountName, FullName=strcat(GivenName," ",Surname), JobTitle, EmailAddress
| sort by Timestamp