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. Dataverse - Mass export of records to Excel

Dataverse - Mass export of records to Excel

Back
Id57000f0d-ff5d-4166-94b6-aa5fb62b16ec
RulenameDataverse - Mass export of records to Excel
DescriptionIdentifies users exporting a large amount of records from Dynamics 365 to Excel, significantly more records exported than any other recent activity by that user. Large exports from users with no recent activity are identified using a predefined threshold.
SeverityLow
TacticsExfiltration
TechniquesT1567
Required data connectorsDataverse
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Business Applications/Analytic Rules/Dataverse - Mass export of records to Excel.yaml
Version3.2.0
Arm template57000f0d-ff5d-4166-94b6-aa5fb62b16ec.json
Deploy To Azure
// Set a mass export threshold for users who have no historical activity.
let mass_export_threshold = 10000;
let query_lookback = 14d;
let query_frequency = 1h;
let export_activity = DataverseActivity
    | where Message == "ExportToExcel"
    | extend QueryCount = iif(QueryResults has ",", todouble(countof(tostring(QueryResults), ',') + 1), double(1));
let current_activity = export_activity
    | where TimeGenerated > ago(query_frequency)
    | extend RecordId = split(QueryResults, ",")
    | summarize
        FirstEvent = min(TimeGenerated),
        CurrentExportRate = sum(QueryCount),
        SampleRecordIds = make_set(RecordId, 1000)
        by UserId, InstanceUrl;
let historical_activity = export_activity
    | where TimeGenerated between(ago(query_lookback) .. ago(query_frequency))
    | summarize HistoricalBaseline = sum(QueryCount) by HistoricalUserId = UserId, InstanceUrl;
current_activity
| join kind=leftouter(historical_activity) on $left.UserId == $right.HistoricalUserId, InstanceUrl
| extend BaselineThreshold = iif(isnotnull(HistoricalBaseline), HistoricalBaseline, todouble(mass_export_threshold))
| where CurrentExportRate > BaselineThreshold
| join kind=inner(export_activity
    | where TimeGenerated > ago(query_frequency)
    | summarize EntityCount = sum(QueryCount) by UserId, ClientIp, InstanceUrl, EntityName
    | extend Details = bag_pack("EntityName", EntityName, "EntityCount", EntityCount)
    | summarize Details = make_set(Details, 100) by UserId, ClientIp, InstanceUrl)
    on UserId, InstanceUrl
| extend
    CloudAppId = int(32780),
    AccountName = tostring(split(UserId, '@')[0]),
    UPNSuffix = tostring(split(UserId, '@')[1])
| project
    FirstEvent,
    UserId,
    ClientIp,
    BaselineThreshold,
    CurrentExportRate,
    Details,
    SampleRecordIds,
    InstanceUrl,
    AccountName,
    UPNSuffix,
    CloudAppId

requiredDataConnectors:
- connectorId: Dataverse
  dataTypes:
  - DataverseActivity
alertDetailsOverride:
  alertDisplayNameFormat: 'Dataverse - mass export to Excel activity in {{{InstanceUrl}} '
  alertDescriptionFormat: User {{UserId}} exported {{{CurrentExportRate}} records using the ExportToExcel function in Dataverse.
query: |
  // Set a mass export threshold for users who have no historical activity.
  let mass_export_threshold = 10000;
  let query_lookback = 14d;
  let query_frequency = 1h;
  let export_activity = DataverseActivity
      | where Message == "ExportToExcel"
      | extend QueryCount = iif(QueryResults has ",", todouble(countof(tostring(QueryResults), ',') + 1), double(1));
  let current_activity = export_activity
      | where TimeGenerated > ago(query_frequency)
      | extend RecordId = split(QueryResults, ",")
      | summarize
          FirstEvent = min(TimeGenerated),
          CurrentExportRate = sum(QueryCount),
          SampleRecordIds = make_set(RecordId, 1000)
          by UserId, InstanceUrl;
  let historical_activity = export_activity
      | where TimeGenerated between(ago(query_lookback) .. ago(query_frequency))
      | summarize HistoricalBaseline = sum(QueryCount) by HistoricalUserId = UserId, InstanceUrl;
  current_activity
  | join kind=leftouter(historical_activity) on $left.UserId == $right.HistoricalUserId, InstanceUrl
  | extend BaselineThreshold = iif(isnotnull(HistoricalBaseline), HistoricalBaseline, todouble(mass_export_threshold))
  | where CurrentExportRate > BaselineThreshold
  | join kind=inner(export_activity
      | where TimeGenerated > ago(query_frequency)
      | summarize EntityCount = sum(QueryCount) by UserId, ClientIp, InstanceUrl, EntityName
      | extend Details = bag_pack("EntityName", EntityName, "EntityCount", EntityCount)
      | summarize Details = make_set(Details, 100) by UserId, ClientIp, InstanceUrl)
      on UserId, InstanceUrl
  | extend
      CloudAppId = int(32780),
      AccountName = tostring(split(UserId, '@')[0]),
      UPNSuffix = tostring(split(UserId, '@')[1])
  | project
      FirstEvent,
      UserId,
      ClientIp,
      BaselineThreshold,
      CurrentExportRate,
      Details,
      SampleRecordIds,
      InstanceUrl,
      AccountName,
      UPNSuffix,
      CloudAppId  
kind: Scheduled
severity: Low
eventGroupingSettings:
  aggregationKind: AlertPerResult
triggerThreshold: 0
queryFrequency: 1h
id: 57000f0d-ff5d-4166-94b6-aa5fb62b16ec
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Business Applications/Analytic Rules/Dataverse - Mass export of records to Excel.yaml
tactics:
- Exfiltration
triggerOperator: gt
description: Identifies users exporting a large amount of records from Dynamics 365 to Excel, significantly more records exported than any other recent activity by that user. Large exports from users with no recent activity are identified using a predefined threshold.
status: Available
queryPeriod: 14d
name: Dataverse - Mass export of records to Excel
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: Name
    columnName: AccountName
  - identifier: UPNSuffix
    columnName: UPNSuffix
- entityType: CloudApplication
  fieldMappings:
  - identifier: AppId
    columnName: CloudAppId
  - identifier: InstanceName
    columnName: InstanceUrl
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: ClientIp
relevantTechniques:
- T1567
version: 3.2.0

{
  "$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/57000f0d-ff5d-4166-94b6-aa5fb62b16ec')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/57000f0d-ff5d-4166-94b6-aa5fb62b16ec')]",
      "properties": {
        "alertDetailsOverride": {
          "alertDescriptionFormat": "User {{UserId}} exported {{{CurrentExportRate}} records using the ExportToExcel function in Dataverse.",
          "alertDisplayNameFormat": "Dataverse - mass export to Excel activity in {{{InstanceUrl}} "
        },
        "alertRuleTemplateName": "57000f0d-ff5d-4166-94b6-aa5fb62b16ec",
        "customDetails": null,
        "description": "Identifies users exporting a large amount of records from Dynamics 365 to Excel, significantly more records exported than any other recent activity by that user. Large exports from users with no recent activity are identified using a predefined threshold.",
        "displayName": "Dataverse - Mass export of records to Excel",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "AccountName",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "CloudApplication",
            "fieldMappings": [
              {
                "columnName": "CloudAppId",
                "identifier": "AppId"
              },
              {
                "columnName": "InstanceUrl",
                "identifier": "InstanceName"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "ClientIp",
                "identifier": "Address"
              }
            ]
          }
        ],
        "eventGroupingSettings": {
          "aggregationKind": "AlertPerResult"
        },
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Business Applications/Analytic Rules/Dataverse - Mass export of records to Excel.yaml",
        "query": "// Set a mass export threshold for users who have no historical activity.\nlet mass_export_threshold = 10000;\nlet query_lookback = 14d;\nlet query_frequency = 1h;\nlet export_activity = DataverseActivity\n    | where Message == \"ExportToExcel\"\n    | extend QueryCount = iif(QueryResults has \",\", todouble(countof(tostring(QueryResults), ',') + 1), double(1));\nlet current_activity = export_activity\n    | where TimeGenerated > ago(query_frequency)\n    | extend RecordId = split(QueryResults, \",\")\n    | summarize\n        FirstEvent = min(TimeGenerated),\n        CurrentExportRate = sum(QueryCount),\n        SampleRecordIds = make_set(RecordId, 1000)\n        by UserId, InstanceUrl;\nlet historical_activity = export_activity\n    | where TimeGenerated between(ago(query_lookback) .. ago(query_frequency))\n    | summarize HistoricalBaseline = sum(QueryCount) by HistoricalUserId = UserId, InstanceUrl;\ncurrent_activity\n| join kind=leftouter(historical_activity) on $left.UserId == $right.HistoricalUserId, InstanceUrl\n| extend BaselineThreshold = iif(isnotnull(HistoricalBaseline), HistoricalBaseline, todouble(mass_export_threshold))\n| where CurrentExportRate > BaselineThreshold\n| join kind=inner(export_activity\n    | where TimeGenerated > ago(query_frequency)\n    | summarize EntityCount = sum(QueryCount) by UserId, ClientIp, InstanceUrl, EntityName\n    | extend Details = bag_pack(\"EntityName\", EntityName, \"EntityCount\", EntityCount)\n    | summarize Details = make_set(Details, 100) by UserId, ClientIp, InstanceUrl)\n    on UserId, InstanceUrl\n| extend\n    CloudAppId = int(32780),\n    AccountName = tostring(split(UserId, '@')[0]),\n    UPNSuffix = tostring(split(UserId, '@')[1])\n| project\n    FirstEvent,\n    UserId,\n    ClientIp,\n    BaselineThreshold,\n    CurrentExportRate,\n    Details,\n    SampleRecordIds,\n    InstanceUrl,\n    AccountName,\n    UPNSuffix,\n    CloudAppId\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Low",
        "status": "Available",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Exfiltration"
        ],
        "techniques": [
          "T1567"
        ],
        "templateVersion": "3.2.0",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}