Mass Export of Dynamics 365 Records to Excel

Back


Id	05eca115-c4b5-48e4-ba6e-07db57695be2
Rulename	Mass Export of Dynamics 365 Records to Excel
Description	The query detects user exporting a large amount of records from Dynamics 365 to Excel, significantly more records exported than any other recent activity by that user.
Severity	Medium
Tactics	Collection
Techniques	T1530
Required data connectors	Dynamics365
Kind	Scheduled
Query frequency	1d
Query period	7d
Trigger threshold	0
Trigger operator	gt
Source Uri	https://github.com/Azure/Azure-Sentinel/blob/master/Detections/Dynamics365Activity/MassExportOfDynamicstoExcel.yaml
Version	1.0.1
Arm template	05eca115-c4b5-48e4-ba6e-07db57695be2.json

KQL

let baseline_time = 7d;
let detection_time = 1d;
Dynamics365Activity
| where TimeGenerated between(ago(baseline_time)..ago(detection_time-1d))
| where OriginalObjectId contains 'ExportToExcel'
| extend numQueryCount = todouble(QueryResults)
| extend QueryCount = iif(QueryResults contains ",", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)
| extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))
| summarize sum(QueryCount) by UserId
| extend HistoricalBaseline = sum_QueryCount
| join (Dynamics365Activity
| where TimeGenerated > ago(detection_time)
| where OriginalObjectId contains 'ExportToExcel'
| extend numQueryCount = todouble(QueryResults)
| extend QueryCount = iif(QueryResults contains ",", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)
| extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))
| summarize sum(QueryCount) by UserId
| extend CurrentExportRate = sum_QueryCount) on UserId
| where CurrentExportRate > HistoricalBaseline
| project UserId, HistoricalBaseline, CurrentExportRate
| join kind=inner(Dynamics365Activity
| where TimeGenerated > ago(detection_time)
| where OriginalObjectId contains 'ExportToExcel'
| extend numQueryCount = todouble(QueryResults)
| extend QueryCount = iif(QueryResults contains ",", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)
| extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))) on UserId
| project TimeGenerated, UserId, QueryCount, UserAgent, OriginalObjectId, ClientIP, HistoricalBaseline, CurrentExportRate, CorrelationId, CrmOrganizationUniqueName
| summarize QuerySizes = make_set(QueryCount), MostRecentQuery = max(TimeGenerated), IPs = make_set(ClientIP), UserAgents = make_set(UserAgent) by UserId, CrmOrganizationUniqueName, HistoricalBaseline, CurrentExportRate
| extend timestamp = MostRecentQuery, AccountCustomEntity = UserId

YAML

severity: Medium
triggerThreshold: 0
metadata:
  source:
    kind: Community
  support:
    tier: Microsoft
  categories:
    domains:
    - Cloud Provider
    - IT Operations
    - Storage
  author:
    name: Microsoft
query: |
  let baseline_time = 7d;
  let detection_time = 1d;
  Dynamics365Activity
  | where TimeGenerated between(ago(baseline_time)..ago(detection_time-1d))
  | where OriginalObjectId contains 'ExportToExcel'
  | extend numQueryCount = todouble(QueryResults)
  | extend QueryCount = iif(QueryResults contains ",", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)
  | extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))
  | summarize sum(QueryCount) by UserId
  | extend HistoricalBaseline = sum_QueryCount
  | join (Dynamics365Activity
  | where TimeGenerated > ago(detection_time)
  | where OriginalObjectId contains 'ExportToExcel'
  | extend numQueryCount = todouble(QueryResults)
  | extend QueryCount = iif(QueryResults contains ",", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)
  | extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))
  | summarize sum(QueryCount) by UserId
  | extend CurrentExportRate = sum_QueryCount) on UserId
  | where CurrentExportRate > HistoricalBaseline
  | project UserId, HistoricalBaseline, CurrentExportRate
  | join kind=inner(Dynamics365Activity
  | where TimeGenerated > ago(detection_time)
  | where OriginalObjectId contains 'ExportToExcel'
  | extend numQueryCount = todouble(QueryResults)
  | extend QueryCount = iif(QueryResults contains ",", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)
  | extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))) on UserId
  | project TimeGenerated, UserId, QueryCount, UserAgent, OriginalObjectId, ClientIP, HistoricalBaseline, CurrentExportRate, CorrelationId, CrmOrganizationUniqueName
  | summarize QuerySizes = make_set(QueryCount), MostRecentQuery = max(TimeGenerated), IPs = make_set(ClientIP), UserAgents = make_set(UserAgent) by UserId, CrmOrganizationUniqueName, HistoricalBaseline, CurrentExportRate
  | extend timestamp = MostRecentQuery, AccountCustomEntity = UserId  
queryFrequency: 1d
requiredDataConnectors:
- connectorId: Dynamics365
  dataTypes:
  - Dynamics365Activity
id: 05eca115-c4b5-48e4-ba6e-07db57695be2
version: 1.0.1
name: Mass Export of Dynamics 365 Records to Excel
kind: Scheduled
status: Available
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/Dynamics365Activity/MassExportOfDynamicstoExcel.yaml
queryPeriod: 7d
relevantTechniques:
- T1530
triggerOperator: gt
tactics:
- Collection
description: |
    'The query detects user exporting a large amount of records from Dynamics 365 to Excel, significantly more records exported than any other recent activity by that user.'
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: FullName
    columnName: AccountCustomEntity

ARM

{
  "$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/05eca115-c4b5-48e4-ba6e-07db57695be2')]",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/05eca115-c4b5-48e4-ba6e-07db57695be2')]",
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules",
      "kind": "Scheduled",
      "apiVersion": "2022-11-01",
      "properties": {
        "displayName": "Mass Export of Dynamics 365 Records to Excel",
        "description": "'The query detects user exporting a large amount of records from Dynamics 365 to Excel, significantly more records exported than any other recent activity by that user.'\n",
        "severity": "Medium",
        "enabled": true,
        "query": "let baseline_time = 7d;\nlet detection_time = 1d;\nDynamics365Activity\n| where TimeGenerated between(ago(baseline_time)..ago(detection_time-1d))\n| where OriginalObjectId contains 'ExportToExcel'\n| extend numQueryCount = todouble(QueryResults)\n| extend QueryCount = iif(QueryResults contains \",\", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)\n| extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))\n| summarize sum(QueryCount) by UserId\n| extend HistoricalBaseline = sum_QueryCount\n| join (Dynamics365Activity\n| where TimeGenerated > ago(detection_time)\n| where OriginalObjectId contains 'ExportToExcel'\n| extend numQueryCount = todouble(QueryResults)\n| extend QueryCount = iif(QueryResults contains \",\", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)\n| extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))\n| summarize sum(QueryCount) by UserId\n| extend CurrentExportRate = sum_QueryCount) on UserId\n| where CurrentExportRate > HistoricalBaseline\n| project UserId, HistoricalBaseline, CurrentExportRate\n| join kind=inner(Dynamics365Activity\n| where TimeGenerated > ago(detection_time)\n| where OriginalObjectId contains 'ExportToExcel'\n| extend numQueryCount = todouble(QueryResults)\n| extend QueryCount = iif(QueryResults contains \",\", todouble(countof(tostring(QueryResults), ',') + 1), numQueryCount)\n| extend QueryCount = iif(isnotempty(QueryCount), QueryCount, double(1))) on UserId\n| project TimeGenerated, UserId, QueryCount, UserAgent, OriginalObjectId, ClientIP, HistoricalBaseline, CurrentExportRate, CorrelationId, CrmOrganizationUniqueName\n| summarize QuerySizes = make_set(QueryCount), MostRecentQuery = max(TimeGenerated), IPs = make_set(ClientIP), UserAgents = make_set(UserAgent) by UserId, CrmOrganizationUniqueName, HistoricalBaseline, CurrentExportRate\n| extend timestamp = MostRecentQuery, AccountCustomEntity = UserId\n",
        "queryFrequency": "P1D",
        "queryPeriod": "P7D",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0,
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Collection"
        ],
        "techniques": [
          "T1530"
        ],
        "alertRuleTemplateName": "05eca115-c4b5-48e4-ba6e-07db57695be2",
        "customDetails": null,
        "entityMappings": [
          {
            "fieldMappings": [
              {
                "columnName": "AccountCustomEntity",
                "identifier": "FullName"
              }
            ],
            "entityType": "Account"
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Detections/Dynamics365Activity/MassExportOfDynamicstoExcel.yaml",
        "status": "Available",
        "templateVersion": "1.0.1"
      }
    }
  ]
}