Microsoft Sentinel Analytic Rules
cloudbrothers.infoAzure Sentinel RepoToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

Mass Download copy to USB device by single user

Back
Id6267ce44-1e9d-471b-9f1e-ae76a6b7aa84
RulenameMass Download & copy to USB device by single user
DescriptionThis 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
SeverityMedium
TacticsExfiltration
TechniquesT1052
Required data connectorsMicrosoftCloudAppSecurity
MicrosoftThreatProtection
KindScheduled
Query frequency1d
Query period1d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Detections/SecurityAlert/Massdownload_USBFileCopy.yaml
Version1.0.4
Arm template6267ce44-1e9d-471b-9f1e-ae76a6b7aa84.json
Deploy To Azure
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
kind: Scheduled
triggerThreshold: 0
relevantTechniques:
- T1052
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/SecurityAlert/Massdownload_USBFileCopy.yaml
requiredDataConnectors:
- dataTypes:
  - SecurityAlert
  connectorId: MicrosoftCloudAppSecurity
- dataTypes:
  - CloudAppEvents
  - DeviceEvents
  - DeviceFileEvents
  connectorId: MicrosoftThreatProtection
queryPeriod: 1d
tactics:
- Exfiltration
severity: Medium
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'  
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  
name: Mass Download & copy to USB device by single user
version: 1.0.4
metadata:
  support:
    tier: Community
  categories:
    domains:
    - Security - Others
  author:
    name: Microsoft Security Research
  source:
    kind: Community
id: 6267ce44-1e9d-471b-9f1e-ae76a6b7aa84
queryFrequency: 1d
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
{
  "$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"
    }
  ]
}