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

severity: Low
relevantTechniques:
- T1567
requiredDataConnectors:
- dataTypes:
  - DataverseActivity
  connectorId: Dataverse
status: Available
triggerThreshold: 0
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.
triggerOperator: gt
alertDetailsOverride:
  alertDescriptionFormat: User {{UserId}} exported {{{CurrentExportRate}} records using the ExportToExcel function in Dataverse.
  alertDisplayNameFormat: 'Dataverse - mass export to Excel activity in {{{InstanceUrl}} '
name: Dataverse - Mass export of records to Excel
queryFrequency: 1h
version: 3.2.0
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
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
tactics:
- Exfiltration
queryPeriod: 14d
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
id: 57000f0d-ff5d-4166-94b6-aa5fb62b16ec

{
  "$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"
    }
  ]
}