Mass Download copy to USB device by single user

let Alerts = SecurityAlert
| where AlertName =~ "mass download by a single user"
| where Status != 'Resolved'
| extend ipEnt = parse_json(Entities), accountEnt = parse_json(Entities)
| mv-apply tempParams = ipEnt on (
mv-expand ipEnt
| where ipEnt.Type == "ip" 
| extend IpAddress = tostring(ipEnt.Address)
)
| mv-apply tempParams = accountEnt on (
mv-expand accountEnt
| where accountEnt.Type == "account"
| extend AADUserId = tostring(accountEnt.AadUserId)
)
| extend Alert_TimeGenerated = TimeGenerated
| distinct Alert_TimeGenerated, IpAddress, AADUserId, DisplayName, Description, ProductName, ExtendedProperties, Entities, Status, CompromisedEntity
;
let CA_Events = CloudAppEvents
| where ActionType == "FileDownloaded"
| extend parsed = parse_json(RawEventData)
| extend UserId = tostring(parsed.UserId)
| extend FileName = tostring(parsed.SourceFileName)
| extend FileExtension = tostring(parsed.SourceFileExtension)
| summarize CloudAppEvent_StartTime = min(TimeGenerated), CloudAppEvent_EndTime = max(TimeGenerated), CloudAppEvent_Files = make_set(FileName), FileCount = dcount(FileName) by Application, AccountObjectId, UserId, IPAddress, City, CountryCode
| extend CloudAppEvents_Details = pack_all();
let CA_Alerts_Events = Alerts | join kind=inner (CA_Events)
on $left.AADUserId == $right.AccountObjectId and $left.IpAddress == $right.IPAddress
// Cloud app event comes before Alert
| where CloudAppEvent_EndTime <= Alert_TimeGenerated
| project Alert_TimeGenerated, UserId, AADUserId, IPAddress, CloudAppEvents_Details, CloudAppEvent_Files
;
// setup list to filter DeviceFileEvents for only files downloaded as indicated by CloudAppEvents
let CA_FileList = CA_Alerts_Events | project CloudAppEvent_Files;
CA_Alerts_Events
| join kind=inner ( DeviceFileEvents
| where ActionType in ("FileCreated", "FileRenamed")
| where FileName in~ (CA_FileList)
| summarize DeviceFileEvent_StartTime = min(TimeGenerated), DeviceFileEvent_EndTime = max(TimeGenerated), DeviceFileEvent_Files = make_set(FolderPath), DeviceFileEvent_FileCount = dcount(FolderPath) by InitiatingProcessAccountUpn, DeviceId, DeviceName, InitiatingProcessFolderPath, InitiatingProcessParentFileName//, InitiatingProcessCommandLine
| extend DeviceFileEvents_Details = pack_all()
) on $left.UserId == $right.InitiatingProcessAccountUpn
| where DeviceFileEvent_StartTime >= Alert_TimeGenerated
| join kind=inner (
// get device events where a USB drive was mounted
DeviceEvents
| where ActionType == "UsbDriveMounted"
| extend parsed = parse_json(AdditionalFields)
| extend USB_DriveLetter = tostring(AdditionalFields.DriveLetter), USB_ProductName = tostring(AdditionalFields.ProductName), USB_Volume = tostring(AdditionalFields.Volume)
| where isnotempty(USB_DriveLetter)
| project USB_TimeGenerated = TimeGenerated, DeviceId, USB_DriveLetter, USB_ProductName, USB_Volume
| extend USB_Details = pack_all()
)  
on DeviceId
// USB event occurs after the Alert
| where USB_TimeGenerated >= Alert_TimeGenerated
| mv-expand DeviceFileEvent_Files
| extend DeviceFileEvent_Files = tostring(DeviceFileEvent_Files)
// make sure that we only pickup the files that have the USB drive letter
| where DeviceFileEvent_Files startswith USB_DriveLetter
| summarize USB_Drive_MatchedFiles = make_set_if(DeviceFileEvent_Files, DeviceFileEvent_Files startswith USB_DriveLetter) by Alert_TimeGenerated, USB_TimeGenerated, UserId, AADUserId, DeviceId, DeviceName, IPAddress, CloudAppEvents_Details = tostring(CloudAppEvents_Details), DeviceFileEvents_Details = tostring(DeviceFileEvents_Details), USB_Details = tostring(USB_Details)
| extend InitiatingProcessFileName = tostring(split(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath, "\\")[-1]), InitiatingProcessFolderPath = tostring(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath)
| extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
| extend HostNameDomain = iff(DeviceName != -1, substring(DeviceName, DomainIndex + 1), DeviceName)
| extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
| project-away DomainIndex

relevantTechniques:
- T1052
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SecurityAlert/Massdownload_USBFileCopy.yaml
metadata:
  categories:
    domains:
    - Security - Others
  source:
    kind: Community
  author:
    name: Microsoft Security Research
  support:
    tier: Community
triggerOperator: gt
kind: Scheduled
entityMappings:
- fieldMappings:
  - columnName: UserId
    identifier: FullName
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: AADUserId
    identifier: AadUserId
  entityType: Account
- fieldMappings:
  - columnName: IPAddress
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: DeviceName
    identifier: FullName
  - columnName: HostName
    identifier: HostName
  - columnName: HostNameDomain
    identifier: DnsDomain
  entityType: Host
- fieldMappings:
  - columnName: InitiatingProcessFileName
    identifier: Name
  - columnName: InitiatingProcessFolderPath
    identifier: Directory
  entityType: File
requiredDataConnectors:
- dataTypes:
  - SecurityAlert
  connectorId: MicrosoftCloudAppSecurity
- dataTypes:
  - CloudAppEvents
  - DeviceEvents
  - DeviceFileEvents
  connectorId: MicrosoftThreatProtection
queryPeriod: 1d
name: Mass Download & copy to USB device by single user
query: |
  let Alerts = SecurityAlert
  | where AlertName =~ "mass download by a single user"
  | where Status != 'Resolved'
  | extend ipEnt = parse_json(Entities), accountEnt = parse_json(Entities)
  | mv-apply tempParams = ipEnt on (
  mv-expand ipEnt
  | where ipEnt.Type == "ip" 
  | extend IpAddress = tostring(ipEnt.Address)
  )
  | mv-apply tempParams = accountEnt on (
  mv-expand accountEnt
  | where accountEnt.Type == "account"
  | extend AADUserId = tostring(accountEnt.AadUserId)
  )
  | extend Alert_TimeGenerated = TimeGenerated
  | distinct Alert_TimeGenerated, IpAddress, AADUserId, DisplayName, Description, ProductName, ExtendedProperties, Entities, Status, CompromisedEntity
  ;
  let CA_Events = CloudAppEvents
  | where ActionType == "FileDownloaded"
  | extend parsed = parse_json(RawEventData)
  | extend UserId = tostring(parsed.UserId)
  | extend FileName = tostring(parsed.SourceFileName)
  | extend FileExtension = tostring(parsed.SourceFileExtension)
  | summarize CloudAppEvent_StartTime = min(TimeGenerated), CloudAppEvent_EndTime = max(TimeGenerated), CloudAppEvent_Files = make_set(FileName), FileCount = dcount(FileName) by Application, AccountObjectId, UserId, IPAddress, City, CountryCode
  | extend CloudAppEvents_Details = pack_all();
  let CA_Alerts_Events = Alerts | join kind=inner (CA_Events)
  on $left.AADUserId == $right.AccountObjectId and $left.IpAddress == $right.IPAddress
  // Cloud app event comes before Alert
  | where CloudAppEvent_EndTime <= Alert_TimeGenerated
  | project Alert_TimeGenerated, UserId, AADUserId, IPAddress, CloudAppEvents_Details, CloudAppEvent_Files
  ;
  // setup list to filter DeviceFileEvents for only files downloaded as indicated by CloudAppEvents
  let CA_FileList = CA_Alerts_Events | project CloudAppEvent_Files;
  CA_Alerts_Events
  | join kind=inner ( DeviceFileEvents
  | where ActionType in ("FileCreated", "FileRenamed")
  | where FileName in~ (CA_FileList)
  | summarize DeviceFileEvent_StartTime = min(TimeGenerated), DeviceFileEvent_EndTime = max(TimeGenerated), DeviceFileEvent_Files = make_set(FolderPath), DeviceFileEvent_FileCount = dcount(FolderPath) by InitiatingProcessAccountUpn, DeviceId, DeviceName, InitiatingProcessFolderPath, InitiatingProcessParentFileName//, InitiatingProcessCommandLine
  | extend DeviceFileEvents_Details = pack_all()
  ) on $left.UserId == $right.InitiatingProcessAccountUpn
  | where DeviceFileEvent_StartTime >= Alert_TimeGenerated
  | join kind=inner (
  // get device events where a USB drive was mounted
  DeviceEvents
  | where ActionType == "UsbDriveMounted"
  | extend parsed = parse_json(AdditionalFields)
  | extend USB_DriveLetter = tostring(AdditionalFields.DriveLetter), USB_ProductName = tostring(AdditionalFields.ProductName), USB_Volume = tostring(AdditionalFields.Volume)
  | where isnotempty(USB_DriveLetter)
  | project USB_TimeGenerated = TimeGenerated, DeviceId, USB_DriveLetter, USB_ProductName, USB_Volume
  | extend USB_Details = pack_all()
  )  
  on DeviceId
  // USB event occurs after the Alert
  | where USB_TimeGenerated >= Alert_TimeGenerated
  | mv-expand DeviceFileEvent_Files
  | extend DeviceFileEvent_Files = tostring(DeviceFileEvent_Files)
  // make sure that we only pickup the files that have the USB drive letter
  | where DeviceFileEvent_Files startswith USB_DriveLetter
  | summarize USB_Drive_MatchedFiles = make_set_if(DeviceFileEvent_Files, DeviceFileEvent_Files startswith USB_DriveLetter) by Alert_TimeGenerated, USB_TimeGenerated, UserId, AADUserId, DeviceId, DeviceName, IPAddress, CloudAppEvents_Details = tostring(CloudAppEvents_Details), DeviceFileEvents_Details = tostring(DeviceFileEvents_Details), USB_Details = tostring(USB_Details)
  | extend InitiatingProcessFileName = tostring(split(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath, "\\")[-1]), InitiatingProcessFolderPath = tostring(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath)
  | extend HostName = tostring(split(DeviceName, ".")[0]), DomainIndex = toint(indexof(DeviceName, '.'))
  | extend HostNameDomain = iff(DeviceName != -1, substring(DeviceName, DomainIndex + 1), DeviceName)
  | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
  | project-away DomainIndex  
tactics:
- Exfiltration
severity: Medium
triggerThreshold: 0
description: |
  'This query looks for any mass download by a single user with possible file copy activity to a new USB drive. Malicious insiders may perform such activities that may cause harm to the organization. 
  This query could also reveal unintentional insider that had no intention of malicious activity but their actions may impact an organizations security posture.
  Reference:https://docs.microsoft.com/defender-cloud-apps/policy-template-reference'  
queryFrequency: 1d
version: 1.0.4
id: 6267ce44-1e9d-471b-9f1e-ae76a6b7aa84

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2023-02-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/6267ce44-1e9d-471b-9f1e-ae76a6b7aa84')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/6267ce44-1e9d-471b-9f1e-ae76a6b7aa84')]",
      "properties": {
        "alertRuleTemplateName": "6267ce44-1e9d-471b-9f1e-ae76a6b7aa84",
        "customDetails": null,
        "description": "'This query looks for any mass download by a single user with possible file copy activity to a new USB drive. Malicious insiders may perform such activities that may cause harm to the organization. \nThis query could also reveal unintentional insider that had no intention of malicious activity but their actions may impact an organizations security posture.\nReference:https://docs.microsoft.com/defender-cloud-apps/policy-template-reference'\n",
        "displayName": "Mass Download & copy to USB device by single user",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserId",
                "identifier": "FullName"
              },
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AADUserId",
                "identifier": "AadUserId"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "IPAddress",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "Host",
            "fieldMappings": [
              {
                "columnName": "DeviceName",
                "identifier": "FullName"
              },
              {
                "columnName": "HostName",
                "identifier": "HostName"
              },
              {
                "columnName": "HostNameDomain",
                "identifier": "DnsDomain"
              }
            ]
          },
          {
            "entityType": "File",
            "fieldMappings": [
              {
                "columnName": "InitiatingProcessFileName",
                "identifier": "Name"
              },
              {
                "columnName": "InitiatingProcessFolderPath",
                "identifier": "Directory"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SecurityAlert/Massdownload_USBFileCopy.yaml",
        "query": "let Alerts = SecurityAlert\n| where AlertName =~ \"mass download by a single user\"\n| where Status != 'Resolved'\n| extend ipEnt = parse_json(Entities), accountEnt = parse_json(Entities)\n| mv-apply tempParams = ipEnt on (\nmv-expand ipEnt\n| where ipEnt.Type == \"ip\" \n| extend IpAddress = tostring(ipEnt.Address)\n)\n| mv-apply tempParams = accountEnt on (\nmv-expand accountEnt\n| where accountEnt.Type == \"account\"\n| extend AADUserId = tostring(accountEnt.AadUserId)\n)\n| extend Alert_TimeGenerated = TimeGenerated\n| distinct Alert_TimeGenerated, IpAddress, AADUserId, DisplayName, Description, ProductName, ExtendedProperties, Entities, Status, CompromisedEntity\n;\nlet CA_Events = CloudAppEvents\n| where ActionType == \"FileDownloaded\"\n| extend parsed = parse_json(RawEventData)\n| extend UserId = tostring(parsed.UserId)\n| extend FileName = tostring(parsed.SourceFileName)\n| extend FileExtension = tostring(parsed.SourceFileExtension)\n| summarize CloudAppEvent_StartTime = min(TimeGenerated), CloudAppEvent_EndTime = max(TimeGenerated), CloudAppEvent_Files = make_set(FileName), FileCount = dcount(FileName) by Application, AccountObjectId, UserId, IPAddress, City, CountryCode\n| extend CloudAppEvents_Details = pack_all();\nlet CA_Alerts_Events = Alerts | join kind=inner (CA_Events)\non $left.AADUserId == $right.AccountObjectId and $left.IpAddress == $right.IPAddress\n// Cloud app event comes before Alert\n| where CloudAppEvent_EndTime <= Alert_TimeGenerated\n| project Alert_TimeGenerated, UserId, AADUserId, IPAddress, CloudAppEvents_Details, CloudAppEvent_Files\n;\n// setup list to filter DeviceFileEvents for only files downloaded as indicated by CloudAppEvents\nlet CA_FileList = CA_Alerts_Events | project CloudAppEvent_Files;\nCA_Alerts_Events\n| join kind=inner ( DeviceFileEvents\n| where ActionType in (\"FileCreated\", \"FileRenamed\")\n| where FileName in~ (CA_FileList)\n| summarize DeviceFileEvent_StartTime = min(TimeGenerated), DeviceFileEvent_EndTime = max(TimeGenerated), DeviceFileEvent_Files = make_set(FolderPath), DeviceFileEvent_FileCount = dcount(FolderPath) by InitiatingProcessAccountUpn, DeviceId, DeviceName, InitiatingProcessFolderPath, InitiatingProcessParentFileName//, InitiatingProcessCommandLine\n| extend DeviceFileEvents_Details = pack_all()\n) on $left.UserId == $right.InitiatingProcessAccountUpn\n| where DeviceFileEvent_StartTime >= Alert_TimeGenerated\n| join kind=inner (\n// get device events where a USB drive was mounted\nDeviceEvents\n| where ActionType == \"UsbDriveMounted\"\n| extend parsed = parse_json(AdditionalFields)\n| extend USB_DriveLetter = tostring(AdditionalFields.DriveLetter), USB_ProductName = tostring(AdditionalFields.ProductName), USB_Volume = tostring(AdditionalFields.Volume)\n| where isnotempty(USB_DriveLetter)\n| project USB_TimeGenerated = TimeGenerated, DeviceId, USB_DriveLetter, USB_ProductName, USB_Volume\n| extend USB_Details = pack_all()\n)  \non DeviceId\n// USB event occurs after the Alert\n| where USB_TimeGenerated >= Alert_TimeGenerated\n| mv-expand DeviceFileEvent_Files\n| extend DeviceFileEvent_Files = tostring(DeviceFileEvent_Files)\n// make sure that we only pickup the files that have the USB drive letter\n| where DeviceFileEvent_Files startswith USB_DriveLetter\n| summarize USB_Drive_MatchedFiles = make_set_if(DeviceFileEvent_Files, DeviceFileEvent_Files startswith USB_DriveLetter) by Alert_TimeGenerated, USB_TimeGenerated, UserId, AADUserId, DeviceId, DeviceName, IPAddress, CloudAppEvents_Details = tostring(CloudAppEvents_Details), DeviceFileEvents_Details = tostring(DeviceFileEvents_Details), USB_Details = tostring(USB_Details)\n| extend InitiatingProcessFileName = tostring(split(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath, \"\\\\\")[-1]), InitiatingProcessFolderPath = tostring(todynamic(DeviceFileEvents_Details).InitiatingProcessFolderPath)\n| extend HostName = tostring(split(DeviceName, \".\")[0]), DomainIndex = toint(indexof(DeviceName, '.'))\n| extend HostNameDomain = iff(DeviceName != -1, substring(DeviceName, DomainIndex + 1), DeviceName)\n| extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n| project-away DomainIndex\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P1D",
        "severity": "Medium",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Exfiltration"
        ],
        "techniques": [
          "T1052"
        ],
        "templateVersion": "1.0.4",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}