SharePointFileOperation via devices with previously unseen user agents

let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let lookback = starttime - 14d;
let MINIMUM_BLOCKS = 10;
let SUCCESS_THRESHOLD = 0.2;
let HistoricalActivity = 
  SigninLogs
  | where TimeGenerated > lookback
  | where isnotempty(ClientAppUsed)
  | summarize SuccessfulSignins = countif(ResultType == "0"), BlockedSignins = countif(ResultType == "50053") by ClientAppUsed
  | extend SuccessBlockRatio = 1.00 * SuccessfulSignins / BlockedSignins
  | where SuccessBlockRatio < SUCCESS_THRESHOLD
  | where BlockedSignins > MINIMUM_BLOCKS;
EnrichedMicrosoft365AuditLogs
| where TimeGenerated between (starttime .. endtime)
| where RecordType == "SharePointFileOperation"
| where Operation in ("FileDownloaded", "FileUploaded")
| extend ClientAppUsed = tostring(parse_json(AdditionalProperties).UserAgent)
| extend SiteUrl = tostring(parse_json(AdditionalProperties).SiteUrl)
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by ClientAppUsed, UserId, ClientIp, SiteUrl
| join kind=innerunique (HistoricalActivity) on ClientAppUsed
| project-away ClientAppUsed1
| extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
| extend IP_0_Address = ClientIp
| extend Account_0_Name = AccountName
| extend Account_0_UPNSuffix = AccountUPNSuffix
| extend URL_0_Url = SiteUrl

entityMappings:
- entityType: IP
  fieldMappings:
  - columnName: IP_0_Address
    identifier: Address
- entityType: Account
  fieldMappings:
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
- entityType: URL
  fieldMappings:
  - columnName: URL_0_Url
    identifier: Url
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/new_sharepoint_downloads_by_UserAgent.yaml
requiredDataConnectors:
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - EnrichedMicrosoft365AuditLogs
  connectorId: AzureActiveDirectory
description: |
  'Tracking via user agent is one way to differentiate between types of connecting device.
  In homogeneous enterprise environments the user agent associated with an attacker device may stand out as unusual.'  
query: |
  let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  let lookback = starttime - 14d;
  let MINIMUM_BLOCKS = 10;
  let SUCCESS_THRESHOLD = 0.2;
  let HistoricalActivity = 
    SigninLogs
    | where TimeGenerated > lookback
    | where isnotempty(ClientAppUsed)
    | summarize SuccessfulSignins = countif(ResultType == "0"), BlockedSignins = countif(ResultType == "50053") by ClientAppUsed
    | extend SuccessBlockRatio = 1.00 * SuccessfulSignins / BlockedSignins
    | where SuccessBlockRatio < SUCCESS_THRESHOLD
    | where BlockedSignins > MINIMUM_BLOCKS;
  EnrichedMicrosoft365AuditLogs
  | where TimeGenerated between (starttime .. endtime)
  | where RecordType == "SharePointFileOperation"
  | where Operation in ("FileDownloaded", "FileUploaded")
  | extend ClientAppUsed = tostring(parse_json(AdditionalProperties).UserAgent)
  | extend SiteUrl = tostring(parse_json(AdditionalProperties).SiteUrl)
  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by ClientAppUsed, UserId, ClientIp, SiteUrl
  | join kind=innerunique (HistoricalActivity) on ClientAppUsed
  | project-away ClientAppUsed1
  | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
  | extend IP_0_Address = ClientIp
  | extend Account_0_Name = AccountName
  | extend Account_0_UPNSuffix = AccountUPNSuffix
  | extend URL_0_Url = SiteUrl  
relevantTechniques:
- T1030
version: 2.0.1
name: SharePointFileOperation via devices with previously unseen user agents
kind: Scheduled
tactics:
- Exfiltration
id: f2367171-1514-4c67-88ef-27434b6a1093

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/f2367171-1514-4c67-88ef-27434b6a1093')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/f2367171-1514-4c67-88ef-27434b6a1093')]",
      "properties": {
        "alertRuleTemplateName": "f2367171-1514-4c67-88ef-27434b6a1093",
        "customDetails": null,
        "description": "'Tracking via user agent is one way to differentiate between types of connecting device.\nIn homogeneous enterprise environments the user agent associated with an attacker device may stand out as unusual.'\n",
        "displayName": "SharePointFileOperation via devices with previously unseen user agents",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "IP_0_Address",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "AccountUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "URL",
            "fieldMappings": [
              {
                "columnName": "URL_0_Url",
                "identifier": "Url"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/new_sharepoint_downloads_by_UserAgent.yaml",
        "query": "let starttime = todatetime('{{StartTimeISO}}');\nlet endtime = todatetime('{{EndTimeISO}}');\nlet lookback = starttime - 14d;\nlet MINIMUM_BLOCKS = 10;\nlet SUCCESS_THRESHOLD = 0.2;\nlet HistoricalActivity = \n  SigninLogs\n  | where TimeGenerated > lookback\n  | where isnotempty(ClientAppUsed)\n  | summarize SuccessfulSignins = countif(ResultType == \"0\"), BlockedSignins = countif(ResultType == \"50053\") by ClientAppUsed\n  | extend SuccessBlockRatio = 1.00 * SuccessfulSignins / BlockedSignins\n  | where SuccessBlockRatio < SUCCESS_THRESHOLD\n  | where BlockedSignins > MINIMUM_BLOCKS;\nEnrichedMicrosoft365AuditLogs\n| where TimeGenerated between (starttime .. endtime)\n| where RecordType == \"SharePointFileOperation\"\n| where Operation in (\"FileDownloaded\", \"FileUploaded\")\n| extend ClientAppUsed = tostring(parse_json(AdditionalProperties).UserAgent)\n| extend SiteUrl = tostring(parse_json(AdditionalProperties).SiteUrl)\n| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by ClientAppUsed, UserId, ClientIp, SiteUrl\n| join kind=innerunique (HistoricalActivity) on ClientAppUsed\n| project-away ClientAppUsed1\n| extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n| extend IP_0_Address = ClientIp\n| extend Account_0_Name = AccountName\n| extend Account_0_UPNSuffix = AccountUPNSuffix\n| extend URL_0_Url = SiteUrl\n",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Exfiltration"
        ],
        "techniques": [
          "T1030"
        ],
        "templateVersion": "2.0.1"
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}