Top File Owners Holding Malware (SharePoint, OneDrive and Teams)

id: fe2faae3-9864-44b0-a42d-8e829baa34a9
name: Top File Owners Holding Malware (SharePoint, OneDrive and Teams)
description: |
  This query lists the file owners (or the SharePoint/Teams site, when a file has no individual owner) whose files in SharePoint, OneDrive or Teams were flagged as malware, using the FileMaliciousContentInfo table.
description-detailed: |
  Microsoft Defender for Office 365 and the built-in SharePoint Online antivirus scan files across SharePoint, OneDrive and Teams. This query ranks the owners (or sites) holding the most malicious files, with the count of malicious files, distinct files by hash, affected workloads and sample threat names, to pinpoint the accounts and locations most affected for targeted remediation.
  This query is part of the Microsoft Defender for Office 365 Detections and Insights workbook in Microsoft Sentinel: https://techcommunity.microsoft.com/blog/microsoftdefenderforoffice365blog/part-3-build-custom-email-security-reports-with-power-bi-and-workbooks-in-micros/4490127
requiredDataConnectors:
- connectorId: MicrosoftThreatProtection
  dataTypes:
  - FileMaliciousContentInfo
tactics:
  - InitialAccess
  - LateralMovement
relevantTechniques:
  - T1566
  - T1080
query: |
  // Groups by the file owner, or by the SharePoint/Teams site when a file has no individual owner.
  FileMaliciousContentInfo
  | where Timestamp > ago(30d)
  | where isnotempty(ThreatTypes)
  | extend Owner = iff(isnotempty(FileOwnerUpn), FileOwnerUpn, strcat('Site: ', tostring(split(FolderPath, '/')[4])))
  | summarize MaliciousFiles = count(),
              DistinctFiles = dcount(SHA256),
              Workloads = make_set(Workload, 3),
              SampleThreats = make_set_if(ThreatNames, isnotempty(ThreatNames), 5),
              LastSeen = max(Timestamp)
      by Owner
  | top 20 by MaliciousFiles
version: 1.0.0