Enriching DNS, proxy and firewall logs with website categories

Questions enrichment lets you answer

Without categories, every report becomes a long list of hostnames that someone must read. With categories, the same logs answer business and security questions in one query.

Step 1: normalize every hostname

Most failed joins come from small formatting differences. Clean every name the same way before you look it up, and keep the original next to it for audit.

Step 3: a batch join in SQL

This example shows the warehouse pattern. Join distinct domains first, because a day of logs usually holds far fewer distinct names than events, then attach results back to the events.

/* 1. distinct normalized domains from yesterday's DNS logs */
WITH d AS (
  SELECT DISTINCT registered_domain
  FROM dns_logs
  WHERE event_date = CURRENT_DATE - 1
),
/* 2. look each one up once in the category table */
c AS (
  SELECT d.registered_domain, cat.primary_category,
         cat.country, cat.popularity_band
  FROM d
  LEFT JOIN url_categories cat
    ON cat.domain = d.registered_domain
)
/* 3. traffic per category, uncategorized kept visible */
SELECT COALESCE(c.primary_category, 'Uncategorized') AS category,
       COUNT(*) AS queries,
       COUNT(DISTINCT l.client_group) AS groups
FROM dns_logs l
JOIN c ON c.registered_domain = l.registered_domain
WHERE l.event_date = CURRENT_DATE - 1
GROUP BY 1
ORDER BY queries DESC;

Reports and alerts that pay off first

Start with a few reports that answer questions people already ask. These examples use DNS or proxy logs joined to the category table.

Worked example: the file storage question

Security specific uses are covered in domain categorization for cybersecurity, DNS filtering and security and enterprise network security.

Pitfalls that skew the numbers

Raw counts from network logs can mislead. These effects are the usual reasons a first dashboard looks wrong.

A four-week rollout plan

Teams that start small and prove value quickly tend to keep the project funded. This plan gets from a raw log table to useful reports within a month.