Pivot from detections to related downloads

Description

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.

Query · kql

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
Raw source Pivot from detections to related downloads · KQL
Esc
Published by Azure/Azure-Sentinel ↗, licensed under MIT ↗. Reproduced here unmodified. 
id: 351f7035-836c-4f4b-80bb-188220ba9215
name: Pivot from detections to related downloads
description: |
  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.
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - DeviceEvents
  - DeviceFileEvents
query: |
  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

Detection rules belong to the projects that publish them and remain under their own licenses. This site indexes and links to them; it claims no rights in them.