File Malware Detections by Location (SharePoint, OneDrive and Teams)

id: 3a9fa02a-4510-47a2-b76f-3be67c9ae06f
name: File Malware Detections by Location (SharePoint, OneDrive and Teams)
description: |
  This query surfaces malware detections in files stored in SharePoint, OneDrive and Teams, grouped by the site or personal location where each file resides, using the FileMaliciousContentInfo table.
description-detailed: |
  Microsoft Defender for Office 365 and the built-in SharePoint Online antivirus scan files uploaded to SharePoint, OneDrive and Teams. This query groups the resulting malware detections by the SharePoint site or OneDrive personal location (parsed from FolderPath), so the most affected locations can be prioritised for 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: |
  // FileMaliciousContentInfo records malicious-content findings for files in SharePoint, OneDrive and Teams.
  FileMaliciousContentInfo
  | where Timestamp > ago(30d)
  | where isnotempty(ThreatTypes)
  // FolderPath segment [4] is the SharePoint site or the OneDrive personal location where the file resides.
  | extend Location = tostring(split(FolderPath, '/')[4])
  | summarize FileMalwareDetections = count(), DistinctFiles = dcount(SHA256) by Location
  | top 100 by FileMalwareDetections desc
version: 1.0.0