RareDNSLookupWithDataTransfer

id: 06c52a66-fffe-4d3b-a05a-646ff65b7ec2
name: RareDNSLookupWithDataTransfer
description: |
  'This query helps identify rare DNS connections and resulting data transfer to/from the associated domain. It can help identify unexpected large data transfers to or from internal systems which may indicate data exfil or malicious tool download.'
description_detailed: |
  'This query is designed to help identify rare DNS connections and resulting data transfer to/from the associated domain.
  This can help identify unexpected large data transfers to or from internal systems which may indicate data exfil or malicious tool download.
  Feel free to add additional data sources to connect DNS results too various network data that has byte transfer information included.'
requiredDataConnectors:
  - connectorId: DNS
    dataTypes:
      - DnsEvents
  - connectorId: PaloAltoNetworks
    dataTypes:
      - CommonSecurityLog
  - connectorId: AzureMonitor(WireData)
    dataTypes:
      - WireData
  - connectorId: AzureMonitor(VMInsights)
    dataTypes:
      - VMConnection
tactics:
  - CommandAndControl
  - Exfiltration
relevantTechniques:
  - T1071
  - T1048
query: |

  let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  let lookback = totimespan((endtime-starttime)*7);
  let lookupThreshold = 21;
  let lookbackint = 7;
  let binvalue = 1;
  let bintime = make_timespan(binvalue,0);
  let avgCalc = starttime/1h;
  // Identify all domain lookups before start time and after lookback time
  let DomainLookups = DnsEvents
  | where TimeGenerated between(ago(lookback)..starttime)
  | where SubType == "LookupQuery"
  | where isnotempty(IPAddresses)
  | extend Domain = iff(countof(Name,'.') >= 2, strcat(split(Name,'.')[-2], '.',split(Name,'.')[-1]), Name)
  | summarize DomainCount = count() by Domain
  | project Domain, DailyAvgLookupCountOverLookback = DomainCount/lookbackint;
  // Common lookups should not include items that occurred more rarely over the lookback period.
  let CommonLookups = DomainLookups
  | where DailyAvgLookupCountOverLookback > lookupThreshold;
  // Get current lookups to compare against the lookback period
  let TodayLookups = DnsEvents
  | where TimeGenerated between(starttime..endtime)
  | where SubType == "LookupQuery"
  | where isnotempty(IPAddresses)
  | extend Domain = iff(countof(Name,'.') >= 2, strcat(split(Name,'.')[-2], '.',split(Name,'.')[-1]), Name)
  | summarize LookupStartTime = min(TimeGenerated), LookupEndTime = max(TimeGenerated), LookupCountToday = count() by ClientIP, Domain, IPAddresses
  | project LookupStartTime, LookupEndTime, ClientIP, LookupCountToday, Domain, IPAddresses;
  // Remove Common Lookups from lookback period from Todays lookups
  let UncommonLookupsToday = TodayLookups
  | join kind=leftanti (
  CommonLookups
  )
  on Domain;
  // Join back the Daily Average Lookup Count to add context to rarity over lookback period
  let RareLookups = UncommonLookupsToday | join kind= innerunique (
  DomainLookups
  ) on Domain
  | project LookupStartTime, LookupEndTime, ClientIP, Domain, IPAddresses, LookupCountToday, DailyAvgLookupCountOverLookback;
  let DNSIPBreakout = RareLookups
  | extend DnsIPAddress = iff(IPAddresses has ",", split(IPAddresses, ","), todynamic(IPAddresses))
  | mvexpand DnsIPAddress
  | extend DnsIPAddress = tostring(DnsIPAddress)
  | distinct LookupStartTime, LookupEndTime, ClientIP, Domain, DnsIPAddress, LookupCountToday, DailyAvgLookupCountOverLookback
  | extend IPCustomEntity = DnsIPAddress
  | where ipv4_is_private(DnsIPAddress) == false
  ;
  let DataMovement = ( union isfuzzy=true
  (CommonSecurityLog
  | where TimeGenerated between(starttime..endtime)
  | where DeviceVendor =="Palo Alto Networks" and Activity == "TRAFFIC"
  | where ipv4_is_private(DestinationIP) == false
  | project DataType = DeviceVendor, TimeGenerated, SourceIP , SourcePort , DestinationIP, DestinationPort, ReceivedBytes, SentBytes
  | sort by SourceIP asc, SourcePort asc,TimeGenerated asc, DestinationIP asc, DestinationPort asc
  | summarize sum(ReceivedBytes), sum(SentBytes), ConnectionCount = count() by DataType, SourceIP, SourcePort, DestinationIP, DestinationPort
  | extend IPCustomEntity = DestinationIP
  | sort by sum_SentBytes desc
  ),
  (VMConnection
  | where TimeGenerated between(starttime..endtime)
  | where Direction =~ "Outbound"
  | where ipv4_is_private(DestinationIp) == false
  | project DataType = Type, TimeGenerated, SourceIP = SourceIp , DestinationIP = DestinationIp, DestinationPort, ReceivedBytes = BytesReceived, SentBytes = BytesSent
  | summarize sum(ReceivedBytes), sum(SentBytes), ConnectionCount = count() by DataType, SourceIP, DestinationIP, DestinationPort
  | sort by sum_SentBytes desc
  | extend IPCustomEntity = DestinationIP
  )
  );
  DNSIPBreakout | join kind = leftouter (
  DataMovement
  ) on $left.DnsIPAddress == $right.DestinationIP and $left.ClientIP == $right.SourceIP
  | project-away DnsIPAddress, ClientIP
  // The below condition can be removed to see all DNS results.
  // This is used here as the goal of the query is to connect rare DNS lookups to a data type that can show byte transfers to that given DestinationIP
  | where isnotempty(DataType)
  | extend timestamp = LookupStartTime, IPCustomEntity = DestinationIP
version: 1.0.2
metadata:
    source:
        kind: Community
    author:
        name: Shain
    support:
        tier: Community
    categories:
        domains: [ "Security - Network" ]