arrowsix | DuckDB Snippets

Back to Code Snippets

arrowsix

@lldkfd_

‌

Top 50 Most Queried Domains from DNS A Records in PCAP AnalysisSQL

This query parses a PCAP file to identify the top 50 most requested domains from DNS A record queries. It filters DNS packets, extracts domains from the _ws.col.info field, normalizes them to lowercase, counts occurrences, and sorts the results by frequency in descending order.

Execute this SQL

SELECT LOWER(REGEXP_EXTRACT("_ws.col.info", ' A ([^ ]+)', 1)) AS domain, COUNT(*) AS total
 FROM read_pcap('dump.pcap')
 WHERE "frame.protocols" LIKE '%dns%' 
          AND "_ws.col.info" LIKE '% A %' 
GROUP BY domain 
ORDER BY total DESC 
LIMIT 50;
Copy code

arrowsix

Created 07/24/25