Linked Malicious Storage Artifacts

//Collect the alert events
let alertData = SecurityAlert
| where DisplayName has "Potential malware uploaded to"
| extend Entities = parse_json(Entities)
| mv-expand Entities;
//Parse the IP address data
let ipData = alertData
| where Entities['Type'] =~ "ip"
| extend AttackerIP = tostring(Entities['Address']), AttackerCountry = tostring(Entities['Location']['CountryName']);
//Parse the file data
let FileData = alertData
| where Entities['Type'] =~ "file"
| extend MaliciousFileDirectory = tostring(Entities['Directory']), MaliciousFileName = tostring(Entities['Name']), MaliciousFileHashes = tostring(Entities['FileHashes']);
//Combine the File and IP data together
ipData
| join (FileData) on VendorOriginalId
| summarize by TimeGenerated, AttackerIP, AttackerCountry, DisplayName, ResourceId, AlertType, MaliciousFileDirectory, MaliciousFileName, MaliciousFileHashes
//Create a type column so we can track if it was a File storage or blobl storage upload
| extend type = iff(DisplayName has "file", "File", "Blob")
| join (
  union
  StorageFileLogs,
  StorageBlobLogs
  //File upload operations
  | where OperationName =~ "PutBlob" or OperationName =~ "PutRange"
  //Parse out the uploader IP
  | extend ClientIP = tostring(split(CallerIpAddress, ":", 0)[0])
  //Extract the filename from the Uri
  | extend FileName = extract(@"\/([\w\-. ]+)\?", 1, Uri)
  //Base64 decode the MD5 filehash, we will encounter non-ascii hex so string operations don't work
  //We can work around this by making it an array then converting it to hex from an int
  | extend base64Char = base64_decode_toarray(ResponseMd5)
  | mv-expand base64Char
  | extend hexChar = tohex(toint(base64Char))
  | extend hexChar = iff(strlen(hexChar) < 2, strcat("0", hexChar), hexChar)
  | extend SourceTable = iff(OperationName has "range", "StorageFileLogs", "StorageBlobLogs")
  | summarize make_list(hexChar) by CorrelationId, ResponseMd5, FileName, AccountName, TimeGenerated, RequestBodySize, ClientIP, SourceTable
  | extend Md5Hash = strcat_array(list_hexChar, "")
  //Pack the file information the summarise into a ClientIP row
  | extend p = pack("FileName", FileName, "FileSize", RequestBodySize, "Md5Hash", Md5Hash, "Time", TimeGenerated, "SourceTable", SourceTable)
  | summarize UploadedFileInfo=make_list(p), FilesUploaded=count() by ClientIP
      | join kind=leftouter (
        union
        StorageFileLogs,
        StorageBlobLogs
        | where OperationName =~ "DeleteFile" or OperationName =~ "DeleteBlob"
        | extend ClientIP = tostring(split(CallerIpAddress, ":", 0)[0])
        | extend FileName = extract(@"\/([\w\-. ]+)\?", 1, Uri)
        | extend SourceTable = iff(OperationName has "range", "StorageFileLogs", "StorageBlobLogs")
        | extend p = pack("FileName", FileName, "Time", TimeGenerated, "SourceTable", SourceTable)
        | summarize DeletedFileInfo=make_list(p), FilesDeleted=count() by ClientIP
        ) on ClientIP
  ) on $left.AttackerIP == $right.ClientIP
| mvexpand UploadedFileInfo
| extend LinkedMaliciousFileName = UploadedFileInfo.FileName
| extend LinkedMaliciousFileHash = UploadedFileInfo.Md5Hash
| project AlertTimeGenerated = TimeGenerated, tostring(LinkedMaliciousFileName), tostring(LinkedMaliciousFileHash), AlertType, AttackerIP, AttackerCountry, MaliciousFileDirectory, MaliciousFileName, FilesUploaded, UploadedFileInfo
| extend FileHashCustomEntity = LinkedMaliciousFileName, HashAlgorithm = "MD5", IPCustomEntity = AttackerIP

severity: Medium
triggerThreshold: 0
query: |
  //Collect the alert events
  let alertData = SecurityAlert
  | where DisplayName has "Potential malware uploaded to"
  | extend Entities = parse_json(Entities)
  | mv-expand Entities;
  //Parse the IP address data
  let ipData = alertData
  | where Entities['Type'] =~ "ip"
  | extend AttackerIP = tostring(Entities['Address']), AttackerCountry = tostring(Entities['Location']['CountryName']);
  //Parse the file data
  let FileData = alertData
  | where Entities['Type'] =~ "file"
  | extend MaliciousFileDirectory = tostring(Entities['Directory']), MaliciousFileName = tostring(Entities['Name']), MaliciousFileHashes = tostring(Entities['FileHashes']);
  //Combine the File and IP data together
  ipData
  | join (FileData) on VendorOriginalId
  | summarize by TimeGenerated, AttackerIP, AttackerCountry, DisplayName, ResourceId, AlertType, MaliciousFileDirectory, MaliciousFileName, MaliciousFileHashes
  //Create a type column so we can track if it was a File storage or blobl storage upload
  | extend type = iff(DisplayName has "file", "File", "Blob")
  | join (
    union
    StorageFileLogs,
    StorageBlobLogs
    //File upload operations
    | where OperationName =~ "PutBlob" or OperationName =~ "PutRange"
    //Parse out the uploader IP
    | extend ClientIP = tostring(split(CallerIpAddress, ":", 0)[0])
    //Extract the filename from the Uri
    | extend FileName = extract(@"\/([\w\-. ]+)\?", 1, Uri)
    //Base64 decode the MD5 filehash, we will encounter non-ascii hex so string operations don't work
    //We can work around this by making it an array then converting it to hex from an int
    | extend base64Char = base64_decode_toarray(ResponseMd5)
    | mv-expand base64Char
    | extend hexChar = tohex(toint(base64Char))
    | extend hexChar = iff(strlen(hexChar) < 2, strcat("0", hexChar), hexChar)
    | extend SourceTable = iff(OperationName has "range", "StorageFileLogs", "StorageBlobLogs")
    | summarize make_list(hexChar) by CorrelationId, ResponseMd5, FileName, AccountName, TimeGenerated, RequestBodySize, ClientIP, SourceTable
    | extend Md5Hash = strcat_array(list_hexChar, "")
    //Pack the file information the summarise into a ClientIP row
    | extend p = pack("FileName", FileName, "FileSize", RequestBodySize, "Md5Hash", Md5Hash, "Time", TimeGenerated, "SourceTable", SourceTable)
    | summarize UploadedFileInfo=make_list(p), FilesUploaded=count() by ClientIP
        | join kind=leftouter (
          union
          StorageFileLogs,
          StorageBlobLogs
          | where OperationName =~ "DeleteFile" or OperationName =~ "DeleteBlob"
          | extend ClientIP = tostring(split(CallerIpAddress, ":", 0)[0])
          | extend FileName = extract(@"\/([\w\-. ]+)\?", 1, Uri)
          | extend SourceTable = iff(OperationName has "range", "StorageFileLogs", "StorageBlobLogs")
          | extend p = pack("FileName", FileName, "Time", TimeGenerated, "SourceTable", SourceTable)
          | summarize DeletedFileInfo=make_list(p), FilesDeleted=count() by ClientIP
          ) on ClientIP
    ) on $left.AttackerIP == $right.ClientIP
  | mvexpand UploadedFileInfo
  | extend LinkedMaliciousFileName = UploadedFileInfo.FileName
  | extend LinkedMaliciousFileHash = UploadedFileInfo.Md5Hash
  | project AlertTimeGenerated = TimeGenerated, tostring(LinkedMaliciousFileName), tostring(LinkedMaliciousFileHash), AlertType, AttackerIP, AttackerCountry, MaliciousFileDirectory, MaliciousFileName, FilesUploaded, UploadedFileInfo
  | extend FileHashCustomEntity = LinkedMaliciousFileName, HashAlgorithm = "MD5", IPCustomEntity = AttackerIP  
queryFrequency: 1d
requiredDataConnectors:
- connectorId: MicrosoftCloudAppSecurity
  dataTypes:
  - SecurityAlert
id: b9e3b9f8-a406-4151-9891-e5ff1ddd8c1d
version: 1.0.1
name: Linked Malicious Storage Artifacts
kind: Scheduled
status: Available
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender for Cloud Apps/Analytic Rules/AdditionalFilesUploadedByActor.yaml
queryPeriod: 1d
relevantTechniques:
- T1071
- T1567
triggerOperator: gt
tactics:
- CommandAndControl
- Exfiltration
description: |
    'An IP address which uploaded malicious content to an Azure Blob or File Storage container (triggering a malware alert) also uploaded additional files.'
entityMappings:
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: IPCustomEntity
- entityType: FileHash
  fieldMappings:
  - identifier: Algorithm
    columnName: HashAlgorithm
  - identifier: Value
    columnName: FileHashCustomEntity

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/b9e3b9f8-a406-4151-9891-e5ff1ddd8c1d')]",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/b9e3b9f8-a406-4151-9891-e5ff1ddd8c1d')]",
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules",
      "kind": "Scheduled",
      "apiVersion": "2022-11-01",
      "properties": {
        "displayName": "Linked Malicious Storage Artifacts",
        "description": "'An IP address which uploaded malicious content to an Azure Blob or File Storage container (triggering a malware alert) also uploaded additional files.'\n",
        "severity": "Medium",
        "enabled": true,
        "query": "//Collect the alert events\nlet alertData = SecurityAlert\n| where DisplayName has \"Potential malware uploaded to\"\n| extend Entities = parse_json(Entities)\n| mv-expand Entities;\n//Parse the IP address data\nlet ipData = alertData\n| where Entities['Type'] =~ \"ip\"\n| extend AttackerIP = tostring(Entities['Address']), AttackerCountry = tostring(Entities['Location']['CountryName']);\n//Parse the file data\nlet FileData = alertData\n| where Entities['Type'] =~ \"file\"\n| extend MaliciousFileDirectory = tostring(Entities['Directory']), MaliciousFileName = tostring(Entities['Name']), MaliciousFileHashes = tostring(Entities['FileHashes']);\n//Combine the File and IP data together\nipData\n| join (FileData) on VendorOriginalId\n| summarize by TimeGenerated, AttackerIP, AttackerCountry, DisplayName, ResourceId, AlertType, MaliciousFileDirectory, MaliciousFileName, MaliciousFileHashes\n//Create a type column so we can track if it was a File storage or blobl storage upload\n| extend type = iff(DisplayName has \"file\", \"File\", \"Blob\")\n| join (\n  union\n  StorageFileLogs,\n  StorageBlobLogs\n  //File upload operations\n  | where OperationName =~ \"PutBlob\" or OperationName =~ \"PutRange\"\n  //Parse out the uploader IP\n  | extend ClientIP = tostring(split(CallerIpAddress, \":\", 0)[0])\n  //Extract the filename from the Uri\n  | extend FileName = extract(@\"\\/([\\w\\-. ]+)\\?\", 1, Uri)\n  //Base64 decode the MD5 filehash, we will encounter non-ascii hex so string operations don't work\n  //We can work around this by making it an array then converting it to hex from an int\n  | extend base64Char = base64_decode_toarray(ResponseMd5)\n  | mv-expand base64Char\n  | extend hexChar = tohex(toint(base64Char))\n  | extend hexChar = iff(strlen(hexChar) < 2, strcat(\"0\", hexChar), hexChar)\n  | extend SourceTable = iff(OperationName has \"range\", \"StorageFileLogs\", \"StorageBlobLogs\")\n  | summarize make_list(hexChar) by CorrelationId, ResponseMd5, FileName, AccountName, TimeGenerated, RequestBodySize, ClientIP, SourceTable\n  | extend Md5Hash = strcat_array(list_hexChar, \"\")\n  //Pack the file information the summarise into a ClientIP row\n  | extend p = pack(\"FileName\", FileName, \"FileSize\", RequestBodySize, \"Md5Hash\", Md5Hash, \"Time\", TimeGenerated, \"SourceTable\", SourceTable)\n  | summarize UploadedFileInfo=make_list(p), FilesUploaded=count() by ClientIP\n      | join kind=leftouter (\n        union\n        StorageFileLogs,\n        StorageBlobLogs\n        | where OperationName =~ \"DeleteFile\" or OperationName =~ \"DeleteBlob\"\n        | extend ClientIP = tostring(split(CallerIpAddress, \":\", 0)[0])\n        | extend FileName = extract(@\"\\/([\\w\\-. ]+)\\?\", 1, Uri)\n        | extend SourceTable = iff(OperationName has \"range\", \"StorageFileLogs\", \"StorageBlobLogs\")\n        | extend p = pack(\"FileName\", FileName, \"Time\", TimeGenerated, \"SourceTable\", SourceTable)\n        | summarize DeletedFileInfo=make_list(p), FilesDeleted=count() by ClientIP\n        ) on ClientIP\n  ) on $left.AttackerIP == $right.ClientIP\n| mvexpand UploadedFileInfo\n| extend LinkedMaliciousFileName = UploadedFileInfo.FileName\n| extend LinkedMaliciousFileHash = UploadedFileInfo.Md5Hash\n| project AlertTimeGenerated = TimeGenerated, tostring(LinkedMaliciousFileName), tostring(LinkedMaliciousFileHash), AlertType, AttackerIP, AttackerCountry, MaliciousFileDirectory, MaliciousFileName, FilesUploaded, UploadedFileInfo\n| extend FileHashCustomEntity = LinkedMaliciousFileName, HashAlgorithm = \"MD5\", IPCustomEntity = AttackerIP\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P1D",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0,
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CommandAndControl",
          "Exfiltration"
        ],
        "techniques": [
          "T1071",
          "T1567"
        ],
        "alertRuleTemplateName": "b9e3b9f8-a406-4151-9891-e5ff1ddd8c1d",
        "customDetails": null,
        "entityMappings": [
          {
            "fieldMappings": [
              {
                "columnName": "IPCustomEntity",
                "identifier": "Address"
              }
            ],
            "entityType": "IP"
          },
          {
            "fieldMappings": [
              {
                "columnName": "HashAlgorithm",
                "identifier": "Algorithm"
              },
              {
                "columnName": "FileHashCustomEntity",
                "identifier": "Value"
              }
            ],
            "entityType": "FileHash"
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Defender for Cloud Apps/Analytic Rules/AdditionalFilesUploadedByActor.yaml",
        "templateVersion": "1.0.1",
        "status": "Available"
      }
    }
  ]
}