Querying DNS data with Live Discover

One of the most common comments from EAP participants so far has been about the lack of visibility of DNS traffic data. The EAP refresh release coming in a week or two will help a lot with that, but in the meantime I thought I'd provide some examples of how you can already write queries using Live Discover if you have access to the Sophos Central Threat Analysis Center.

Between now and the final release we are planning to publish some pre-canned DNS queries in Live Discover that you can run as-is or use as a starting point to create tailored queries of your own.

Here are a few to get you started.

Please note that the data schema isn't yet fully finalized, so these queries aren't guaranteed to keep working, but they should work for now.

To use these, go to the Threat Analysis Center, switch to Designer Mode and hit “Create new query”. Make sure you specify that this is a Datalake query, then past the code into the query definition.

DNS - Query count by policy action with security blocks separated out:

Query code:

SELECT

    Action, src_ip, sum(Queries) as Queries

FROM (

    SELECT

        CASE
         when domain_risk='4' and log_subtype='Reject'

THEN 'Security block'

            ELSE log_subtype

END as Action,

    src_ip,
      COUNT(*) AS Queries

    FROM
       xgfw_data

    WHERE

        log_type='DNS'

    GROUP BY

        location_id, src_ip, log_subtype, domain_risk
   )

GROUP BY
    Action, src_ip

ORDER BY
    Action ASC

Example output:

DNS - Query count and size for a domain & its subdomains

This query uses a variable so that after you save it, you can use it to search for any domain.

Variable editor setup:

Query code:

SELECT

    src_ip,
    dns_qname,

    dns_qtype,
    log_subtype,

    response_code,
    COUNT(*) AS hits,

    avg(response_size) as rsize_avg

FROM
    xgfw_data

WHERE

    log_type='DNS' 
  AND

    LOWER(dns_qname) LIKE LOWER('%.$$Domain$$') or LOWER(dns_qname) LIKE LOWER('$$Domain$$')

GROUP BY

    src_ip,dns_qname, dns_qtype,
    response_code, log_subtype

ORDER BY

    log_subtype DESC, hits DESC

Example output:

DNS - Blocked domains:

Query code:

SELECT
CASE WHEN domain_risk='4'
THEN
'Security risk'
ELSE
'Policy'
END AS Reason,
dns_qname as "Requested domain",

src_ip as "Originating IP",
domain_category,
COUNT(*) AS Queries
FROM xgfw_data
WHERE
log_type='DNS' AND log_subtype='Reject'
GROUP BY
domain_risk,dns_qname, src_ip, domain_category
ORDER BY
reason DESC, Queries DESC

Example output:

DNS - Query count by policy action:

SELECT

log_subtype as Action,

    src_ip,
    COUNT(*) AS Queries

FROM
    xgfw_data

WHERE

    log_type='DNS'

GROUP BY

    location_id, src_ip, log_subtype

ORDER BY

Action ASC

Corrected more typos, updated to reflect removal of trailing '.' from domain names, and added 'Category' to the blocked requests query
[edited by: RichBaldry at 7:32 PM (GMT -8) on 13 Dec 2023]