The join is one query: group hits by rule over the window, then left-join that result onto the rule list. Every rule appears at least once, with a count of zero if it never fired. Do not use an inner join. The dead rules are the ones that disappear, and they are half the point.
-- hits_by_rule: rule_id, hits, distinct_tools, distinct_arg_heads
-- rule_list: rule_id, file_line, intent
select
r.rule_id,
r.file_line,
coalesce(h.hits, 0) as hits,
coalesce(h.distinct_tools, 0) as distinct_tools,
coalesce(h.distinct_arg_heads, 0) as distinct_arg_heads
from rule_list r
left join hits_by_rule h
on h.rule_id = r.rule_id
order by hits asc;
Reading the output is triage, not judgment. The security literature on rule hit counts makes the point cleanly: a low or zero count can mean dead policy, a misplaced rule, a broken data source, or a control that no longer matches conditions, while a high count can mean a useful rule or noisy logic that needs refinement (NHI Mgmt Group). Hit counts are a usage metric, not a quality score. They tell you where to look.
| Pattern | Reading | Default action |
|---|
| Zero hits, stable policy version | Dead or shadowed | Move to deny in audit mode, watch one cycle |
| Zero hits, broad rule above always fires | Shadowed | Narrow the broad rule, then recheck |
| Low hits across many tools | Under-specified | Split by tool, then by argument head |
| High hits, one tool, many argument heads | Too broad | Add argument checks until one rule maps to one behavior |
| High hits, one tool, one behavior | Healthy | Leave it, record the intent comment |
Worked example. An allowlist of 60 rules produced 12,400 decisions in thirty days. The join shows 22 rules with zero hits, 9 above 500 hits each, and 29 in between. The dead rules are 37 percent of the file. The 9 hot rules account for 8,900 decisions, 72 percent of volume. That distribution is typical of a file built by unblocking, and it says where the work is: the top 9 rules are where a missing argument check turns into real risk.
A caveat on shadowed rules. A zero count can mean the rule is unreachable rather than unused. Firewall practice hits exactly this: a stateful rule with a pass action generates no alert log by default, so it never appears in hit counting unless logging is added to it (AWS Security Blog). Before deleting a zero-hit rule, check the ordering. If a generic rule earlier in the file always matches, the specific rule below it is invisible to the metrics. Narrow the generic rule rather than deleting the specific one.