Query looking for secrets

id: 2bf19f27-0466-4c16-a821-ce84e524476d
name: Query looking for secrets
description: |
  'This hunting query looks for queries that appear to be looking for secrets or passwords in tables.'
requiredDataConnectors:
  - connectorId: AzureMonitor(Query Audit)
    dataTypes:
      - LAQueryLogs
tactics:
  - Collection
relevantTechniques:
  - T1530
  - T1213
query: |

  // Extend this list with items to search for
  let keywords = dynamic(["password", "pwd", "creds", "credentials", "secret"]);
  // To exclude key phrases or tables to exclude add to these lists
  let table_exclusions = dynamic(["AuditLogs","SigninLogs", "LAQueryLogs", "SecurityEvent"]);
  let keyword_exclusion = dynamic(["reset user password", "change user password"]);
  LAQueryLogs
  | where RequestClientApp != 'Sentinel-General'
  | extend querytext_lower = tolower(QueryText)
  | where querytext_lower has_any(keywords)
  | project TimeGenerated, AADEmail, QueryText, RequestClientApp, RequestTarget, ResponseCode, ResponseRowCount, ResponseDurationMs, CorrelationId
  | extend timestamp = TimeGenerated, AccountCustomEntity = AADEmail
  | join kind=leftanti ( LAQueryLogs
  | where RequestClientApp != 'Sentinel-General'
  | extend querytext_lower = tolower(QueryText)
  | where QueryText has_any(table_exclusions) or querytext_lower has_any(keyword_exclusion))
  on CorrelationId
version: 1.0.0
metadata:
    source:
        kind: Community
    author:
        name: Pete Bryan
    support:
        tier: Microsoft
    categories:
        domains: [ "Security - Threat Protection" ]