Dataverse - Mass export of records to Excel

// 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

queryPeriod: 14d
requiredDataConnectors:
- connectorId: Dataverse
  dataTypes:
  - DataverseActivity
severity: Low
triggerOperator: gt
entityMappings:
- fieldMappings:
  - identifier: Name
    columnName: AccountName
  - identifier: UPNSuffix
    columnName: UPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: AppId
    columnName: CloudAppId
  - identifier: InstanceName
    columnName: InstanceUrl
  entityType: CloudApplication
- fieldMappings:
  - identifier: Address
    columnName: ClientIp
  entityType: IP
kind: Scheduled
triggerThreshold: 0
tactics:
- Exfiltration
name: Dataverse - Mass export of records to Excel
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Microsoft Business Applications/Analytic Rules/Dataverse - Mass export of records to Excel.yaml
relevantTechniques:
- T1567
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  
eventGroupingSettings:
  aggregationKind: AlertPerResult
id: 57000f0d-ff5d-4166-94b6-aa5fb62b16ec
status: Available
version: 3.2.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.
alertDetailsOverride:
  alertDescriptionFormat: User {{UserId}} exported {{{CurrentExportRate}} records using the ExportToExcel function in Dataverse.
  alertDisplayNameFormat: 'Dataverse - mass export to Excel activity in {{{InstanceUrl}} '
queryFrequency: 1h