GSA Enriched Office 365 -SharePointFileOperation via devices with previously unseen user agents

Back


Id	f2367171-1514-4c67-88ef-27434b6a1093
Rulename	GSA Enriched Office 365 -SharePointFileOperation via devices with previously unseen user agents
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.
Tactics	Exfiltration
Techniques	T1030
Required data connectors	AzureActiveDirectory
Kind	Scheduled
Source Uri	https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/new_sharepoint_downloads_by_UserAgent.yaml
Version	2.0.2
Arm template	f2367171-1514-4c67-88ef-27434b6a1093.json

KQL

let starttime = todatetime('{{StartTimeISO}}');
let endtime = todatetime('{{EndTimeISO}}');
let lookback = starttime - 14d;
let MINIMUM_BLOCKS = 10;
let SUCCESS_THRESHOLD = 0.2;
// Identify user agents or client apps with a low success-to-block ratio in Sign-in Logs
let HistoricalActivity = SigninLogs
    | where TimeGenerated > lookback
    | where isnotempty(UserAgent)
    | summarize SuccessfulSignins = countif(ResultType == "0"), BlockedSignins = countif(ResultType == "50053") by UserAgent
    | extend SuccessBlockRatio = 1.00 * SuccessfulSignins / BlockedSignins
    | where SuccessBlockRatio < SUCCESS_THRESHOLD
    | where BlockedSignins > MINIMUM_BLOCKS;
// OfficeActivity Query: File operations by matching user agents
let OfficeEvents = OfficeActivity
    | where TimeGenerated between (starttime .. endtime)
    | where RecordType == "SharePointFileOperation"
    | where Operation in ("FileDownloaded", "FileUploaded")
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by UserAgent, UserId, ClientIP, Site_Url
    | join kind=innerunique (HistoricalActivity) on UserAgent
    | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
    | extend IP_0_Address = ClientIP, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix, URL_0_Url = Site_Url;
// EnrichedMicrosoft365AuditLogs Query: File operations by matching client apps (UserAgent)
let EnrichedEvents = EnrichedMicrosoft365AuditLogs
    | where TimeGenerated between (starttime .. endtime)
    | where RecordType == "SharePointFileOperation"
    | where Operation in ("FileDownloaded", "FileUploaded")
    | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)  // Ensure matching with UserAgent column
    | extend SiteUrl = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by UserAgent, UserId, ClientIp, SiteUrl
    | join kind=innerunique (HistoricalActivity) on UserAgent
    | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
    | extend IP_0_Address = ClientIp, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix, URL_0_Url = SiteUrl;
// Combine Office and Enriched Logs
let CombinedEvents = OfficeEvents
    | union EnrichedEvents
    | summarize arg_min(StartTime, *) by UserId, ClientIP;
// Final Output
CombinedEvents
    | project StartTime, EndTime, RecentFileActivities, IP_0_Address, Account_0_Name, Account_0_UPNSuffix, URL_0_Url
    | order by StartTime desc;

YAML

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.'  
entityMappings:
- fieldMappings:
  - columnName: IP_0_Address
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: AccountName
    identifier: Name
  - columnName: AccountUPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: URL_0_Url
    identifier: Url
  entityType: URL
kind: Scheduled
requiredDataConnectors:
- connectorId: AzureActiveDirectory
  dataTypes:
  - SigninLogs
- connectorId: AzureActiveDirectory
  dataTypes:
  - EnrichedMicrosoft365AuditLogs
query: |
  let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  let lookback = starttime - 14d;
  let MINIMUM_BLOCKS = 10;
  let SUCCESS_THRESHOLD = 0.2;
  // Identify user agents or client apps with a low success-to-block ratio in Sign-in Logs
  let HistoricalActivity = SigninLogs
      | where TimeGenerated > lookback
      | where isnotempty(UserAgent)
      | summarize SuccessfulSignins = countif(ResultType == "0"), BlockedSignins = countif(ResultType == "50053") by UserAgent
      | extend SuccessBlockRatio = 1.00 * SuccessfulSignins / BlockedSignins
      | where SuccessBlockRatio < SUCCESS_THRESHOLD
      | where BlockedSignins > MINIMUM_BLOCKS;
  // OfficeActivity Query: File operations by matching user agents
  let OfficeEvents = OfficeActivity
      | where TimeGenerated between (starttime .. endtime)
      | where RecordType == "SharePointFileOperation"
      | where Operation in ("FileDownloaded", "FileUploaded")
      | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by UserAgent, UserId, ClientIP, Site_Url
      | join kind=innerunique (HistoricalActivity) on UserAgent
      | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
      | extend IP_0_Address = ClientIP, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix, URL_0_Url = Site_Url;
  // EnrichedMicrosoft365AuditLogs Query: File operations by matching client apps (UserAgent)
  let EnrichedEvents = EnrichedMicrosoft365AuditLogs
      | where TimeGenerated between (starttime .. endtime)
      | where RecordType == "SharePointFileOperation"
      | where Operation in ("FileDownloaded", "FileUploaded")
      | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)  // Ensure matching with UserAgent column
      | extend SiteUrl = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
      | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by UserAgent, UserId, ClientIp, SiteUrl
      | join kind=innerunique (HistoricalActivity) on UserAgent
      | extend AccountName = tostring(split(UserId, "@")[0]), AccountUPNSuffix = tostring(split(UserId, "@")[1])
      | extend IP_0_Address = ClientIp, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix, URL_0_Url = SiteUrl;
  // Combine Office and Enriched Logs
  let CombinedEvents = OfficeEvents
      | union EnrichedEvents
      | summarize arg_min(StartTime, *) by UserId, ClientIP;
  // Final Output
  CombinedEvents
      | project StartTime, EndTime, RecentFileActivities, IP_0_Address, Account_0_Name, Account_0_UPNSuffix, URL_0_Url
      | order by StartTime desc;  
version: 2.0.2
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Hunting Queries/new_sharepoint_downloads_by_UserAgent.yaml
relevantTechniques:
- T1030
tactics:
- Exfiltration
id: f2367171-1514-4c67-88ef-27434b6a1093
name: GSA Enriched Office 365 -SharePointFileOperation via devices with previously unseen user agents

ARM

{
  "$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": "GSA Enriched Office 365 -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;\n// Identify user agents or client apps with a low success-to-block ratio in Sign-in Logs\nlet HistoricalActivity = SigninLogs\n    | where TimeGenerated > lookback\n    | where isnotempty(UserAgent)\n    | summarize SuccessfulSignins = countif(ResultType == \"0\"), BlockedSignins = countif(ResultType == \"50053\") by UserAgent\n    | extend SuccessBlockRatio = 1.00 * SuccessfulSignins / BlockedSignins\n    | where SuccessBlockRatio < SUCCESS_THRESHOLD\n    | where BlockedSignins > MINIMUM_BLOCKS;\n// OfficeActivity Query: File operations by matching user agents\nlet OfficeEvents = OfficeActivity\n    | where TimeGenerated between (starttime .. endtime)\n    | where RecordType == \"SharePointFileOperation\"\n    | where Operation in (\"FileDownloaded\", \"FileUploaded\")\n    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by UserAgent, UserId, ClientIP, Site_Url\n    | join kind=innerunique (HistoricalActivity) on UserAgent\n    | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n    | extend IP_0_Address = ClientIP, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix, URL_0_Url = Site_Url;\n// EnrichedMicrosoft365AuditLogs Query: File operations by matching client apps (UserAgent)\nlet EnrichedEvents = EnrichedMicrosoft365AuditLogs\n    | where TimeGenerated between (starttime .. endtime)\n    | where RecordType == \"SharePointFileOperation\"\n    | where Operation in (\"FileDownloaded\", \"FileUploaded\")\n    | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)  // Ensure matching with UserAgent column\n    | extend SiteUrl = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)\n    | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), RecentFileActivities = count() by UserAgent, UserId, ClientIp, SiteUrl\n    | join kind=innerunique (HistoricalActivity) on UserAgent\n    | extend AccountName = tostring(split(UserId, \"@\")[0]), AccountUPNSuffix = tostring(split(UserId, \"@\")[1])\n    | extend IP_0_Address = ClientIp, Account_0_Name = AccountName, Account_0_UPNSuffix = AccountUPNSuffix, URL_0_Url = SiteUrl;\n// Combine Office and Enriched Logs\nlet CombinedEvents = OfficeEvents\n    | union EnrichedEvents\n    | summarize arg_min(StartTime, *) by UserId, ClientIP;\n// Final Output\nCombinedEvents\n    | project StartTime, EndTime, RecentFileActivities, IP_0_Address, Account_0_Name, Account_0_UPNSuffix, URL_0_Url\n    | order by StartTime desc;\n",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Exfiltration"
        ],
        "techniques": [
          "T1030"
        ],
        "templateVersion": "2.0.2"
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}