Detect excessive NXDOMAIN DNS queries - Anomaly based ASIM DNS Solution

let threshold = 2.5;
let min_t = ago(14d);
let max_t = now();
let dt = 1d;
let summarizationexist = (
  union isfuzzy=true 
      (
      DNS_Summarized_Logs_ip_CL
      | where EventTime_t > ago(1d) 
      | project v = int(2)
      ),
      (
      print int(1) 
      | project v = print_0
      )
  | summarize maxv = max(v)
  | extend sumexist = (maxv > 1)
  );
let allData = union isfuzzy=true
      (
      (datatable(exists: int, sumexist: bool)[1, false]
      | join (summarizationexist) on sumexist)
      | join (
          _Im_Dns(responsecodename='NXDOMAIN', starttime=todatetime(min_t), endtime=max_t)
          | summarize Count=count() by SrcIpAddr, bin(TimeGenerated, 1h)
          | extend EventTime = TimeGenerated, Count = toint(Count), exists=int(1)
          )
          on exists
      | project-away exists, maxv, sum*
      ),
      (
      DNS_Summarized_Logs_ip_CL
      | where EventTime_t > min_t and EventResultDetails_s == 'NXDOMAIN'
      | summarize Count=toint(sum(count__d)) by SrcIpAddr=SrcIpAddr_s, bin(EventTime=EventTime_t, 1h)
      );
allData
| make-series EventCount=sum(Count) on EventTime from min_t to max_t step dt by SrcIpAddr
| extend (anomalies, score, baseline) = series_decompose_anomalies(EventCount, threshold, -1, 'linefit')
| mv-expand anomalies, score, baseline, EventTime, EventCount
| extend
  anomalies = toint(anomalies),
  score = toint(score),
  baseline = toint(baseline),
  EventTime = todatetime(EventTime),
  Total = tolong(EventCount)
| where EventTime >= ago(dt)
| where score >= threshold * 2
| join kind=inner(_Im_Dns(responsecodename='NXDOMAIN', starttime=ago(dt), endtime=max_t)
  | summarize DNSQueries = make_set(DnsQuery) by SrcIpAddr)
  on SrcIpAddr
| project-away SrcIpAddr1

tags:
- SchemaVersion: 0.1.6
  Schema: ASimDns
triggerOperator: gt
queryPeriod: 14d
queryFrequency: 1d
requiredDataConnectors: []
status: Available
id: 02f23312-1a33-4390-8b80-f7cd4df4dea0
relevantTechniques:
- T1568
- T1008
eventGroupingSettings:
  aggregationKind: AlertPerResult
triggerThreshold: 0
kind: Scheduled
entityMappings:
- fieldMappings:
  - identifier: Address
    columnName: SrcIpAddr
  entityType: IP
query: |
  let threshold = 2.5;
  let min_t = ago(14d);
  let max_t = now();
  let dt = 1d;
  let summarizationexist = (
    union isfuzzy=true 
        (
        DNS_Summarized_Logs_ip_CL
        | where EventTime_t > ago(1d) 
        | project v = int(2)
        ),
        (
        print int(1) 
        | project v = print_0
        )
    | summarize maxv = max(v)
    | extend sumexist = (maxv > 1)
    );
  let allData = union isfuzzy=true
        (
        (datatable(exists: int, sumexist: bool)[1, false]
        | join (summarizationexist) on sumexist)
        | join (
            _Im_Dns(responsecodename='NXDOMAIN', starttime=todatetime(min_t), endtime=max_t)
            | summarize Count=count() by SrcIpAddr, bin(TimeGenerated, 1h)
            | extend EventTime = TimeGenerated, Count = toint(Count), exists=int(1)
            )
            on exists
        | project-away exists, maxv, sum*
        ),
        (
        DNS_Summarized_Logs_ip_CL
        | where EventTime_t > min_t and EventResultDetails_s == 'NXDOMAIN'
        | summarize Count=toint(sum(count__d)) by SrcIpAddr=SrcIpAddr_s, bin(EventTime=EventTime_t, 1h)
        );
  allData
  | make-series EventCount=sum(Count) on EventTime from min_t to max_t step dt by SrcIpAddr
  | extend (anomalies, score, baseline) = series_decompose_anomalies(EventCount, threshold, -1, 'linefit')
  | mv-expand anomalies, score, baseline, EventTime, EventCount
  | extend
    anomalies = toint(anomalies),
    score = toint(score),
    baseline = toint(baseline),
    EventTime = todatetime(EventTime),
    Total = tolong(EventCount)
  | where EventTime >= ago(dt)
  | where score >= threshold * 2
  | join kind=inner(_Im_Dns(responsecodename='NXDOMAIN', starttime=ago(dt), endtime=max_t)
    | summarize DNSQueries = make_set(DnsQuery) by SrcIpAddr)
    on SrcIpAddr
  | project-away SrcIpAddr1  
customDetails:
  Total: Total
  DNSQueries: DNSQueries
  AnomalyScore: score
  baseline: baseline
name: Detect excessive NXDOMAIN DNS queries - Anomaly based (ASIM DNS Solution)
version: 1.0.2
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/DNS Essentials/Analytic Rules/ExcessiveNXDOMAINDNSQueriesAnomalyBased.yaml
tactics:
- CommandAndControl
alertDetailsOverride:
  alertDescriptionFormat: |-
    This client is generating excessive amount of DNS queries for non-existent domains. This can be an indication of possible C2 communications.

    Baseline for 'NXDOMAIN' error count for this client: '{{baseline}}'

    Current 'NXDOMAIN' error count for this client: '{{Total}}'

    DNS queries requested by the client include:

    '{{DNSQueries}}'    
  alertDisplayNameFormat: "[Anomaly] Excessive NXDOMAIN DNS Queries has been detected from client IP: '{{SrcIpAddr}}'"
severity: Medium
description: |
    'This rule makes use of the series decompose anomaly method to generate an alert when client requests excessive amount of DNS queries to non-existent domains. This helps in identifying possible C2 communications. It utilizes [ASIM](https://aka.ms/AboutASIM) normalization and is applied to any source that supports the ASIM DNS schema.'