Pivot from detections to related downloads.txt

// Pivot from downloads detected by Windows Defender Antivirus to other files downloaded from the same sites
// To learn more about the download URL info that is available and see other sample queries,
// check out this blog post: https://techcommunity.microsoft.com/t5/Threat-Intelligence/Hunting-tip-of-the-month-Browser-downloads/td-p/220454
let detectedDownloads =
    DeviceEvents
    | where ActionType == "AntivirusDetection" and isnotempty(FileOriginUrl)
    | project Timestamp, FileOriginUrl, FileName, DeviceId,
              ThreatName=tostring(parse_json(AdditionalFields).ThreatName)
    // Filter out less severe threat categories on which we do not want to pivot
    | where ThreatName !startswith "PUA"
            and ThreatName !startswith "SoftwareBundler:" 
            and FileOriginUrl != "about:internet";
let detectedDownloadsSummary =
    detectedDownloads
    // Get a few examples for each detected Host:
    // up to 4 filenames, up to 4 threat names, one full URL)
    | summarize DetectedUrl=any(FileOriginUrl),
                DetectedFiles=makeset(FileName, 4),
                ThreatNames=makeset(ThreatName, 4)
                by Host=tostring(parse_url(FileOriginUrl).Host);
// Query for downloads from sites from which other downloads were detected by Windows Defender Antivirus
DeviceFileEvents
| where isnotempty(FileOriginUrl)
| project FileName, FileOriginUrl, DeviceId, Timestamp,
          Host=tostring(parse_url(FileOriginUrl).Host), SHA1 
// Filter downloads from hosts serving detected files
| join kind=inner(detectedDownloadsSummary) on Host
// Filter out download file create events that were also detected.
// This is needed because sometimes both of these events will be reported, 
// and sometimes only the AntivirusDetection event - depending on timing.
| join kind=leftanti(detectedDownloads) on DeviceId, FileOriginUrl
// Summarize a single row per host - with the machines count 
// and an example event for a missed download (select the last event)
| summarize MachineCount=dcount(DeviceId), arg_max(Timestamp, *) by Host
// Filter out common hosts, as they probably ones that also serve benign files
| where MachineCount < 20
| project Host, MachineCount, DeviceId, FileName, DetectedFiles, 
          FileOriginUrl, DetectedUrl, ThreatNames, Timestamp, SHA1
| order by MachineCount desc