Multiple large queries made by user

Hunt Hypothesis

Users executing multiple large queries may be attempting to exfiltrate data or gather sensitive information, indicating potential reconnaissance or data extraction activities. SOC teams should proactively hunt for this behavior in Azure Sentinel to identify and mitigate potential data leakage or adversarial reconnaissance efforts.

KQL Query

let UI_apps = dynamic(['ASI_Portal','AzureMonitorLogsConnector','AppAnalytics']);
let threshold = 3;
LAQueryLogs
| where (ResponseRowCount == 10001 and RequestClientApp in(UI_apps)) or (ResponseRowCount > 10001 and RequestClientApp !in(UI_apps))
| summarize count() by AADEmail
| where count_ > threshold
| join kind=rightsemi (
LAQueryLogs
| where (ResponseRowCount == 10001 and RequestClientApp in(UI_apps)) or (ResponseRowCount > 10001 and RequestClientApp !in(UI_apps)))
on AADEmail
| extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail

Analytic Rule Definition

id: cd11d6a1-e2ad-47fa-9a9f-4c70b143d4fd
name: Multiple large queries made by user
description: |
  'This hunting query looks for users who are running multiple queries that return either a very large
  amount of data or the maximum amount allowed by the query method.'
requiredDataConnectors:
  - connectorId: AzureMonitor(Query Audit)
    dataTypes:
      - LAQueryLogs
tactics:
  - Exfiltration
relevantTechniques:
  - T1030
query: |

  let UI_apps = dynamic(['ASI_Portal','AzureMonitorLogsConnector','AppAnalytics']);
  let threshold = 3;
  LAQueryLogs
  | where (ResponseRowCount == 10001 and RequestClientApp in(UI_apps)) or (ResponseRowCount > 10001 and RequestClientApp !in(UI_apps))
  | summarize count() by AADEmail
  | where count_ > threshold
  | join kind=rightsemi (
  LAQueryLogs
  | where (ResponseRowCount == 10001 and RequestClientApp in(UI_apps)) or (ResponseRowCount > 10001 and RequestClientApp !in(UI_apps)))
  on AADEmail
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail
version: 1.0.0
metadata:
    source:
        kind: Community
    author:
        name: Pete Bryan
    support:
        tier: Microsoft
    categories:
        domains: [ "Security - Threat Protection" ]

MITRE ATT&CK Context

• Tactic: Exfiltration
• Technique: T1030 — T1030

References

• [Source Query](https://github.com/Azure/Azure-Sentinel/blob/main/Hunting Queries/LAQueryLogs/MultipleLargeQueriesByUser.yaml)

False Positive Guidance

• Scenario: A system administrator is running multiple large queries to generate end-of-day reports using Power BI or SQL Server Reporting Services (SSRS).
  Filter/Exclusion: Exclude users with administrative privileges or those known to perform regular reporting tasks.

• Scenario: A database administrator is performing a database backup using SQL Server Backup Utility or MySQL mysqldump, which generates large query outputs.
  Filter/Exclusion: Exclude queries executed during scheduled backup windows or by known backup tools.

• Scenario: A developer is testing a large data export using Python’s pandas or ETL tools like Talend, which can generate high-volume query results.
  Filter/Exclusion: Exclude queries originating from development environments or specific IP ranges used for testing.

• Scenario: A user is running a scheduled job via SQL Agent or Airflow that processes large datasets for analytics.
  Filter/Exclusion: Exclude queries associated with scheduled jobs or those running during off-peak hours.

• Scenario: A user is using PowerShell or Python scripts to query a database for data migration or ETL processes, which can result in large query outputs.
  Filter/Exclusion: Exclude queries executed from scripts or by users with specific script execution permissions.