本文为原文前 6,000 字符的节选翻译，完整内容请查看原文。

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.

挑选任何用于安全机器学习入门实验的公共数据集，看看它的列。通常只有一个表，每行代表一个事件，并带有一个标签。其中没有任何内容需要与其他内容进行匹配。这与真实分析的第一周截然相反，在真实分析中，“这是哪台机器做的？”这个问题所花费的时间比随后的每一个建模步骤都要长。因此，当人们问安全分析师的数据科学培训应该教什么时，我们的回答从一个不那么引人注目的地方开始：连接（joins）。具体来说，是那些键仅在一段时间窗口内有效的连接。

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:

IP 地址并不等同于主机。Zeek 的 dns.log 将客户端记录为 id.orig_h。最诱人的第一步是使用 dns.groupby(“id.orig_h”) 并统计每个源的 NXDOMAIN 响应数量，以此作为对域名生成算法（T1568.002）的粗略搜寻。在静态服务器的有线网络上，这很有效。但在租期为一小时的企业 Wi-Fi 网段上，它会悄悄地制造出虚假信息：一个 IP 的行混合了三台笔记本电脑，而“最吵闹的主机”是一个地址，而不是一台机器。在编写任何检测逻辑之前，你可以先衡量一下这种情况有多糟糕。Zeek 的 dhcp.log 记录了每次完成的交换，包含 assigned_addr、mac、host_name 和 lease_time。统计在分析窗口内每个地址被多少个 MAC 地址持有：

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.

如果这个数字大于零，那么该网段上每个基于 IP 的聚合数据都需要先进行修正。带时间维度的连接：解决方法是将每个 DNS 查询归因于查询发生时处于活动状态的租约。pandas.merge_asof 可以实现这一点：对于左侧表的每一行，它会在相同的分组（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:

有两个细节很重要。两个数据框都必须按其时间键排序，否则 merge_asof 会报错。而且过期检查是必不可少的：如果没有它，来自昨天离开网络的地址的查询会继承最后租用它的人。客户端续租时会发送一个新的 DHCPREQUEST，Zeek 将其记录为新行，因此活动主机始终能匹配到当前的租约。现在，基于稳定的键进行聚合。以下是同样的 NXDOMAIN 搜寻，针对每台机器根据其自身历史进行评分，使用中位数和 MAD（绝对中位差）基准而不是平均值，这样过去的一次突发流量就不会抬高阈值：

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.

clip(lower=1) 可以防止历史记录完全平坦（MAD 为零）的主机在第一次查找失败时产生无限大的分数。评分逻辑本身只有十行。其上方的归因过程才是分析变得准确的关键。如果你的团队使用 SQL 而不是 pandas，DuckDB 直接支持 ASOF JOIN，因此同样的逻辑可以在 Parquet 导出文件上运行，而无需将其全部加载到内存中。

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.

归因失效的情况：这种方法存在失效模式，分析师在信任输出结果前必须了解它们：传感器位置。如果客户端将 DNS 发送到内部 Windows 解析器，而你的 Zeek 传感器位于出口处，那么 id.orig_h 对每个查询来说都是解析器。没有任何连接能恢复出客户端信息。你需要 DNS 服务器日志或解析器客户端侧的传感器。MAC 地址随机化。iOS、Android 和 Windows 可以使用私有的、针对每个网络的 MAC 地址，有些还会轮换它。host_name（DHCP 选项 12）或 client_fqdn 是较弱的后备方案，因为它们由客户端设置且可能为空。IPv6。使用带有临时地址（RFC 8981）的 SLAAC 的主机从不接触 DHCP。此时归因需来自路由器邻居表或端点遥测。NAT 和 VPN 集中器。位于同一地址后的多个用户需要 VPN 或防火墙会话日志作为右侧表，以同样的方式连接，用会话开始和结束时间代替租约开始和持续时间。每种情况的模式都是一样的：找到记录谁持有标识符及其持续时间的记录，然后按时间连接。表会变，但技能不会。

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.

向培训提供商询问什么：当你比较针对安全分析师的数据科学课程时，要求查看一份实验手册并检查：是否有任何练习使用了两个或多个键无法对齐的日志源？是否有任何连接包含时间条件，还是所有连接都基于精确键？数据集是真实的带有真实字段名的网络或端点日志，还是预先扁平化的基准测试数据？课程在引入模型之前是否花费时间进行数据准备？你今天就可以自己练习 DHCP 到 DNS 的连接。Brad Duncan 的 malware-traffic-analysis.net 练习经常要求你识别受感染主机的 MAC 和主机名，对其中一个数据包捕获运行 zeek -C -r exercise.pcap LogAscii::use_json=T 会生成 dhcp.log 和 dns.log，你可以直接将其输入到上述代码中。我们特意教授这个顺序。《网络安全应用数据科学与人工智能》的第一天课程就是数据工程和特征准备，在任何监督模型出现之前，而《数据分析 SQL》课程则涵盖了跨扁平文件的连接。