Data Science for Security Analysts: Training Starts at the Join

Pick any public dataset used in an intro machine learning for security lab and look at its columns. Usually there is one table, one row per event, and a label. Nothing in it needs to be matched against anything else. That is the opposite of the first week of real analysis, where the question “which machine did this?” takes longer than every modeling step that follows.

So when people ask what data science for security analysts training should teach, our answer starts somewhere unglamorous: joins. Specifically, joins where the key is only valid for a window of time.

The IP address is not the host

Zeek’s dns.log records the client as id.orig_h. The tempting first move is dns.groupby("id.orig_h") and a count of NXDOMAIN responses per source, as a crude hunt for domain generation algorithms (T1568.002). On a wired network of static servers that works. On a corporate Wi-Fi segment with one-hour leases it quietly produces fiction: one IP’s row blends three laptops, and the “noisiest host” is an address, not a machine.

You can measure how bad it is before you write any detection. Zeek’s dhcp.log records each completed exchange with assigned_addr, mac, host_name, and lease_time. Count how many MACs held each address over the analysis window:

import pandas as pd

def zeek(path):
    df = pd.read_json(path, lines=True)  # Zeek with LogAscii::use_json=T
    df["ts"] = pd.to_datetime(df["ts"], unit="s", utc=True)
    return df.sort_values("ts")

dhcp = (zeek("dhcp.log")
        .dropna(subset=["assigned_addr"])
        .rename(columns={"assigned_addr": "ip", "ts": "lease_ts"})
        [["lease_ts", "ip", "mac", "host_name", "lease_time"]])

reused = dhcp.groupby("ip")["mac"].nunique()
print(f"{(reused > 1).mean():.0%} of leased IPs had more than one owner")

If that number is above zero, every per-IP aggregate on that segment needs fixing first.

A join with a time dimension

The fix is to attribute each DNS query to whichever lease was active when the query happened. pandas.merge_asof does this: for each left row it finds the last right row with a key at or before it, within the same by group.

dns = zeek("dns.log").rename(columns={"id.orig_h": "ip"})

attributed = pd.merge_asof(
    dns, dhcp,
    left_on="ts", right_on="lease_ts",
    by="ip", direction="backward",
)

expiry = attributed["lease_ts"] + pd.to_timedelta(attributed["lease_time"], unit="s")
attributed.loc[attributed["ts"] > expiry, ["mac", "host_name"]] = None

print(attributed["mac"].isna().mean())  # share of queries with no owner

Two details matter. Both frames must be sorted on their time keys or merge_asof raises an error. And the expiry check is not optional: without it, a query from an address that left the network yesterday inherits whoever leased it last. A client renewing its lease sends a fresh DHCPREQUEST, which Zeek logs as a new row, so active hosts keep getting matched to current leases.

Now aggregate on the stable key. Here is the same NXDOMAIN hunt, scored per machine against that machine’s own history with a median and MAD baseline instead of a mean, so one past burst does not inflate the threshold:

attributed["nx"] = attributed["rcode_name"].eq("NXDOMAIN")
nx = (attributed.dropna(subset=["mac"])
      .groupby(["mac", pd.Grouper(key="ts", freq="1h")])["nx"].sum())

med = nx.groupby(level="mac").transform("median")
mad = nx.groupby(level="mac").transform(lambda s: (s - s.median()).abs().median())
score = (nx - med) / (1.4826 * mad.clip(lower=1))

print(score.sort_values(ascending=False).head(20))

The clip(lower=1) stops a host with a perfectly flat history (MAD of zero) from producing an infinite score on its first failed lookup. The scoring itself is ten lines. The attribution above it is where the analysis became correct.

If your team works in SQL rather than pandas, DuckDB supports ASOF JOIN directly, so the same logic runs over Parquet exports without loading them into memory.

Where the attribution breaks

This approach has failure modes, and an analyst should know them before trusting the output:

• Sensor placement. If clients send DNS to an internal Windows resolver and your Zeek sensor sits at the egress, id.orig_h is the resolver for every query. No join recovers the client. You need DNS server logs or a sensor on the client side of the resolver.
• MAC randomization. iOS, Android, and Windows can use a private, per-network MAC address, and some rotate it. host_name (DHCP option 12) or client_fqdn is a weaker fallback, since the client sets them and they can be blank.
• IPv6. Hosts using SLAAC with temporary addresses (RFC 8981) never touch DHCP. Attribution then comes from router neighbor tables or endpoint telemetry.
• NAT and VPN concentrators. Many users behind one address need the VPN or firewall session log as the right-hand table, joined the same way, with session start and end in place of lease start and duration.

The pattern is the same in each case: find the record that says who held an identifier and for how long, then join on time. The tables change. The skill does not.

What to ask a training provider

When you are comparing data science courses for security analysts, ask to see one lab notebook and check:

• Does any exercise use two or more log sources whose keys do not line up?
• Does any join have a time condition, or are all joins on exact keys?
• Are the datasets real network or endpoint logs with real field names, or a pre-flattened benchmark?
• Does the course spend time on data preparation before it introduces a model?

You can practice the DHCP-to-DNS join on your own today. Brad Duncan’s malware-traffic-analysis.net exercises regularly ask you to identify an infected host’s MAC and hostname, and running zeek -C -r exercise.pcap LogAscii::use_json=T over one of those packet captures produces dhcp.log and dns.log you can feed straight into the code above.

We teach this sequence on purpose. Day one of Applied Data Science & AI for Cybersecurity is data engineering and feature preparation, before any supervised model appears, and SQL for Data Analysis covers joins across flat files, Spark, Drill, and Elasticsearch. I chair the Apache Drill project, which exists because the data analysts need is almost never sitting in one tidy table. For the broader skills checklist, see data science skills every SOC analyst needs; for the first pandas operations, start with pandas for security data analysis.