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

severity: Medium
relevantTechniques:
- T1052
queryFrequency: 1d
kind: Scheduled
version: 1.0.4
metadata:
  support:
    tier: Community
  categories:
    domains:
    - Security - Others
  author:
    name: Microsoft Security Research
  source:
    kind: Community
triggerOperator: gt
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'  
queryPeriod: 1d
id: 6267ce44-1e9d-471b-9f1e-ae76a6b7aa84
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
tactics:
- Exfiltration
name: Mass Download & copy to USB device by single user
triggerThreshold: 0
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SecurityAlert/Massdownload_USBFileCopy.yaml
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  
requiredDataConnectors:
- dataTypes:
  - SecurityAlert
  connectorId: MicrosoftCloudAppSecurity
- dataTypes:
  - CloudAppEvents
  - DeviceEvents
  - DeviceFileEvents
  connectorId: MicrosoftThreatProtection

{
  "$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"
    }
  ]
}