Field-shaped case study

Migrating 800 detection rules across seven parallel ingest buses.

The question that kills more security-lakehouse projects than any benchmark or schema debate is this: how do I move 800 detection rules off Splunk SPL without missing a threat during the migration? This is the playbook I've watched work in production: tiered triage, LLM-assisted translation, 90-day regression testing, and a phased cutover that lets each business unit move at its own risk tolerance.

Reading time: roughly 19 minutes. Evidence tier: B (anonymized composite of one large federated migration plus my consulting work across twelve SOC teams totaling around 9,600 rules), with one Tier C note where I cite LLM-translation accuracy ranges and one Tier D forward-looking remark on the parallel-run cost calculus.

Take a typical brute-force authentication rule. In Splunk SPL it might look something like this:

index=windows sourcetype=WinEventLog:Security EventCode=4625
| stats count by src_ip, user
| where count > 10
| lookup threat_intel_ip src_ip OUTPUT threat_score
| where threat_score > 70
| eval severity=case(count>50, "critical", count>20, "high", 1=1, "medium")
| outputlookup failed_auth_summary

That one rule does seven things: search, aggregate, threshold filter, threat-intel enrichment, second threshold filter, dynamic severity scoring, and a write to a summary lookup. The SQL equivalent is rarely one statement and usually runs to three, a main aggregation query, a join against the threat-intel table, and an INSERT into the summary table, because there's no native lookup command, no outputlookup, and string-matching semantics differ in ways that matter for false-positive rates.

Multiply that translation work by 800 and the engineering estimate that comes back is usually 400 to 600 hours of manual translation. That estimate is the moment I see projects fold. "We can't do this in our timeline. We'll wait."

The estimate is correct, but the premise underneath it is wrong, because you should not be trying to translate all 800 in the first place.

The cost-saving move is to use an LLM for the first pass on every rule, then concentrate human review where the cost of a translation bug is highest. The shape that has worked for me is roughly: 40 hours of LLM work to draft SQL for all 800 rules, 80 hours of human engineering review across the 160 Tier 1 rules (about 30 minutes per rule), and 16 hours of spot-checking a 10% sample of Tier 2 and Tier 3. That's 136 hours total against the 400-hour fully-manual estimate.

Across the migration projects I've seen using GPT-4-class and Claude 3.5-class models, LLM translation accuracy lands roughly where you'd expect once you sort by rule complexity. Simple aggregations and filters translate at 92–95% accuracy on the first pass. Queries with subsearches, lookup enrichments, and outputlookup writes drop to 75–85%; the LLM gets you 80% of the way and the engineer finishes the rest. Highly custom macros and Splunk-specific functions (tstats, mvcombine) fall to 40–60% accuracy and usually need full manual rewrites. The ranges here are Tier C; they're my observation across roughly eight migration projects, not a published benchmark.

Read those ranges against the published text-to-SQL ceiling, because the BIRD-benchmark numbers are the outside view on what "the LLM got it right" actually means, and I work through them in LLM-assisted OCSF mapping. My own 92–95% figure for simple aggregations is plausibly real precisely because SPL aggregate-and-threshold rules are the easy end of that distribution; the 40–60% I see on tstats and custom macros is where my anecdote and the benchmark agree. Treat the published numbers as the ceiling and my ranges as one practitioner's slice underneath it.

Here's a concrete example of what an LLM gets right and what it gets wrong. The input rule:

index=edr sourcetype=process_telemetry
| search process_name=powershell.exe command_line=*DownloadString*
| stats count by host, user, command_line
| where count > 5

The LLM's first-pass SQL:

SELECT
    device_hostname,
    actor_user_name,
    process_cmd_line,
    COUNT(*) AS download_attempts
FROM ocsf_process_activity
WHERE event_date >= CURRENT_DATE - 1
  AND process_file_path LIKE '%powershell%'
  AND process_cmd_line LIKE '%DownloadString%'
GROUP BY device_hostname, actor_user_name, process_cmd_line
HAVING download_attempts > 5;

Human review catches three things that the LLM consistently misses. NULL handling: the SPL version implicitly drops events where command_line is empty; the SQL version will return them unless you add AND process_cmd_line IS NOT NULL. Time-window semantics: Splunk's earliest/latest is a rolling window from query time, while event_date >= CURRENT_DATE - 1 is calendar-day-boundary behavior, so the two are different rules. And field mapping: the LLM mapped host to device_hostname, which is correct against OCSF in most cases but wrong for sources where the upstream parser put the hostname in device.name instead.

Look at the shape of all three, because none of them throws an error: the NULL-handling miss, the calendar-day versus rolling-window mismatch, and the field mapped to the wrong OCSF attribute each produce valid SQL that runs clean and returns a plausible result set. This is the failure mode that matters, and it isn't the rule that fails to translate, because a rule that fails to translate announces itself and the engineer sees the error, fixes it, and moves on. The dangerous one is the silent error, where the LLM emits valid-but-semantically-wrong logic that passes a shallow eyeball check and ships looking correct, so that a rule which drops a third of its events to an unhandled NULL still produces alerts, just fewer of them, and "fewer alerts" reads like a quieter SOC rather than a coverage hole until the threat you needed it to catch slips through.

That is why the regression gate is load-bearing rather than optional. You cannot inspect your way out of a silent error; the SQL looks right by construction. The only check that surfaces it is replaying the rule against labeled events and comparing the output to a known-good baseline, which is exactly the 90-day regression in Phase 2. The field-mapping miss is the highest-frequency version of this I've seen across LLM-translated rules, and it's why I don't trust LLM translation without regression testing in front of it. See LLM-assisted OCSF mapping for the longer treatment of where LLMs help and where they hallucinate field-name correctness.

Regression testing validates the rule against threats that already happened. Parallel execution validates it against threats that haven't happened yet. Splunk continues running the Tier 1 ruleset and feeding alerts to the SOC; the new platform runs the SQL equivalents and feeds alerts to a validation queue that no analyst actions. A daily automated comparison surfaces any rule where the two systems disagree:

SELECT
    rule_name,
    COUNT(DISTINCT splunk_alert_id) AS splunk_alerts,
    COUNT(DISTINCT sql_alert_id) AS sql_alerts,
    COUNT(DISTINCT splunk_alert_id) - COUNT(DISTINCT sql_alert_id) AS delta
FROM alert_comparison
WHERE alert_date = CURRENT_DATE - 1
GROUP BY rule_name
HAVING delta != 0
ORDER BY ABS(delta) DESC;

Week one almost always surfaces a cluster of discrepancies. On the case I'm describing, twelve rules showed 5–15% fewer alerts on the SQL side, and the root cause was again time-zone handling, Splunk's UTC default versus the new engine's local time, which took one fix applied across all twelve rules. Weeks two through four came back with zero discrepancies, so those rules cleared for production.

The hard part of parallel execution isn't technical but financial, because each business unit is paying for two SIEM-grade environments simultaneously during their parallel window. On the federated case the seven business units settled into a wide range of parallel-window durations based on their own risk tolerance: roughly three weeks at the short end for a unit with compliance urgency that needed the Splunk contract gone, three to four months in the middle, and six months at the long end for a critical-infrastructure unit whose CISO refused to move faster.

The total double-spend across the seven units landed in the mid-six-figure range, which sounds scary in isolation, so the frame I've found persuasive is to compare it against the cost of the failure case: cutting over without parallel validation, missing a ransomware detection, and paying for incident response plus the credibility loss that ends the migration program entirely. The parallel-operation spend is insurance, and the premium is calibrated by the unit's own risk appetite rather than by a corporate timeline.

That last point is the central decision the federated coalition rests on, because each business unit controls its own parallel-window duration and forcing uniformity tends to collapse the coalition. The unit with the longest window is paying the most, but they're also the unit most likely to walk away from the migration entirely if rushed, and walking away takes the corporate-wide cost-savings case down with them.

The 800 rules in this case were not 800 corporate-mandated rules running uniformly across the business. They were a federation. Different units faced different threats, ran different log sources, and maintained different detection content. Forcing a shared ruleset would have collapsed the operational reality of the migration.

The structure that worked was a two-layer detection library. The first layer was a corporate-shared baseline of roughly 100 rules covering the threats every unit must detect (ransomware, lateral movement, privilege escalation), carried as a board-level compliance requirement and owned by the corporate SOC. The second was a per-unit overlay, where each business unit maintains its own detection content on top of the baseline, owned by that unit's security team, so that corporate doesn't touch the overlay and the unit doesn't touch the baseline.

Mathematically, the migration shrank from 800 unit-specific rules to 100 shared rules plus around 300 unit-specific rules across seven units (an average of 40-something per unit after Tier 3 deprecation). Corporate migrated the shared baseline once; each unit migrated its overlay independently. That's a usefully different workload from "migrate 800 rules end-to-end" and it's the structural reason the timeline closes in a year rather than two.

One unit in the federation ran Zeek with non-standard fields that did not map cleanly to OCSF. Corporate IT spent about a month in a joint working session with that unit's security engineers using LLM-assisted mapping to get to semantic correctness. The timeline impact was a two-week delay against plan. The trust impact, in the unit's own words, was substantial: the unit went into the migration worried that "corporate is going to flatten our custom sources," and came out describing the corporate IT team as collaborators rather than overlords. That's not a quantifiable outcome, but it's the outcome that makes the next federated initiative possible.

Across the 160 Tier 1 rules on the case I'm working from, the regression-testing numbers landed like this. Ransomware-family rules came in four short on the first pass, all four traced to a field-mapping bug and fixed to 100% parity; every family closed to full parity after the field-mapping and time-zone fixes.

Aggregate Tier 1: 1,524 baseline alerts, 1,501 first-pass alerts (98.5% parity), 1,524 after fixing field mapping and time-zone bugs. That last number is the artifact the CISO signed against. That the first pass was already at 98.5% is what made the project finishable in the timeline; the 2-percentage-point gap was the work that justified the human-review investment.

On the cost side, the project's headline savings landed in the low seven-figures annually after all seven units sunset their Splunk contracts. The parallel-operation insurance premium consumed roughly 15–20% of the first-year savings: real money, but not enough to change the business case. The analysis in the hidden cost of SIEM migration covers the broader cost framing including the categories that are easy to miss in early budgeting.