Microsoft Sentinel Analytic Rules
cloudbrothers.infoAzure Sentinel RepoToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage
  1. Microsoft Sentinel Analytics Rules
  2. /
  3. Analytic Rules
  4. /
  5. GSA Enriched Office 365 - SharePointFileOperation via devices with previously unseen user agents

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

Back
Idefd17c5f-5167-40f8-a1e9-0818940785d9
RulenameGSA Enriched Office 365 - SharePointFileOperation via devices with previously unseen user agents
DescriptionIdentifies anomalies if the number of documents uploaded or downloaded from device(s) associated with a previously unseen user agent exceeds a threshold (default is 5) and deviation (default is 25%).
SeverityMedium
TacticsExfiltration
TechniquesT1030
Required data connectorsAzureActiveDirectory
Office365
KindScheduled
Query frequency1d
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Analytic Rules/Office 365 - SharePoint_Downloads_byNewUserAgent.yaml
Version2.2.6
Arm templateefd17c5f-5167-40f8-a1e9-0818940785d9.json
Deploy To Azure
let threshold = 5;
let szSharePointFileOperation = "SharePointFileOperation";
let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
let starttime = 14d;
let endtime = 1d;

// OfficeActivity - Base Events
let BaseeventsOffice = OfficeActivity
    | where TimeGenerated between (ago(starttime)..ago(endtime))
    | where RecordType =~ szSharePointFileOperation
    | where Operation in~ (szOperations)
    | where isnotempty(UserAgent);

// OfficeActivity - Frequent User Agents
let FrequentUAOffice = BaseeventsOffice
    | summarize FUACount = count() by UserAgent, RecordType, Operation
    | where FUACount >= threshold
    | distinct UserAgent;

// OfficeActivity - User Baseline
let UserBaseLineOffice = BaseeventsOffice
    | summarize Count = count() by UserId, Operation, Site_Url
    | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;

// OfficeActivity - Recent User Activity
let RecentActivityOffice = OfficeActivity
    | where TimeGenerated > ago(endtime)
    | where RecordType =~ szSharePointFileOperation
    | where Operation in~ (szOperations)
    | where isnotempty(UserAgent)
    | where UserAgent in~ (FrequentUAOffice)
    | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), OfficeObjectIdCount = dcount(OfficeObjectId), OfficeObjectIdList = make_set(OfficeObjectId), UserAgentSeenCount = count()
        by RecordType, Operation, UserAgent, UserType, UserId, ClientIP , OfficeWorkload, Site_Url;

// EnrichedMicrosoft365AuditLogs - Base Events
let BaseeventsEnriched = EnrichedMicrosoft365AuditLogs
    | where TimeGenerated between (ago(starttime)..ago(endtime))
    | where RecordType == szSharePointFileOperation
    | where Operation in (szOperations)
    | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)
    | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
    | where isnotempty(UserAgent);

// EnrichedMicrosoft365AuditLogs - Frequent User Agents
let FrequentUAEnriched = BaseeventsEnriched
    | summarize FUACount = count() by UserAgent, RecordType, Operation
    | where FUACount >= threshold
    | distinct UserAgent;

// EnrichedMicrosoft365AuditLogs - User Baseline
let UserBaseLineEnriched = BaseeventsEnriched
    | summarize Count = count() by UserId, Operation, Site_Url
    | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;

// EnrichedMicrosoft365AuditLogs - Recent User Activity
let RecentActivityEnriched = EnrichedMicrosoft365AuditLogs
    | where TimeGenerated > ago(endtime)
    | where RecordType == szSharePointFileOperation
    | where Operation in (szOperations)
    | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)
    | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
    | extend  ClientIP = ClientIp
    | where isnotempty(UserAgent)
    | where UserAgent in (FrequentUAEnriched)
    | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ObjectIdCount = dcount(ObjectId), ObjectIdList = make_set(ObjectId), UserAgentSeenCount = count()
        by RecordType, Operation, UserAgent, UserId,ClientIP, Site_Url;

// Combine Baseline and Recent Activity, Calculate Deviation, and Deduplicate
let UserBehaviorAnalysisOffice = UserBaseLineOffice
    | join kind=inner (RecentActivityOffice) on UserId, Operation, Site_Url
    | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;

let UserBehaviorAnalysisEnriched = UserBaseLineEnriched
    | join kind=inner (RecentActivityEnriched) on UserId, Operation, Site_Url
    | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;

// Combine Office and Enriched Logs
let CombinedUserBehaviorAnalysis = UserBehaviorAnalysisOffice
    | union UserBehaviorAnalysisEnriched;

// Filter and Format Final Results
CombinedUserBehaviorAnalysis
    | where Deviation > 0.25
    | extend UserIdName = tostring(split(UserId, '@')[0]), 
             UserIdUPNSuffix = tostring(split(UserId, '@')[1])
    | project-reorder StartTimeUtc, EndTimeUtc, UserAgent, UserAgentSeenCount, UserId, ClientIP, Site_Url
    | order by UserAgentSeenCount desc, UserAgent asc, UserId asc, Site_Url asc

relevantTechniques:
- T1030
name: GSA Enriched Office 365 - SharePointFileOperation via devices with previously unseen user agents
requiredDataConnectors:
- dataTypes:
  - EnrichedMicrosoft365AuditLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - OfficeActivity (SharePoint)
  connectorId: Office365
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: UserId
  - identifier: Name
    columnName: UserIdName
  - identifier: UPNSuffix
    columnName: UserIdUPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: ClientIP
  entityType: IP
- fieldMappings:
  - identifier: Url
    columnName: Site_Url
  entityType: URL
triggerThreshold: 0
id: efd17c5f-5167-40f8-a1e9-0818940785d9
tactics:
- Exfiltration
version: 2.2.6
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Analytic Rules/Office 365 - SharePoint_Downloads_byNewUserAgent.yaml
queryPeriod: 14d
kind: Scheduled
queryFrequency: 1d
severity: Medium
status: Available
description: |
    Identifies anomalies if the number of documents uploaded or downloaded from device(s) associated with a previously unseen user agent exceeds a threshold (default is 5) and deviation (default is 25%).
query: |
  let threshold = 5;
  let szSharePointFileOperation = "SharePointFileOperation";
  let szOperations = dynamic(["FileDownloaded", "FileUploaded"]);
  let starttime = 14d;
  let endtime = 1d;

  // OfficeActivity - Base Events
  let BaseeventsOffice = OfficeActivity
      | where TimeGenerated between (ago(starttime)..ago(endtime))
      | where RecordType =~ szSharePointFileOperation
      | where Operation in~ (szOperations)
      | where isnotempty(UserAgent);

  // OfficeActivity - Frequent User Agents
  let FrequentUAOffice = BaseeventsOffice
      | summarize FUACount = count() by UserAgent, RecordType, Operation
      | where FUACount >= threshold
      | distinct UserAgent;

  // OfficeActivity - User Baseline
  let UserBaseLineOffice = BaseeventsOffice
      | summarize Count = count() by UserId, Operation, Site_Url
      | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;

  // OfficeActivity - Recent User Activity
  let RecentActivityOffice = OfficeActivity
      | where TimeGenerated > ago(endtime)
      | where RecordType =~ szSharePointFileOperation
      | where Operation in~ (szOperations)
      | where isnotempty(UserAgent)
      | where UserAgent in~ (FrequentUAOffice)
      | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), OfficeObjectIdCount = dcount(OfficeObjectId), OfficeObjectIdList = make_set(OfficeObjectId), UserAgentSeenCount = count()
          by RecordType, Operation, UserAgent, UserType, UserId, ClientIP , OfficeWorkload, Site_Url;

  // EnrichedMicrosoft365AuditLogs - Base Events
  let BaseeventsEnriched = EnrichedMicrosoft365AuditLogs
      | where TimeGenerated between (ago(starttime)..ago(endtime))
      | where RecordType == szSharePointFileOperation
      | where Operation in (szOperations)
      | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)
      | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
      | where isnotempty(UserAgent);

  // EnrichedMicrosoft365AuditLogs - Frequent User Agents
  let FrequentUAEnriched = BaseeventsEnriched
      | summarize FUACount = count() by UserAgent, RecordType, Operation
      | where FUACount >= threshold
      | distinct UserAgent;

  // EnrichedMicrosoft365AuditLogs - User Baseline
  let UserBaseLineEnriched = BaseeventsEnriched
      | summarize Count = count() by UserId, Operation, Site_Url
      | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;

  // EnrichedMicrosoft365AuditLogs - Recent User Activity
  let RecentActivityEnriched = EnrichedMicrosoft365AuditLogs
      | where TimeGenerated > ago(endtime)
      | where RecordType == szSharePointFileOperation
      | where Operation in (szOperations)
      | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)
      | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)
      | extend  ClientIP = ClientIp
      | where isnotempty(UserAgent)
      | where UserAgent in (FrequentUAEnriched)
      | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ObjectIdCount = dcount(ObjectId), ObjectIdList = make_set(ObjectId), UserAgentSeenCount = count()
          by RecordType, Operation, UserAgent, UserId,ClientIP, Site_Url;

  // Combine Baseline and Recent Activity, Calculate Deviation, and Deduplicate
  let UserBehaviorAnalysisOffice = UserBaseLineOffice
      | join kind=inner (RecentActivityOffice) on UserId, Operation, Site_Url
      | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;

  let UserBehaviorAnalysisEnriched = UserBaseLineEnriched
      | join kind=inner (RecentActivityEnriched) on UserId, Operation, Site_Url
      | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;

  // Combine Office and Enriched Logs
  let CombinedUserBehaviorAnalysis = UserBehaviorAnalysisOffice
      | union UserBehaviorAnalysisEnriched;

  // Filter and Format Final Results
  CombinedUserBehaviorAnalysis
      | where Deviation > 0.25
      | extend UserIdName = tostring(split(UserId, '@')[0]), 
               UserIdUPNSuffix = tostring(split(UserId, '@')[1])
      | project-reorder StartTimeUtc, EndTimeUtc, UserAgent, UserAgentSeenCount, UserId, ClientIP, Site_Url
      | order by UserAgentSeenCount desc, UserAgent asc, UserId asc, Site_Url asc  
triggerOperator: gt

{
  "$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/efd17c5f-5167-40f8-a1e9-0818940785d9')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/efd17c5f-5167-40f8-a1e9-0818940785d9')]",
      "properties": {
        "alertRuleTemplateName": "efd17c5f-5167-40f8-a1e9-0818940785d9",
        "customDetails": null,
        "description": "Identifies anomalies if the number of documents uploaded or downloaded from device(s) associated with a previously unseen user agent exceeds a threshold (default is 5) and deviation (default is 25%).\n",
        "displayName": "GSA Enriched Office 365 - SharePointFileOperation via devices with previously unseen user agents",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserId",
                "identifier": "FullName"
              },
              {
                "columnName": "UserIdName",
                "identifier": "Name"
              },
              {
                "columnName": "UserIdUPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "ClientIP",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "URL",
            "fieldMappings": [
              {
                "columnName": "Site_Url",
                "identifier": "Url"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Global Secure Access/Analytic Rules/Office 365 - SharePoint_Downloads_byNewUserAgent.yaml",
        "query": "let threshold = 5;\nlet szSharePointFileOperation = \"SharePointFileOperation\";\nlet szOperations = dynamic([\"FileDownloaded\", \"FileUploaded\"]);\nlet starttime = 14d;\nlet endtime = 1d;\n\n// OfficeActivity - Base Events\nlet BaseeventsOffice = OfficeActivity\n    | where TimeGenerated between (ago(starttime)..ago(endtime))\n    | where RecordType =~ szSharePointFileOperation\n    | where Operation in~ (szOperations)\n    | where isnotempty(UserAgent);\n\n// OfficeActivity - Frequent User Agents\nlet FrequentUAOffice = BaseeventsOffice\n    | summarize FUACount = count() by UserAgent, RecordType, Operation\n    | where FUACount >= threshold\n    | distinct UserAgent;\n\n// OfficeActivity - User Baseline\nlet UserBaseLineOffice = BaseeventsOffice\n    | summarize Count = count() by UserId, Operation, Site_Url\n    | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;\n\n// OfficeActivity - Recent User Activity\nlet RecentActivityOffice = OfficeActivity\n    | where TimeGenerated > ago(endtime)\n    | where RecordType =~ szSharePointFileOperation\n    | where Operation in~ (szOperations)\n    | where isnotempty(UserAgent)\n    | where UserAgent in~ (FrequentUAOffice)\n    | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), OfficeObjectIdCount = dcount(OfficeObjectId), OfficeObjectIdList = make_set(OfficeObjectId), UserAgentSeenCount = count()\n        by RecordType, Operation, UserAgent, UserType, UserId, ClientIP , OfficeWorkload, Site_Url;\n\n// EnrichedMicrosoft365AuditLogs - Base Events\nlet BaseeventsEnriched = EnrichedMicrosoft365AuditLogs\n    | where TimeGenerated between (ago(starttime)..ago(endtime))\n    | where RecordType == szSharePointFileOperation\n    | where Operation in (szOperations)\n    | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)\n    | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)\n    | where isnotempty(UserAgent);\n\n// EnrichedMicrosoft365AuditLogs - Frequent User Agents\nlet FrequentUAEnriched = BaseeventsEnriched\n    | summarize FUACount = count() by UserAgent, RecordType, Operation\n    | where FUACount >= threshold\n    | distinct UserAgent;\n\n// EnrichedMicrosoft365AuditLogs - User Baseline\nlet UserBaseLineEnriched = BaseeventsEnriched\n    | summarize Count = count() by UserId, Operation, Site_Url\n    | summarize AvgCount = avg(Count) by UserId, Operation, Site_Url;\n\n// EnrichedMicrosoft365AuditLogs - Recent User Activity\nlet RecentActivityEnriched = EnrichedMicrosoft365AuditLogs\n    | where TimeGenerated > ago(endtime)\n    | where RecordType == szSharePointFileOperation\n    | where Operation in (szOperations)\n    | extend UserAgent = tostring(parse_json(tostring(AdditionalProperties)).UserAgent)\n    | extend Site_Url = tostring(parse_json(tostring(AdditionalProperties)).SiteUrl)\n    | extend  ClientIP = ClientIp\n    | where isnotempty(UserAgent)\n    | where UserAgent in (FrequentUAEnriched)\n    | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), ObjectIdCount = dcount(ObjectId), ObjectIdList = make_set(ObjectId), UserAgentSeenCount = count()\n        by RecordType, Operation, UserAgent, UserId,ClientIP, Site_Url;\n\n// Combine Baseline and Recent Activity, Calculate Deviation, and Deduplicate\nlet UserBehaviorAnalysisOffice = UserBaseLineOffice\n    | join kind=inner (RecentActivityOffice) on UserId, Operation, Site_Url\n    | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;\n\nlet UserBehaviorAnalysisEnriched = UserBaseLineEnriched\n    | join kind=inner (RecentActivityEnriched) on UserId, Operation, Site_Url\n    | extend Deviation = abs(UserAgentSeenCount - AvgCount) / AvgCount;\n\n// Combine Office and Enriched Logs\nlet CombinedUserBehaviorAnalysis = UserBehaviorAnalysisOffice\n    | union UserBehaviorAnalysisEnriched;\n\n// Filter and Format Final Results\nCombinedUserBehaviorAnalysis\n    | where Deviation > 0.25\n    | extend UserIdName = tostring(split(UserId, '@')[0]), \n             UserIdUPNSuffix = tostring(split(UserId, '@')[1])\n    | project-reorder StartTimeUtc, EndTimeUtc, UserAgent, UserAgentSeenCount, UserId, ClientIP, Site_Url\n    | order by UserAgentSeenCount desc, UserAgent asc, UserId asc, Site_Url asc\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "status": "Available",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Exfiltration"
        ],
        "techniques": [
          "T1030"
        ],
        "templateVersion": "2.2.6",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}