Log Health Monitoring

Tip

tl;dr: SIEMs rarely monitor their own log ingestion beyond “source went fully silent.” I built a pipeline on Chronicle, BigQuery, BigQuery ML (ARIMA+), Apps Script, and Slack that also catches spikes, dips, and new unaligned sources, using per-source volume baselines instead of fixed thresholds. The design, deployment steps, and ongoing tuning playbook are all below.

I can’t count the number of times I’ve heard that log ingestion is a problem. I’ve felt the pain myself. You buy an expensive SIEM and feed logs in. Eventually you need to investigate something and the logs aren’t there, silent for weeks.

Modern SIEMs have plenty of nice features, but in my experience none of them tackle log ingestion seriously. Some offload it to external tooling or plugins where you build the monitoring logic yourself; others only cover the basic case, like detecting a fully silent source. What almost none do natively is the harder, more useful thing: alert you when a source’s volume deviates from its own baseline, before the gap becomes weeks wide.

Tired of the pain, I decided to own the problem. This post documents what I built and what I learned building it alongside AI.

Context

Before getting into the solution I must share some context on the environment. I’m a Google Chronicle (now SecOps) administrator, so Chronicle is the SIEM my monitoring targets. Keeping the rest of the stack on Google made sense for pricing and maintainability, so I picked five tools I’ll cover in detail later:

Chronicle SIEM for log ingestion,
BigQuery for storage and analytics,
BigQuery ML for the ARIMA+ model,
Apps Script for the automation layer, and
Slack for notifications.

You can swap any of these. The logic is what matters.

With this stack, I cover four failure modes that are pertinent to my environment and, I’d argue, to any team running a SIEM:

🔴 Full stop: a log source goes completely silent
🔵 New source: an unknown log type starts sending data to the SIEM without previous alignment
⬆️ Spike: a source sends significantly more data than expected
⬇️ Dip: a source sends significantly less data than expected, but hasn’t stopped entirely

Full stop and new source are cheap to detect and apply to every source. Spike and dip require statistical modeling and only work on sources with predictable volume, more on that in a moment. The naming mirrors BQML ARIMA+’s own vocabulary (has_spikes_and_dips): an anomaly above the confidence band is a spike; below the band, a dip.

Log sources don’t behave uniformly, and that’s worth stating up front. A firewall logs every packet, so it’s sending data every minute, 24/7. A honeypot may be silent for weeks and that’s completely normal. A cloud audit feed logs bursts of activity around business hours. Treating all of them the same way leads to either constant false positives or blind spots.

That asymmetry shapes the whole design. And it makes log health monitoring an ongoing process, not a one-time deployment. Log sources get added, deprecated, and change behavior over time. The environment is a living organism, so the solution has to account for that, which is why I approached this as a PDCA loop: Plan, Do, Check, Act, repeat.

This post follows two PDCA loops. The first is the deployment cycle: assess your environment, build and deploy the stack, then validate it works and make early adjustments. The second is the operational cycle that runs indefinitely once the system is live: daily monitoring and investigation, threshold tuning when alert quality drifts, and a quarterly reassessment that re-profiles the full estate and updates the configuration. The next two sections, Research and Design, cover architectural decisions. The later sections cover these two loops.

Research

Before writing a line of SQL, I spent time with Claude (Opus 4.7) in research mode, treating it pretty much like a coworker, to map the problem space and discuss my ideas. I asked it to interview me before writing anything, because I’ve learned that skipping that step leads to solving the wrong problem.

It asked three things: build versus buy preference, where the logic should live and run, and what “log source” meant in my context, whether the log type in Chronicle or the physical appliance behind it (a PAN_FIREWALL tag versus each firewall’s hostname). Those were small questions with large scope implications. The scope answer forced me to commit early: I’d monitor at the log type level, which Chronicle exposes directly through its ingestion metrics. Appliance-level correlation would come later, if at all.

I asked for three options: one using Chronicle’s native features if any existed, one with a new tool to buy, and one built from scratch with what I already had. I also asked it to follow a standard RFC structure and end with a clear recommendation and reasoning.

Once the interview was done, Claude wrote the RFC and I read it top to bottom. By the time I reached the recommendation section, I already had my own preference and it matched Claude’s. A nice surprise.

The winning option was to build from scratch on BigQuery. What surprised me most wasn’t the match itself, but the recommendation: I didn’t know BigQuery had ML models, let alone that they were easy to use. The capability I needed was already sitting in my stack; I just hadn’t looked. ARIMA+ for forecasting expected log volume, a heartbeat check for sources that don’t fit the model, Apps Script and Slack for notifications. All tools in my current stack, green light to proceed.

Note

ARIMA+ (AutoRegressive Integrated Moving Average) is a time series forecasting model. You feed it historical data (in this case, hourly log counts per source) and it learns the normal pattern: how much a source typically sends on a Tuesday at 3 AM, how it behaves on weekends. At detection time, it scores incoming data against that learned baseline and flags anything that falls outside the expected range. Think of it as a statistical anomaly detector that adapts to each source’s own rhythm, rather than a fixed threshold someone set manually.

Design

The design splits log sources into two monitoring tracks based on how they behave:

Heartbeat: for bursty or sparse sources where the ARIMA+ forecast band would be too wide to be meaningful. It uses a simpler check: if I haven’t heard from this source in N hours, alert.
ARIMA+: for sources with steady, forecastable volume. BigQuery ML trains a model on 90 days of hourly data and generates a confidence band. Anything outside the band is a candidate anomaly. Spikes and dips only escalate to a real alert after meeting persistence or magnitude thresholds.

Which track a source gets depends on two metrics from 90 days of historical data: coverage (what percentage of hourly buckets had at least one event) and CV (coefficient of variation: standard deviation divided by mean, which measures volume stability). A source with ≥80% coverage and CV ≤1.0 qualifies for ARIMA. Everything else gets heartbeat.

Together, Claude and I designed the pipeline shown in the next diagram, implementing this monitoring scheme.

graph TD
    src((log source))
    siem[SIEM]
    src -->|logs| siem

    subgraph BQ[BigQuery]
        metrics[(Metrics)]
        config[(Config)]
        model{Model}
        candidates[(Candidates)]
        anomalies[(Anomalies)]
        sq[Scheduled queries]

        metrics -->|train| model
        metrics --> sq
        model --> sq
        config --> sq
        sq -->|SPIKE/DIP| candidates
        sq -->|FULL_STOP/NEW_SOURCE| anomalies
        candidates --> sq
        sq -->|promote| anomalies
    end

    siem -->|metrics| metrics

    code[Serverless]
    notif((Notification))

    anomalies --> code
    code --> notif

The pipeline has five moving parts:

Chronicle emits hourly ingestion metrics into BigQuery.
BigQuery scheduled queries run the detection logic and write to two tables: ARIMA findings (SPIKE/DIP) go to anomaly_candidates, while heartbeat and new-source findings go directly to anomalies. The same scheduled queries also read anomaly_candidates and promote qualifying entries to anomalies.
Apps Script polls anomalies every 10 minutes and posts to Slack.
A separate monthly job retrains the model.
A weekly job checks the pipeline itself.

The split between candidates and anomalies is intentional and worth understanding. ARIMA findings don’t go directly to anomalies; they land in anomaly_candidates first. The hourly scheduled query then checks those candidates and promotes them to anomalies only when they meet a persistence or magnitude bar: at least four detections for the same source in 24 hours, or a deviation above 200%. Below that, they stay silent.

This matters because treating model outputs as alerts directly is one of the most common failure modes in ML-based detection. The model doesn’t know what’s actionable; it just knows what’s statistically unusual. In a dynamic environment, statistically unusual happens constantly: a new service rolls out, a batch job runs on a new schedule, a team starts using a tool more. None of those warrant an alert. The promotion layer is where the signal gets filtered from the noise. Without it, alert fatigue kills the system faster than any configuration problem.

The promotion layer is the architectural centerpiece of the whole pipeline, and it’s what makes an imperfect model operationally useful. ARIMA+ has real limitations on this data shape — see Deployment. No amount of hyperparameter tuning will fully fix them, and BQML’s auto-selection exposes limited knobs anyway. Rather than chase a perfect model, this design lets ARIMA+ do what it’s good at (finding statistical outliers) and hands off to explicit business rules (persistence ≥ 4 in 24h, magnitude ≥ 200%, 12h cooldown) to decide what deserves an alert. It’s a modest architectural investment with an outsized payoff: it turns “the model is imperfect” from a problem into a design principle.

There’s one more filter before candidates even reach the promotion layer. The SPIKE/DIP detection query drops micro-deviations, which means a breach only counts if it exceeds the breached bound (upper_bound for spikes, lower_bound for dips) by at least 10 events or at least 5% of that bound. Without this, a source doing 10 million events an hour would trigger every time it drifted a few events past expected, statistically anomalous but operationally noise. The 10-events / 5% pair is deliberate: 10 events is small enough that a quiet source still gets scrutinized, and 5% is loose enough that a busy source’s rounding wobble doesn’t pollute the candidate table.

Note

Once a (source, anomaly_type) pair promotes to anomalies, the promotion query won’t re-alert the same pair for 12 hours — I call it cooldown. This limits a sustained breach at ~2 alerts per day per source, matching how humans triage: one alert, act, look again in a few hours. The persistence rule (≥4 candidates / 24h) and magnitude rule (severity ≥ 200%) still evaluate every hour, but they can’t punch through the cooldown.

The 12h value came from tuning. An earlier version used 24h and produced a nasty failure mode: after a sustained spike fired its first alert, no follow-up alerts reached Slack for 16 hours even though candidates kept piling up. 12h re-fires alerts soon enough to notice without spamming Slack.

For heartbeat sources, the silence threshold is set per source from its own history: take the 99th-percentile gap between events over the last 90 days (p99_gap), multiply by 1.5, round up, and keep the result between 6h and 336h. In practice, most sources land in one of five tiers:

Tier	`max_silence_hours`	Profile	Rationale
CONTINUOUS	3h	Firewalls, proxies, SSO, EDR	A 3h gap is always anomalous
FREQUENT	6h	Bursty audit and access logs	Occasional quiet pockets are normal
DAILY	48h	Weekend-tolerant streams, on-prem infra	Covers Fri PM → Mon AM
BURSTY	72h	Alert and finding feeds	3 days without a finding = feed is broken
SPARSE	336h	IAM analyses, honeypots, compliance scans	Expected to be rarely active

Treat the tiers as direction, not hard rules. It’s perfectly fine to set a source to 168h if that’s what the data says. The tiers are just a mental model to keep the fleet consistent and avoid ad hoc choices. The only hard limit is 336h: above that, use suppressions for planned downtime or excluded_log_types for permanent exclusions.

Note

The hourly detection query scans ingestion_metrics for the FULL_STOP heartbeat check, and to keep that scan cheap it only looks back 14 days — 336h. Any source silent beyond that returns latest_event = NULL and fires FULL_STOP regardless of max_silence_hours; the scan window short-circuits the threshold check. Scanning further back is possible but scales cost linearly, and 14 days is the pragmatic tradeoff. Sources that legitimately need >336h tolerance belong in suppressions (temporary) or excluded_log_types (permanent) instead. Both cost nothing to evaluate.

With all architectural decisions in mind, it’s time to start implementing the log health monitoring.

Assessment

Start by verifying the metrics are there with the query below. It should return a near real-time timestamp and a row count for the last 24 hours. If so, Chronicle is feeding ingestion metrics into BigQuery and you’re good to go. If not, you need to configure that export first.

SELECT MAX(start_time) AS latest_event, COUNT(*) AS rows_last_24h
FROM `chronicle-google.datalake.ingestion_metrics`
WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR);

Note

Chronicle exports data to BigQuery through two mechanisms. Advanced BigQuery Export is a fully managed pipeline available on Enterprise Plus; Google provisions the BigQuery project and covers ingestion and storage costs; you only pay for queries. Bring Your Own BigQuery (BYOBQ) is the self-managed alternative for other subscription tiers, configured under SIEM Settings → Data Export. Check the official documentation for your tier. Either way, the datalake.ingestion_metrics table and schema are the same.

Snippets in this post use two project placeholders you should replace with yours: chronicle-google for the project holding ingestion_metrics (Google-owned under Advanced BQ Export; your own under BYOBQ) and chronicle-self for the project holding the monitoring stack (log_health dataset, ARIMA+ model, scheduled queries). Advanced BQ Export users will have two distinct projects; BYOBQ users can substitute the same name in both places or keep them separate for clean isolation.

Next, run the query below to profile all log sources. It calculates coverage and CV over 90 days of hourly data and suggests a monitoring track using the ARIMA+ qualification introduced in the Design section — coverage ≥ 80% AND CV ≤ 1.0. Sources that miss either threshold go to heartbeat. For bursty or sparse sources the confidence band would be too wide to catch real anomalies, and brand-new sources won’t qualify until they’ve accumulated enough history.

WITH hourly AS (
  SELECT log_type AS source_key,
         TIMESTAMP_TRUNC(start_time, HOUR) AS hour_bucket,
         SUM(event_count) AS log_count
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  GROUP BY source_key, hour_bucket
),
prof AS (
  SELECT source_key,
         COUNT(*) AS hours_with_data,
         ROUND(COUNT(*)/2160*100, 1) AS pct_covered,
         ROUND(AVG(log_count)) AS avg_per_hour,
         ROUND(STDDEV(log_count)) AS stddev_per_hour,
         ROUND(SAFE_DIVIDE(STDDEV(log_count), AVG(log_count)), 2) AS cv,
         TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(hour_bucket), HOUR) AS hours_since_last
  FROM hourly
  GROUP BY source_key
)
SELECT *,
  CASE WHEN pct_covered < 80 OR cv IS NULL OR cv > 1.0 THEN 'HEARTBEAT'
       ELSE 'ARIMA' END AS suggested_track,
  CASE WHEN pct_covered < 80 OR cv IS NULL OR cv > 1.0 THEN NULL
       WHEN cv <= 0.5 THEN 0.99
       WHEN cv <= 0.8 THEN 0.995
       ELSE 0.998 END AS suggested_threshold
FROM prof
ORDER BY avg_per_hour DESC;

This query returns one row per log source. The most important columns are source_key, suggested_track (ARIMA or HEARTBEAT), and suggested_threshold. Save the full result as estate.csv — you’ll need it in the next step.

Next, run the signal profile query below. Where the estate query answered “does this source qualify for ARIMA?”, this one answers “what should ARIMA actually be able to learn from it?”, vetting whether each source has meaningful daily and weekly patterns before you commit to a track. It’s purely informational and doesn’t feed derive-seeds.py. But it shapes your expectations while reviewing plan.csv in the next step and gives you a baseline to cross-check the trained model against.

WITH hourly AS (
  SELECT log_type AS source_key,
         TIMESTAMP_TRUNC(start_time, HOUR) AS hour_bucket,
         SUM(event_count) AS log_count
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  GROUP BY source_key, hour_bucket
),
lagged AS (
  SELECT source_key, hour_bucket, log_count,
         IF(EXTRACT(DAYOFWEEK FROM hour_bucket) IN (1, 7), 'weekend', 'weekday') AS daytype,
         LAG(log_count, 24)  OVER (PARTITION BY source_key ORDER BY hour_bucket) AS lag_24,
         LAG(log_count, 168) OVER (PARTITION BY source_key ORDER BY hour_bucket) AS lag_168
  FROM hourly
),
day_medians AS (
  SELECT source_key,
         MAX(IF(daytype = 'weekday', med, NULL)) AS wd_med,
         MAX(IF(daytype = 'weekend', med, NULL)) AS we_med
  FROM (
    SELECT source_key, daytype,
           APPROX_QUANTILES(log_count, 100)[OFFSET(50)] AS med
    FROM lagged
    GROUP BY source_key, daytype
  )
  GROUP BY source_key
),
signals AS (
  SELECT source_key,
         ROUND(CORR(log_count, lag_24), 2)  AS acf_24h,
         ROUND(CORR(log_count, lag_168), 2) AS acf_168h,
         ROUND(SAFE_DIVIDE(
           APPROX_QUANTILES(log_count, 100)[OFFSET(99)],
           NULLIF(APPROX_QUANTILES(log_count, 100)[OFFSET(50)], 0)
         ), 1) AS p99_p50_ratio
  FROM lagged
  GROUP BY source_key
)
SELECT s.source_key,
       ROUND(SAFE_DIVIDE(d.wd_med, NULLIF(d.we_med, 0)), 2) AS wd_we_ratio,
       s.acf_24h,
       s.acf_168h,
       s.p99_p50_ratio
FROM signals s
JOIN day_medians d USING (source_key)
ORDER BY s.acf_168h DESC;

Save as signals.csv. Read source-by-source:

wd_we_ratio: ratio of median weekday-hour volume to weekend-hour volume. >1.3 or <0.7 = human-driven source with weekly rhythm. ≈1.0 = automation-dominated, no weekly pattern.
acf_24h: autocorrelation at lag 24 hours. >0.5 expected on any live source. If weak, the source is bursty and ARIMA will fit poorly regardless of coverage.
acf_168h: autocorrelation at lag 168 hours (weekly). >0.5 = weekly seasonality present in the raw data. Keep this number; after training, cross-check that the model actually picked up WEEKLY on these sources. Often it won’t; see the Deployment section for why.
p99_p50_ratio: heavy-tail signal. >20 = spiky source; confidence bands will be wide even at high thresholds.

None of this changes the ARIMA-vs-HEARTBEAT gate. Coverage and CV are still the split. Signal profile just sets expectations about what the trained model can and can’t learn.

Next, run the query below to measure silence gaps, specifically how long each log source typically goes quiet between events. This is what drives the heartbeat threshold.

WITH hourly AS (
  SELECT log_type AS source_key, TIMESTAMP_TRUNC(start_time, HOUR) AS hour_bucket
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  GROUP BY source_key, hour_bucket
),
gaps AS (
  SELECT source_key,
         TIMESTAMP_DIFF(
           hour_bucket,
           LAG(hour_bucket) OVER (PARTITION BY source_key ORDER BY hour_bucket),
           HOUR
         ) AS gap_hours
  FROM hourly
)
SELECT source_key,
       COUNT(*) AS n_gaps,
       ROUND(APPROX_QUANTILES(gap_hours, 100)[OFFSET(50)]) AS p50_gap,
       ROUND(APPROX_QUANTILES(gap_hours, 100)[OFFSET(95)]) AS p95_gap,
       ROUND(APPROX_QUANTILES(gap_hours, 100)[OFFSET(99)]) AS p99_gap,
       MAX(gap_hours) AS max_gap
FROM gaps
WHERE gap_hours IS NOT NULL AND gap_hours > 0
GROUP BY source_key
ORDER BY p99_gap DESC;

Save it as gaps.csv. Check that the source_key sets match between the two CSVs; the gap query is a subset of the estate query, so the count should be equal or smaller. Chronicle’s metrics include internal sources like UDM, UNSPECIFIED_LOG_TYPE, and a set of LT_* types (LT_0 through LT_20). They’ll show up in both queries. No action needed here as derive-seeds.py excludes them automatically via a built-in list. If your environment has additional garbage sources, you can extend that list in the script.

With both CSV files in hand, run derive-seeds.py (source in References) to generate a monitoring plan:

python3 derive-seeds.py plan \
  estate.csv \
  gaps.csv > plan.csv

Open plan.csv in your editor. It has one row per log source with suggested_track, arima_threshold, and max_silence_hours. Then, for each row, decide: keep the suggested track or flip it, adjust the threshold if you know the source is noisy, adjust max_silence_hours for sources you know are legitimately sparse, and add a note for anything you’re excluding.

Personally, I don’t touch the ARIMA+ threshold at this stage. Let it run a few days and tune from real alerts. That way, you can confirm what normal looks like before changing anything. On the exclusion side, use EXCLUDE with a note rather than deleting rows. Future you (or a teammate) will want to know why the source was set aside, and a deleted row can’t tell you. The exception is truly dead sources from past deployments: delete those, or you’ll get NEW_SOURCE alerts for each one on day one.

For sources with p99_gap > 168h, ping the owning team before deciding. A broken feed should be fixed in Chronicle or deprecated there, not silenced in the monitoring layer. Intentionally sparse feeds (honeypots, compliance scans) should be kept with an oversized max_silence_hours to avoid false positives.

Tip

Take a moment to carefully review the monitoring plan. Query log sources in the SIEM, verify the data is correct, and confirm silence thresholds reflect reality. The goal of this phase is to get the environment clean, not to create synthetic metrics. Exclusions are a last resort for Chronicle’s internal garbage sources or genuinely intentional gaps. Threshold bumps should only happen after confirming the log source is behaving normally. If you finish the assessment with a long exclusion list or too many loose thresholds, something is wrong upstream. Fix it there.

Once the plan is reviewed, emit the seed SQL. Use the --project and --dataset flags to match your environment:

python3 derive-seeds.py emit plan.csv \
  --project chronicle-self \
  --dataset log_health > seeds.sql

At this point, the assessment is done and all necessary information is collected. It’s time to deploy.

Deployment

Before starting, it’s nice to give an overview of what will be created. The tables shown in the next diagram make up the log_health dataset. source_config is the configuration backbone; every detection query joins against it. anomaly_candidates and anomalies are the two-stage output layer. The remaining tables are either inputs (ingestion_hourly), controls (suppressions, excluded_log_types), or pipeline health outputs (pipeline_health_status, unspecified_growth_status). The dataset also holds arima_ingestion, a BigQuery ML model trained from ingestion_hourly; not a table, so not shown here.

erDiagram
    direction LR
    source_config {
        STRING source_key PK
        BOOL enabled
        STRING detection_method
        FLOAT64 anomaly_prob_threshold
        INT64 max_silence_hours
    }
    suppressions {
        STRING source_key FK
        STRING reason
        TIMESTAMP until
    }
    excluded_log_types {
        STRING log_type PK
        STRING reason
    }
    ingestion_hourly {
        STRING source_key
        TIMESTAMP hour_bucket
        INT64 log_count
    }
    anomaly_candidates {
        TIMESTAMP detected_at
        STRING source_key FK
        STRING anomaly_type
        TIMESTAMP hour_bucket
        FLOAT64 log_count
        FLOAT64 lower_bound
        FLOAT64 upper_bound
        FLOAT64 anomaly_probability
        FLOAT64 severity_pct
        BOOL promoted
    }
    anomalies {
        TIMESTAMP detected_at
        STRING source_key FK
        STRING anomaly_type
        TIMESTAMP hour_bucket
        FLOAT64 log_count
        FLOAT64 lower_bound
        FLOAT64 upper_bound
        FLOAT64 anomaly_probability
        BOOL sent_to_slack
        FLOAT64 actual_value
        FLOAT64 expected_value
        FLOAT64 delta_value
        FLOAT64 severity_pct
        STRING value_unit
        TIMESTAMP breach_first_at
        TIMESTAMP breach_last_at
        INT64 breach_hits
        INT64 breach_overflow_evts
        FLOAT64 breach_overflow_pct
    }
    pipeline_health_status {
        STRING statement_type
        TIMESTAMP last_run
        STRING last_state
        TIMESTAMP checked_at
    }
    unspecified_growth_status {
        INT64 current_week
        INT64 prior_week
        FLOAT64 growth_pct
        TIMESTAMP checked_at
    }

    source_config ||--o{ suppressions : "mutes"
    source_config ||--o{ anomaly_candidates : "detects"
    source_config ||--o{ anomalies : "alerts"
    anomaly_candidates }o--o{ anomalies : "promotes to"

Start the deployment by initializing the monitoring dataset on BigQuery. The next query creates the log_health dataset. Change the location if needed:

CREATE SCHEMA IF NOT EXISTS `chronicle-self.log_health` OPTIONS(location = 'US');

Now, create the tables.

-- source_config: routing table that drives the whole pipeline
CREATE OR REPLACE TABLE `chronicle-self.log_health.source_config` (
  source_key STRING NOT NULL,
  enabled BOOL DEFAULT TRUE,
  detection_method STRING DEFAULT 'ARIMA',     -- 'ARIMA' | 'HEARTBEAT'
  anomaly_prob_threshold FLOAT64 DEFAULT 0.99,
  max_silence_hours INT64 DEFAULT 3
);

-- suppressions: TEMPORARY mute for a real source (planned maintenance, holiday)
CREATE OR REPLACE TABLE `chronicle-self.log_health.suppressions` (
  source_key STRING,
  reason STRING,
  until TIMESTAMP
);

-- excluded_log_types: PERMANENT exclusion list for junk log_types
CREATE OR REPLACE TABLE `chronicle-self.log_health.excluded_log_types` (
  log_type STRING NOT NULL,
  reason STRING
);

-- anomalies: append-only history of every alert that goes to Slack.
-- Detection queries populate the normalized columns; raw log_count/
-- lower_bound/upper_bound are kept for ad-hoc SQL.
CREATE OR REPLACE TABLE `chronicle-self.log_health.anomalies` (
  detected_at TIMESTAMP,
  source_key STRING,
  anomaly_type STRING,             -- SPIKE | DIP | FULL_STOP | NEW_SOURCE
  hour_bucket TIMESTAMP,
  log_count FLOAT64,
  lower_bound FLOAT64,
  upper_bound FLOAT64,
  anomaly_probability FLOAT64,
  sent_to_slack BOOL DEFAULT FALSE,
  -- Unit-aware actual/expected/delta. SPIKE/DIP: log_count vs. bound, value_unit='evt'.
  -- FULL_STOP: hours_silent vs. max_silence_hours, value_unit='h'.
  -- NEW_SOURCE: NULL (no baseline).
  actual_value FLOAT64,
  expected_value FLOAT64,
  delta_value FLOAT64,
  severity_pct FLOAT64,
  value_unit STRING,
  -- Cluster summary: SPIKE/DIP only (NULL elsewhere). breach_overflow_pct
  -- is weighted: SUM(|log_count - bound|) / SUM(bound) * 100 across the cluster.
  breach_first_at TIMESTAMP,
  breach_last_at TIMESTAMP,
  breach_hits INT64,
  breach_overflow_evts INT64,
  breach_overflow_pct FLOAT64
);

-- anomaly_candidates: raw SPIKE/DIP detections; a companion promotion query lifts a subset to anomalies
CREATE OR REPLACE TABLE `chronicle-self.log_health.anomaly_candidates` (
  detected_at TIMESTAMP,
  source_key STRING,
  anomaly_type STRING,             -- only SPIKE | DIP land here
  hour_bucket TIMESTAMP,
  log_count FLOAT64,
  lower_bound FLOAT64,
  upper_bound FLOAT64,
  anomaly_probability FLOAT64,
  severity_pct FLOAT64,
  promoted BOOL DEFAULT FALSE
);

Once the stage is set, it’s time to seed it. Copy the content of seeds.sql and execute it on BigQuery. Note that the inserts are enclosed by begin/commit transaction statements to ensure atomicity. If any of those lines fails to execute, the whole block fails, so you can fix things and run it again without loss.

At this point, you’ve initialized the monitoring environment in BigQuery with your baselines for exclusion, ARIMA, and heartbeat. The next step is to materialize ingestion_hourly, a small (few MB) hourly rollup of the last 90 days of ingestion_metrics, with junk log types filtered out, for two reasons. First, it makes every downstream consumer cheap: ARIMA+ training scans a few MB instead of tens of GB, and quarterly reassessment queries reuse the same rollup at near-zero cost.

Second, and more importantly, the 90-day window is rolling, rebuilt monthly by scheduled query (next step): each retrain drops ~30 days off the tail and adds the most recent ~30, so by month 4 every byte of the original bootstrap data has aged out completely. That’s how the model stays honest about current reality: drift adapts, sources promoted to ARIMA+ at the quarterly reassessment get their 90d history pulled into the next train, and decommissioned sources stop polluting the model. The materialization looks like an optimization and the rolling rebuild is what makes it work.

CREATE OR REPLACE TABLE `chronicle-self.log_health.ingestion_hourly` AS
SELECT log_type AS source_key,
       TIMESTAMP_TRUNC(start_time, HOUR) AS hour_bucket,
       SUM(event_count) AS log_count
FROM `chronicle-google.datalake.ingestion_metrics`
WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND log_type IS NOT NULL
  AND log_type NOT IN (SELECT log_type FROM `chronicle-self.log_health.excluded_log_types`)
GROUP BY source_key, hour_bucket;

With the training data in place, run the next query to train ARIMA+.

CREATE OR REPLACE MODEL `chronicle-self.log_health.arima_ingestion`
OPTIONS(model_type='ARIMA_PLUS',
        time_series_data_col='log_count',
        time_series_timestamp_col='hour_bucket',
        time_series_id_col='source_key',
        data_frequency='HOURLY',
        auto_arima=TRUE,
        decompose_time_series=TRUE) AS
SELECT r.source_key, r.hour_bucket, r.log_count
FROM `chronicle-self.log_health.ingestion_hourly` r
JOIN `chronicle-self.log_health.source_config` c
  ON c.source_key = r.source_key
WHERE c.enabled AND c.detection_method = 'ARIMA';

The query runs auto-ARIMA independently per source_key, meaning each log source gets its own model with no cross-source influence. For each series, BQML searches for the best (p, d, q) triplet: p is how many past hours the model looks back when predicting the next one (autoregressive order); d is how many times the series must be differenced to remove trend and make it stationary; q is how many past prediction errors factor into the forecast — moving-average order. With decompose_time_series=TRUE, the signal is also split into trend, seasonal, and residual components, each fitted separately. The result is stored as a BigQuery ML model object in the same project and dataset namespace as your tables, but addressed with MODEL in SQL rather than TABLE. Nothing is written to a regular table, thus all the per-source fitted parameters live inside the model artifact.

Note

In my experience, the original ingestion_metrics had ~29 GB of data; ingestion_hourly, the filtered table used to train ARIMA+, had only 27 MB. The whole training run against this 90-day window took 13 seconds. Super cheap.

holiday_region is intentionally omitted. ARIMA+’s holiday-effect modeling requires two conditions: the time series must be daily or weekly (not hourly), and it must span more than a year. This pipeline is hourly, which alone disqualifies it; extending the training window past a year doesn’t help either. I tried it, and every source still came back with has_holiday_effect = FALSE. BQML silently disables the option at hourly frequency regardless of history length; setting it looks like a knob, but does nothing here. If your setup is on daily or weekly aggregation with >1 year of history, set it to a country code ('US', 'BR', …) or region group ('NA', 'LAC', 'EMEA', 'JAPAC', 'GLOBAL').

Once the model is trained, validate what seasonality it actually captured.

SELECT source_key,
       non_seasonal_p, non_seasonal_d, non_seasonal_q,
       seasonal_periods,
       has_holiday_effect, has_spikes_and_dips, has_step_changes
FROM ML.ARIMA_EVALUATE(MODEL `chronicle-self.log_health.arima_ingestion`);

Cross-reference the seasonal_periods column against the acf_168h values you saved in signals.csv during Assessment. If a source had strong weekly autocorrelation there (acf_168h > 0.5) but the model reports only DAILY here, you’ve hit a known BQML behavior: ARIMA+’s auto-selection is systematically conservative on WEEKLY seasonality with high-volume hourly count data. The pattern is present in the raw signal, but the selector favors daily and doesn’t add weekly on top. I verified this experimentally by disabling the spike/dip cleaner (clean_spikes_and_dips=FALSE) and it didn’t recover weekly either. It’s a genuine limitation of the auto-selection algorithm on this data shape, not something you can preprocess away.

That’s not a reason to abandon ARIMA+ though. It’s a reason to know its blind spots and design around them, which is exactly what the promotion layer does. The persistence and magnitude gates on anomaly_candidates were built for this: the model doesn’t need to be perfect, it needs to be statistically useful and paired with logic that filters model noise from operator signal.

As a time series model, ARIMA+ is only as good as the signal it’s fed. Three things break it:

Low coverage: a source that’s silent 40% of the time has no consistent pattern to learn
High variability: if volume swings 7× the mean hour-to-hour (CV > 1.0), the confidence band becomes so wide it misses real anomalies
Too few data points: ARIMA+ needs history, ideally 90 days of consistent hourly data

That’s the CV and coverage split from the assessment query. Sources that pass both thresholds get meaningful predictions. Sources that don’t get heartbeat.

Note

You might think: “why not train the model on labeled data instead?” As a security engineer coming from detection engineering, I reach for this: a hand-curated training set with clean, labeled spikes and dips should produce a more accurate model, right?

First, ARIMA+ is unsupervised: it’s a forecaster, not a classifier. There’s no input slot for “this hour was an anomaly.” The model learns what normal looks like from historical data and flags deviations at inference time and labels don’t participate. Second, training on synthetic clean data makes production worse, not better. If you fabricate a training set with tidy weekly patterns and no real noise, the model learns that pattern; deployed against actual traffic (with drift, real weekend behavior, real incidents already in history), its confidence bands are miscalibrated and everything looks anomalous.

The version of the idea that does have merit is injecting synthetic anomalies into real data for benchmarking (does the model catch a known 30% dip injected at 3 AM last Wednesday?) which validates sensitivity without touching production. And if you truly want a labeled approach, you’re leaving ARIMA+ behind entirely: that’s a supervised classifier (random forest, gradient boost, LSTM), a different pipeline, and a different ongoing cost, because someone has to label incidents forever. Almost never the right trade for log-ingestion monitoring at fleet scale.

Setup

With the monitoring stack seeded, it’s time to schedule the queries that will keep it running. Start with the monthly model retrain.

Name: log-health-retrain-monthly
Frequency: Months, every 1 month, day 1 at 04:00 UTC
Query: see log-health-retrain-monthly in the Appendix

Next, schedule the hourly detection query. This is the core of the pipeline. It runs every hour and detects anomalies across all monitored sources.

Name: log-health-detect-hourly
Frequency: Hours, every 1 hour, time-of-day minute :18. Any date works; what matters is the :18 mark, giving buckets ~18 min to settle
Query: see log-health-detect-hourly in the Appendix

Now, create the weekly monitor. This one watches the monitoring pipeline itself (who watches the watchmen?). It overwrites pipeline_health_status each Monday. Empty table means healthy environment; any rows mean something to investigate. Apps Script polls the table and posts to Slack when non-empty.

Name: log-health-pipeline-health-weekly
Frequency: Weeks, every Monday at 11:00 UTC (adjust for your timezone)
Query: see log-health-pipeline-health-weekly in the Appendix

Another weekly monitor, this one for UNSPECIFIED_LOG_TYPE. It’s a Chronicle-specific check that watches whether Chronicle is receiving garbage. Overwrites unspecified_growth_status each Monday. Empty table means stable environment; any row means week-over-week growth exceeded 20%. Apps Script polls the table and posts to Slack when non-empty.

Name: log-health-unspecified-growth-weekly
Frequency: Weeks, every Monday at 11:05 UTC (adjust for your timezone)
Query: see log-health-unspecified-growth-weekly in the Appendix

Notifications

Detection is only useful if someone reads it, so the notification layer closes the loop. I used Slack, but the logic is the same for any webhook; only the delivery method changes. Apps Script is the natural fit since everything else runs on Google.

The full script can be found in the References section. It has four trigger functions that map to the four jobs: real-time anomaly delivery (checkAnomalies), a daily inventory digest (dailyDigest), a weekly pipeline-health check (weeklyPipelineHealth), and a weekly UNSPECIFIED_LOG_TYPE growth check (weeklyUnspecifiedGrowth). Put the code in the editor, save it as log-health.gs, and link it to your GCP project so it can read and write BigQuery. Then set up one trigger per function in the Triggers sidebar. I used UTC; adjust for your timezone:

checkAnomalies: Time-driven → Minutes timer → Every 10 minutes.
dailyDigest: Time-driven → Day timer → 11:00 to 12:00 UTC.
weeklyPipelineHealth: Time-driven → Week timer → Mondays 12:00 to 13:00 UTC, an hour after the 11:00 UTC scheduled query it reads from.
weeklyUnspecifiedGrowth: Time-driven → Week timer → Mondays 12:00 to 13:00 UTC, an hour after the 11:05 UTC scheduled query it reads from.

Validate

With all the pieces set on the chessboard, it’s time to validate. All in BQ Studio: manual schedule runs from Scheduled queries → click name → Run now:

Run log-health-detect-hourly once then check both tables separately, one line at a time:
```
SELECT COUNT(*) FROM `chronicle-self.log_health.anomaly_candidates`;
SELECT COUNT(*) FROM `chronicle-self.log_health.anomalies`;
```
First run may both be 0 (a quiet hour); run again after the next :18 to confirm flow.
Run log-health-pipeline-health-weekly and expect zero rows.
Run log-health-unspecified-growth-weekly and expect zero rows.

Synthetic Slack test:

INSERT INTO `chronicle-self.log_health.anomalies`
  (detected_at, source_key, anomaly_type, hour_bucket, log_count, sent_to_slack)
VALUES (CURRENT_TIMESTAMP(), 'TEST_SOURCE', 'FULL_STOP', CURRENT_TIMESTAMP(), 0, FALSE);

Wait up to 10 min, confirm Slack delivery, then clean up:

DELETE FROM `chronicle-self.log_health.anomalies` WHERE source_key = 'TEST_SOURCE';

Verify job history (last 24h):

SELECT creation_time, statement_type, state,
       ROUND(total_bytes_processed/POW(2,30), 3) AS gib_processed,
       error_result.message AS error
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND query LIKE '%log_health%'
ORDER BY creation_time DESC;

All recent runs must show state = DONE. Hourly detector gib_processed ≲ 14 (336h heartbeat scan dominates).

If the validation is OK at this point, deployment went fine. From here it’s monitoring and tuning for the next days: adjusting thresholds, adding or suppressing sources where needed. Keep the same discipline as during Assessment: investigate first, tune second. An ARIMA alert is a real statistical anomaly. Reach for understanding the source’s behavior before you reach for a higher threshold to silence it.

Take my own case. After moving to production, I noticed a lot of alerts from GITHUB, all spikes. But GitHub is one of my oldest log sources; it’s been running at the company for ages, so ARIMA+ should know its pattern. It was only when I sat with the problem that it clicked: with the AI hype and a steady increase of people (plus more and more agents) using GitHub, my org’s traffic had climbed significantly. That shifted the baseline and made ARIMA+’s predictions stale. From there, I had to decide: accept the spike alerts or bump the ARIMA+ threshold to suppress them, knowing that would make predictions less accurate. As with any detection logic, there’s no right or wrong. It’s just what fits your scenario better.

Note

I run this in a distributed, mixed-workforce setup with fast source churn, heavy 24/7 automation, and human-driven volume growing steadily, like AI adoption on GitHub.

When ARIMA+ kept picking up only daily seasonality, I assumed the environment was the problem: constant noise drowning out any weekly rhythm. Digging into the data disproved that. Human-driven sources have clean weekly patterns: weekend medians run 40–65% of weekday medians, and lag-168h autocorrelation sits above 0.7 on most. The issue was that BQML’s auto-ARIMA is conservative about picking WEEKLY on hourly count data at this scale, and there’s no knob that recovers it. I tested disabling the spike/dip cleaner, nothing changed.

The tuning recipes below cover all common scenarios. Expect to apply several of them in the first week.

Operations

The deployment is done. From here the system runs on its own with scheduled queries detecting anomalies, the model retraining monthly, and pipeline health running weekly. Your job shifts to monitoring and adjusting: a new PDCA loop, this one for keeping log health in shape. The diagram below shows the three operational cadences:

flowchart TD
    monitor[Monitor fleet] --> check{Anomaly?}
    check -->|Yes| investigate[Investigate\nand act]
    investigate --> monitor
    check -->|No| monitor

    monitor -->|Alert quality drifts| tune[Tune thresholds\nor reclassify sources]
    tune --> monitor

    monitor -. Every 90 days .-> reassess[Quarterly reassessment\nRe-profile · replan · apply]
    reassess --> monitor

Monitoring

Once deployed, the monitoring runs mostly on autopilot. While the hourly query detects anomalies, the monthly retrain keeps the model current. Two weekly jobs watch the pipeline itself. The only thing you actually do is review and act when something looks wrong.

The query below tracks ARIMA+ alert quality per source over the training window. If a source’s per_day value comes out above 2, bump its anomaly_prob_threshold one step (0.99 → 0.995 → 0.998 → 0.999). The threshold is the minimum probability ARIMA+ needs before flagging a point as anomalous. Each step up raises the confidence bar, so fewer alerts fire, but the ones that do are the ones the model has the least doubt about.

If the weekend share on_weekend / (on_weekend + on_weekday) exceeds 50%, apply the same bump. Since BQML often misses WEEKLY seasonality (see Deployment), weekend/weekday imbalance is a leading indicator that a source’s threshold needs to rise. Compare against the source’s wd_we_ratio in signals.csv: if that ratio was well above 1.0 and weekend alerts dominate here, the model isn’t compensating for the pattern the raw data has.

WITH window_size AS (
  SELECT DATE_DIFF(MAX(DATE(hour_bucket)), MIN(DATE(hour_bucket)), DAY) + 1 AS days
  FROM `chronicle-self.log_health.ingestion_hourly`
),
a AS (
  SELECT d.source_key,
         IF(d.log_count > d.upper_bound, 'SPIKE', 'DIP') AS kind,
         IF(EXTRACT(DAYOFWEEK FROM d.hour_bucket AT TIME ZONE 'America/New_York') IN (1, 7),
            'weekend', 'weekday') AS daytype
  FROM ML.DETECT_ANOMALIES(MODEL `chronicle-self.log_health.arima_ingestion`,
                           STRUCT(0.99 AS anomaly_prob_threshold)) d
  JOIN `chronicle-self.log_health.source_config` c ON c.source_key = d.source_key
  WHERE d.is_anomaly
    AND d.anomaly_probability >= c.anomaly_prob_threshold
    AND (
      ABS(d.log_count - IF(d.log_count > d.upper_bound, d.upper_bound, d.lower_bound)) >= 10
      OR ABS(d.log_count - IF(d.log_count > d.upper_bound, d.upper_bound, d.lower_bound))
         / NULLIF(IF(d.log_count > d.upper_bound, d.upper_bound, d.lower_bound), 0) >= 0.05
    )
)
SELECT a.source_key,
       COUNT(*) AS anomalies_total,
       w.days AS window_days,
       ROUND(COUNT(*) / w.days, 2) AS per_day,
       COUNTIF(kind = 'SPIKE') AS spikes,
       COUNTIF(kind = 'DIP') AS dips,
       COUNTIF(daytype = 'weekend') AS on_weekend,
       COUNTIF(daytype = 'weekday') AS on_weekday
FROM a CROSS JOIN window_size w
GROUP BY a.source_key, w.days
ORDER BY anomalies_total DESC;

Every now and then, you need a view of what’s under monitoring. The next query can help with that. It’ll give you an overview of the log sources in scope, including hours silent and their statuses.

WITH latest_per_source AS (
  SELECT log_type AS source_key, MAX(start_time) AS latest_event
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 336 HOUR)
    AND event_count > 0
  GROUP BY log_type
),
recent_alerts AS (
  SELECT source_key,
         COUNTIF(anomaly_type = 'SPIKE')     AS spike_alerts_7d,
         COUNTIF(anomaly_type = 'DIP')       AS dip_alerts_7d,
         COUNTIF(anomaly_type = 'FULL_STOP') AS full_stops_7d,
         MAX(detected_at) AS last_alert_at
  FROM `chronicle-self.log_health.anomalies`
  WHERE detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  GROUP BY source_key
),
recent_candidates AS (
  SELECT source_key,
         COUNTIF(anomaly_type = 'SPIKE') AS spike_cands_7d,
         COUNTIF(anomaly_type = 'DIP')   AS dip_cands_7d
  FROM `chronicle-self.log_health.anomaly_candidates`
  WHERE detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  GROUP BY source_key
)
SELECT c.source_key,
       c.detection_method,
       c.anomaly_prob_threshold        AS arima_threshold,
       c.max_silence_hours,
       l.latest_event,
       TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), l.latest_event, HOUR) AS hours_silent,
       CASE
         WHEN l.latest_event IS NULL THEN 'DEAD (no data in 14d)'
         WHEN TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), l.latest_event, HOUR) > c.max_silence_hours THEN 'ALERTING'
         WHEN TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), l.latest_event, HOUR) > c.max_silence_hours * 0.75 THEN 'APPROACHING'
         ELSE 'HEALTHY'
       END AS heartbeat_status,
       COALESCE(a.spike_alerts_7d, 0) AS spike_alerts_7d,
       COALESCE(a.dip_alerts_7d, 0)   AS dip_alerts_7d,
       COALESCE(a.full_stops_7d, 0)   AS full_stops_7d,
       COALESCE(d.spike_cands_7d, 0)  AS spike_cands_7d,
       COALESCE(d.dip_cands_7d, 0)    AS dip_cands_7d,
       a.last_alert_at
FROM `chronicle-self.log_health.source_config` c
LEFT JOIN latest_per_source  l ON l.source_key = c.source_key
LEFT JOIN recent_alerts      a ON a.source_key = c.source_key
LEFT JOIN recent_candidates  d ON d.source_key = c.source_key
WHERE c.enabled
ORDER BY
  CASE
    WHEN l.latest_event IS NULL THEN 0
    WHEN TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), l.latest_event, HOUR) > c.max_silence_hours THEN 1
    WHEN TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), l.latest_event, HOUR) > c.max_silence_hours * 0.75 THEN 2
    ELSE 3
  END,
  c.detection_method, c.source_key;

Note

Chronicle’s ingestion_metrics writes rows with event_count = NULL or 0 for log types that are configured in Chronicle but haven’t sent real events yet. That causes two silent failure modes: NEW_SOURCE fires for configured-but-not-yet-live sources (rows exist even though SUM(event_count) = 0), and FULL_STOP never fires for a dead source that Chronicle keeps writing 0-count metadata rows for (MAX(start_time) stays recent). To fix that, add AND event_count > 0 to any query that uses MAX(start_time) as a proof-of-life signal, like in the fleet-inventory query below. Queries that use SUM(event_count) don’t need it because 0-count rows contribute nothing to the totals anyway.

No collection of queries can cover every investigation scenario. The best investment for a good operation is time with the schema: understand the tables (source_config, anomaly_candidates, anomalies, ingestion_hourly), their relationships, and what each field actually means. Combine that with a basic feel for how ARIMA+ scores data and you’ll be able to write your own queries, read the signal, and make tuning decisions with confidence.

Use AI to help with this. Share the schema and ask it to write investigation queries for whatever you’re seeing. In my experience it’s precise. More on that in the AI section.

Tuning

No set of recipes can anticipate every situation, but the operations below cover the common cases. Each is self-contained and copy-paste ready. Multi-step operations are wrapped in BEGIN TRANSACTION / COMMIT TRANSACTION to stay atomic. After any change, re-run the fleet-overview query from the previous section to verify the result.

Raise Silence Threshold

Repeated FULL_STOP alerts for the same source with no real outage behind them, because the source legitimately goes quiet for longer than its current tier allows. Step up one tier at a time; see the tier table in Design for reference.

UPDATE `chronicle-self.log_health.source_config`
SET max_silence_hours = <hours>
WHERE source_key = '<source>';

Raise ARIMA+ Threshold

SPIKE or DIP alerts repeating for the same source without a real event behind them. The alert-quality query in Monitoring signals this when per_day > 2. Step up one increment at a time: 0.99 → 0.995 → 0.998 → 0.999.

UPDATE `chronicle-self.log_health.source_config`
SET anomaly_prob_threshold = <threshold>
WHERE source_key = '<source>';

Demote ARIMA+ to HEARTBEAT

A source’s behavior has shifted since classification: too bursty, too sparse, or the confidence band has become meaninglessly wide. Between reassessments this is a direct edit; at the quarterly reassessment it’s handled through replan.csv.

UPDATE `chronicle-self.log_health.source_config`
SET detection_method       = 'HEARTBEAT',
    anomaly_prob_threshold = NULL,
    max_silence_hours      = <hours>
WHERE source_key = '<source>';

Add a Source

A NEW_SOURCE alert fired for a log type that should be monitored. Classify it as HEARTBEAT because new sources don’t have 90 days of history yet, so ARIMA isn’t an option. At the next quarterly reassessment, promote it if it qualifies.

INSERT INTO `chronicle-self.log_health.source_config`
  (source_key, enabled, detection_method, max_silence_hours)
VALUES ('<source>', TRUE, 'HEARTBEAT', <hours>);

Exclude a Source Permanently

Source is deprecated, decommissioned, or sends junk Chronicle can’t parse. Adding it to excluded_log_types also filters it from ingestion_hourly, so it won’t pollute ARIMA+ training data.

BEGIN TRANSACTION;

DELETE FROM `chronicle-self.log_health.source_config`
WHERE source_key = '<source>';

INSERT INTO `chronicle-self.log_health.excluded_log_types` (log_type, reason)
VALUES ('<source>', '<reason>');

COMMIT TRANSACTION;

Re-Include an Excluded Source

A previously excluded source is back in production and should be monitored again. Start as HEARTBEAT because it needs to accumulate history before ARIMA is an option.

BEGIN TRANSACTION;

DELETE FROM `chronicle-self.log_health.excluded_log_types`
WHERE log_type = '<source>';

INSERT INTO `chronicle-self.log_health.source_config`
  (source_key, enabled, detection_method, max_silence_hours)
VALUES ('<source>', TRUE, 'HEARTBEAT', <hours>);

COMMIT TRANSACTION;

Suppress a Source Temporarily

Planned maintenance, a known outage window, or a holiday period where silence is expected. The suppression expires automatically at until. NULL means indefinite, so use sparingly; for anything permanent, prefer excluded_log_types.

-- Add suppression
INSERT INTO `chronicle-self.log_health.suppressions` (source_key, reason, until)
VALUES ('<source>', '<reason>', TIMESTAMP '<YYYY-MM-DD HH:MM:SS UTC>');

-- Remove early if maintenance ends sooner than expected
DELETE FROM `chronicle-self.log_health.suppressions`
WHERE source_key = '<source>';

Quarterly Reassessment

The initial setup was a blank-slate plan from raw discovery. From day 90 onward, every 90 days you replan, but unlike the bootstrap, you merge fresh discovery against current state, preserving manual tuning (threshold overrides, oversized max_silence_hours for sparse feeds, your full excluded_log_types). Quarterly reassessments match the ARIMA training window so cohort decisions and model retraining stay on the same clock. Set a calendar reminder. First reassessment at day 90, then every 90 days.

Between reassessments you can:

Add a new source as HEARTBEAT when NEW_SOURCE fires
Add a source to excluded_log_types
Adjust an existing source’s threshold or max_silence_hours
Remove a source from monitoring entirely

The reassessment workflow is required for:

Promote HEARTBEAT to ARIMA (needs 90d of training data and cohort review)
Demote ARIMA to HEARTBEAT

Export Current State from BigQuery

Start the reassessment by opening BQ Studio. Click on new query (+), paste the next snippet, run, and click on Save results as CSV as reassess-current-config.csv.

SELECT source_key, enabled, detection_method, anomaly_prob_threshold, max_silence_hours
FROM `chronicle-self.log_health.source_config`
ORDER BY source_key;

Delete the previous query and execute the next, saving the CSV as reassess-current-excluded.csv.

SELECT log_type, reason
FROM `chronicle-self.log_health.excluded_log_types`
ORDER BY log_type;

Re-Discovery Against Post-Deployment Data

Re-run the estate and gap queries from Assessment, but reading from chronicle-self.log_health.ingestion_hourly instead of raw ingestion_metrics. It’s cheap: the rollup is refreshed monthly by the retrain job. Similarly to the previous step, two queries must be run here. Start running the next snippet to reassess the estate and save results as reassess-estate.csv.

WITH prof AS (
  SELECT source_key,
         COUNT(*) AS hours_with_data,
         ROUND(COUNT(*)/2160*100, 1) AS pct_covered,
         ROUND(AVG(log_count)) AS avg_per_hour,
         ROUND(STDDEV(log_count)) AS stddev_per_hour,
         ROUND(SAFE_DIVIDE(STDDEV(log_count), AVG(log_count)), 2) AS cv
  FROM `chronicle-self.log_health.ingestion_hourly`
  GROUP BY source_key
)
SELECT *,
  CASE WHEN pct_covered < 80 OR cv IS NULL OR cv > 1.0 THEN 'HEARTBEAT'
       ELSE 'ARIMA' END AS suggested_track,
  CASE WHEN pct_covered < 80 OR cv IS NULL OR cv > 1.0 THEN NULL
       WHEN cv <= 0.5 THEN 0.99
       WHEN cv <= 0.8 THEN 0.995
       ELSE 0.998 END AS suggested_threshold
FROM prof
ORDER BY avg_per_hour DESC;

The second one is the next snippet and will reassess gaps. Save its results as reassess-gaps.csv.

WITH gaps AS (
  SELECT source_key,
         TIMESTAMP_DIFF(
           hour_bucket,
           LAG(hour_bucket) OVER (PARTITION BY source_key ORDER BY hour_bucket),
           HOUR
         ) AS gap_hours
  FROM `chronicle-self.log_health.ingestion_hourly`
)
SELECT source_key,
       COUNT(*) AS n_gaps,
       ROUND(APPROX_QUANTILES(gap_hours, 100)[OFFSET(50)]) AS p50_gap,
       ROUND(APPROX_QUANTILES(gap_hours, 100)[OFFSET(95)]) AS p95_gap,
       ROUND(APPROX_QUANTILES(gap_hours, 100)[OFFSET(99)]) AS p99_gap,
       MAX(gap_hours) AS max_gap
FROM gaps
WHERE gap_hours IS NOT NULL AND gap_hours > 0
GROUP BY source_key
ORDER BY p99_gap DESC;

Generate the Replan

With all necessary artifacts in hand, use derive-seeds.py to replan, as shown in the next command.

python3 derive-seeds.py replan \
  reassess-current-config.csv reassess-current-excluded.csv \
  reassess-estate.csv reassess-gaps.csv \
  > replan.csv

Output is a plan CSV with current tracks/thresholds preserved, not reset to fresh suggestions. The note column flags decisions:

`note` value	Meaning
(empty)	No change suggested. Leave row alone.
`PROMOTE candidate (...)`	Currently HEARTBEAT, fresh data suggests ARIMA.
`DEMOTE candidate (...)`	Currently ARIMA, fresh data suggests HEARTBEAT.
`NEW source — added as HEARTBEAT`	Not in current config; added as HEARTBEAT by default.
`GONE — was in config, no data in 90d. Keep, EXCLUDE, or delete row.`	In current config but absent from fresh data. Decide.
`EXCLUDED — <reason>`	Carried forward from current `excluded_log_types`.

The script prints a summary to stderr: counts of PROMOTE, DEMOTE, NEW, GONE, and carried-forward EXCLUDED rows.

Review and Edit `replan.csv`

This script will help find opportunities for improvement, but it doesn’t have the context of an operation. Hence, its output must be critically reviewed. Walk the file in your editor:

PROMOTE candidate: confirm the source has been HEARTBEAT-but-ARIMA-suggested since the prior reassessment (anti-flapping). If yes, change suggested_track to ARIMA. If no, leave it.
DEMOTE candidate: same logic in reverse. Usually means the ARIMA+ model is producing too-wide bands or too many false positives.
NEW source: any log_type present in the fresh discovery but missing from your current source_config.
GONE: three options: keep monitoring (leave as-is, FULL_STOP keeps firing), exclude (change track to EXCLUDE, write reason in note), or delete the row entirely.
EXCLUDED: leave alone unless bringing it back. To re-enable: change track to HEARTBEAT (or ARIMA if it qualifies and you have prior-cycle confirmation) and clear the EXCLUDED — prefix from note.

Note that NEW and GONE entries deserve extra attention: both should have been caught by daily NEW_SOURCE and FULL_STOP alerts respectively, and handled in daily or weekly operations. A large count of either at reassessment means those alerts aren’t being acted on in day-to-day ops. In other words, your operational review is missing.

Emit MERGE Statements

Once the review is done, it’s necessary to update the tables with new values. Run the snippet below to do that.

python3 derive-seeds.py emit replan.csv --merge \
  --project chronicle-self \
  --dataset log_health > replan.sql

Output is a transaction-wrapped pair of MERGE statements: one for excluded_log_types, one for source_config. Each updates existing rows, inserts new ones, and removes anything absent from replan.csv.

Warning

The enabled flag on source_config is not touched by the MERGE. Sources you’ve manually UPDATEd to enabled = FALSE stay disabled across reassessments.

Apply

Open BQ Studio, create a new query, paste replan.sql, and run it. The BEGIN TRANSACTION; ... COMMIT TRANSACTION; wrapper makes the two merges atomic.

Retrain ARIMA+ on the New Cohort

If any PROMOTE/DEMOTE applied, manually run log-health-retrain-monthly once to pick up the new cohort immediately instead of waiting up to 30 days for the next scheduled retrain. Otherwise, skip.

Optionally, re-export the current config to a dated filename (reassess-2026-09-15-source_config.csv) for audit. Or rely on git history of replan.csv in your working folder.

Document Decisions

For any non-obvious decision, write a short note somewhere, like a comment in the SQL or a line in a doc. The CSV diffs in git capture what changed; the note captures why. Keep the artifacts in a Git repo, including a logbook for tracking operational tasks, and use a simple template so entries stay consistent.

How I Used AI Here

The RFC and architecture were done with Claude. That story is in the Research section. What I want to reflect on here is what happened during implementation, because that’s where the collaboration got interesting.

The MVP went up with hourly model retraining. Claude had recommended it, since more frequent retraining means fresher predictions. That logic held in isolation. But I’m a frugal engineer and I know companies burn money on cloud when no one’s watching. Something felt off. That night, after finishing the MVP, I was watching the World Cup after my toddler went to bed. I opened Gemini on my phone and shared the context. It spotted the problem immediately: retraining hourly is wasteful because 30 days of data barely moves in an hour. The model learns nothing new; we were just burning money. I jumped to my workstation and disabled the scheduled retraining. Fortunately it had only run five times. I went back to Claude on Saturday and proposed monthly retraining. It agreed immediately and helped me make the change.

That sequence matters. Claude didn’t flag the cost issue during the MVP. It optimized for model freshness without accounting for operational frugality (a constraint I hadn’t stated explicitly). Gemini, coming in cold, reasoned about it differently. Getting a second opinion was the right call, and I now do it more deliberately. It highlights an important thing when working with AI: if you don’t have sufficient knowledge on a subject to challenge AI’s decisions, you might be in hot water. My experience with Claude had been exceptional so far (see Lantana), but I was always using it in my field of expertise. Once we moved into the Data Science domain, I had to rely almost entirely on Claude’s decisions, as I had no depth to push back. Looking at other cases I’m aware of, like designers writing code and publishing secrets on GitHub, I see the problem is real. AI maximizes everything: if you’re an expert doing the right thing, right thing squared. If not, wrong thing squared.

The other turning point was the alert volume after go-live. I was seeing too many ARIMA alerts. I asked Claude several times whether I should raise the thresholds, always reminding it I’m not an ML expert and it was OK to push back. It pushed back firmly: the anomalies were real, the environment was too dynamic for ARIMA+ to model weekly seasonality properly, and touching thresholds prematurely would just mask problems without fixing them. Wait, it said. More on it next.

I agreed, made a coffee, went to sit in the backyard and thought about it. The insight came from detection engineering, not ML: every alert should have an associated action; otherwise it’s informational, not a detection. If ARIMA+ was generating too many candidates that didn’t warrant paging, the answer wasn’t to change the model, it was to add a layer between the model and the pager. That’s the anomaly_candidates table and the promotion query that runs alongside detection, the architectural centerpiece of the whole pipeline. It was a genuine joint effort: I brought the framing from detection engineering, Claude brought the depth to translate it into working SQL, and we tightened the design together as we went. I challenged it more than once: wouldn’t raising thresholds be simpler? It held the line: the two-table approach was more principled.

Weeks later, a data-scientist-lens investigation (also with Claude) turned up something surprising. The initial diagnosis (“the environment is too dynamic for ARIMA+ to model weekly seasonality”) was partly wrong. The raw data has clean weekly patterns on human-driven sources; BQML’s auto-ARIMA just doesn’t pick them up on this data shape, and can’t be tuned to do so. The architectural response was right anyway. The promotion layer wasn’t compensating for a chaotic environment (as we first thought). It was compensating for a model limitation we didn’t fully understand yet. Same layer, better justification. That’s a nice lesson: solid architecture holds up even when the diagnosis behind it turns out to be incomplete. Don’t wait for a perfect understanding of the underlying problem to build a robust structure around it.

SQL was one thing that stood out beyond architecture. Once the schema was in place, Claude was precise at writing investigation queries against it: it understood the tables quickly and generated accurate SQL. Across all my sessions it made only a few minor mistakes, like referencing a field that didn’t exist. Everything else was on target. If you’re exploring the data and need a query for something specific, just share the schema and describe what you want to see. I assume this precision comes from the fact that SQL has been around for a long time and is heavily documented: books, blog posts, forum threads. It’s clearly an advantage for any LLM.

There’s one more dimension worth naming: AI as a learning accelerator. I’m a security engineer, not a data scientist. I studied statistics at university but I’m far from a specialist. Metrics like CV and p99 were unfamiliar territory when I started this project, let alone ARIMA parameters. AI was extremely helpful here, not just for writing code, but for explaining what these concepts mean, why they matter in this context, and helping me build enough understanding to reason about the model and make informed decisions. A practical tip for security engineers approaching this: treat ARIMA+ as a black box. Parameters go in, anomaly scores come out as numbers. Your job is to understand what to feed it and what to do with the output, not the mathematics underneath. Don’t try to become a machine learning specialist. Be a user of the tool.

What I took from all of this was to shift AI left. Bring it in at the problem-framing stage, not just when you have code to write. Let it interview you. Let it disagree. Consider it a peer that should feel OK to push back when it’s for a greater good. The quality of what comes out depends on the quality of the conversation going in, and on you staying critical throughout. AI won’t stop you from building the wrong thing if you don’t give it the right context and the permission to challenge you.

Takeaways

Log monitoring is essential for any SOC. Unfortunately, SIEMs and other related tools don’t take it seriously, because they have no embedded mechanisms to monitor it. The proposed solution, although specific to the Google stack, can be adapted to different scenarios.

I’ve seen cases where the team has an expensive, magic-quadrant, modern SIEM, but when the time comes to respond to an incident, the required log source has been silent for days. A SIEM is as good as the logs it ingests. If it ingests no logs, it’s useless. Log ingestion monitoring must be taken seriously, or your whole security operation is at risk.

Future Work

Migrate Apps Script to Cloud Functions for an “enterprise-grade” solution; Apps Script resides on the user’s Google Drive.
Add a log source granularity beyond log type, useful for firewalls and other appliances aggregated under a single type.

Appendix: Scheduled Queries

log-health-retrain-monthly

-- Step A: rebuild 90-day rollup
CREATE OR REPLACE TABLE `chronicle-self.log_health.ingestion_hourly` AS
SELECT log_type AS source_key,
        TIMESTAMP_TRUNC(start_time, HOUR) AS hour_bucket,
        SUM(event_count) AS log_count
FROM `chronicle-google.datalake.ingestion_metrics`
WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
  AND log_type IS NOT NULL
  AND log_type NOT IN (SELECT log_type FROM `chronicle-self.log_health.excluded_log_types`)
GROUP BY source_key, hour_bucket;

-- Step B: retrain ARIMA+ on the fresh rollup (trains on a few MB)
CREATE OR REPLACE MODEL `chronicle-self.log_health.arima_ingestion`
OPTIONS(model_type='ARIMA_PLUS',
        time_series_data_col='log_count',
        time_series_timestamp_col='hour_bucket',
        time_series_id_col='source_key',
        data_frequency='HOURLY',
        auto_arima=TRUE,
        decompose_time_series=TRUE) AS
SELECT r.source_key, r.hour_bucket, r.log_count
FROM `chronicle-self.log_health.ingestion_hourly` r
JOIN `chronicle-self.log_health.source_config` c
  ON c.source_key = r.source_key
WHERE c.enabled AND c.detection_method = 'ARIMA';

log-health-detect-hourly

Runs every hour at :18. Detects SPIKE/DIP via ARIMA+, FULL_STOP via heartbeat, and NEW_SOURCE via set difference. Promotes persistent or high-magnitude ARIMA+ candidates to alerts.

At runtime, ML.DETECT_ANOMALIES() loads the per-source_key parameters stored in arima_ingestion and runs them forward against the fresh data window. For each (source_key, hour_bucket) pair it generates a point forecast and a probability distribution around it, then derives lower_bound and upper_bound at the requested anomaly_prob_threshold. Actual log_count outside those bounds returns is_anomaly = TRUE with an anomaly_probability score. The bands are computed on the fly, so nothing is looked up from a pre-computed table.

-- Step B: SPIKE/DIP detection → anomaly_candidates (raw signal, with severity_pct)
INSERT INTO `chronicle-self.log_health.anomaly_candidates`
  (detected_at, source_key, anomaly_type, hour_bucket, log_count, lower_bound, upper_bound, anomaly_probability, severity_pct)
SELECT CURRENT_TIMESTAMP(), a.source_key,
        IF(a.log_count > a.upper_bound, 'SPIKE', 'DIP'),
        a.hour_bucket, a.log_count, a.lower_bound, a.upper_bound, a.anomaly_probability,
        ROUND(
          ABS(a.log_count - IF(a.log_count > a.upper_bound, a.upper_bound, a.lower_bound))
          / NULLIF(IF(a.log_count > a.upper_bound, a.upper_bound, a.lower_bound), 0) * 100,
          1
        ) AS severity_pct
FROM ML.DETECT_ANOMALIES(
        MODEL `chronicle-self.log_health.arima_ingestion`,
        STRUCT(0.99 AS anomaly_prob_threshold),
        (SELECT log_type AS source_key,
                TIMESTAMP_TRUNC(start_time, HOUR) AS hour_bucket,
                SUM(event_count) AS log_count
        FROM `chronicle-google.datalake.ingestion_metrics`
        WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 HOUR)
        GROUP BY source_key, hour_bucket)
      ) a
JOIN `chronicle-self.log_health.source_config` c ON c.source_key = a.source_key
WHERE a.is_anomaly
  AND a.anomaly_probability >= c.anomaly_prob_threshold
  AND a.hour_bucket >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 4 HOUR)
  AND a.hour_bucket <  TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR)
  -- Noise floor: ≥10 events OR ≥5% deviation
  AND (
    ABS(a.log_count - IF(a.log_count > a.upper_bound, a.upper_bound, a.lower_bound)) >= 10
    OR ABS(a.log_count - IF(a.log_count > a.upper_bound, a.upper_bound, a.lower_bound))
        / NULLIF(IF(a.log_count > a.upper_bound, a.upper_bound, a.lower_bound), 0) >= 0.05
  )
  AND NOT EXISTS (
    SELECT 1 FROM `chronicle-self.log_health.suppressions` s
    WHERE s.source_key = a.source_key
      AND (s.until IS NULL OR s.until > CURRENT_TIMESTAMP())
  )
  AND NOT EXISTS (
    -- Per-hour dedup across overlapping 8h scoring windows
    SELECT 1 FROM `chronicle-self.log_health.anomaly_candidates` x
    WHERE x.source_key = a.source_key AND x.hour_bucket = a.hour_bucket
  );

-- Step C: FULL_STOP heartbeat (all enabled sources, both cohorts)
-- event_count > 0 excludes Chronicle's phantom rows (configured but not ingesting).
INSERT INTO `chronicle-self.log_health.anomalies`
  (detected_at, source_key, anomaly_type, hour_bucket)
WITH latest_per_source AS (
  SELECT log_type AS source_key, MAX(start_time) AS latest_event
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 336 HOUR)
    AND event_count > 0
  GROUP BY log_type
)
SELECT CURRENT_TIMESTAMP(), c.source_key, 'FULL_STOP', l.latest_event
FROM `chronicle-self.log_health.source_config` c
LEFT JOIN latest_per_source l ON l.source_key = c.source_key
WHERE c.enabled
  AND (
    l.latest_event IS NULL
    OR l.latest_event < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL c.max_silence_hours HOUR)
  )
  AND NOT EXISTS (
    SELECT 1 FROM `chronicle-self.log_health.suppressions` s
    WHERE s.source_key = c.source_key
      AND (s.until IS NULL OR s.until > CURRENT_TIMESTAMP())
  )
  AND NOT EXISTS (
    SELECT 1 FROM `chronicle-self.log_health.anomalies` x
    WHERE x.source_key = c.source_key
      AND x.anomaly_type = 'FULL_STOP'
      AND x.detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
  );

-- Step D: NEW_SOURCE detection
-- Re-fires daily until the log_type is classified in source_config or added to excluded_log_types.
INSERT INTO `chronicle-self.log_health.anomalies`
  (detected_at, source_key, anomaly_type, log_count, hour_bucket)
SELECT CURRENT_TIMESTAMP(), m.log_type, 'NEW_SOURCE', m.events_24h, m.first_seen
FROM (
  SELECT log_type,
          COALESCE(SUM(event_count), 0) AS events_24h,
          MIN(start_time) AS first_seen
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
    AND log_type IS NOT NULL
  GROUP BY log_type
  HAVING events_24h > 0
) m
WHERE m.log_type NOT IN (SELECT source_key FROM `chronicle-self.log_health.source_config`)
  AND m.log_type NOT IN (SELECT log_type   FROM `chronicle-self.log_health.excluded_log_types`)
  AND m.log_type NOT IN (
    SELECT source_key FROM `chronicle-self.log_health.anomalies`
    WHERE anomaly_type = 'NEW_SOURCE'
      AND detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  );

-- Step E: promote SPIKE/DIP candidates → anomalies
-- Persistence: ≥4 candidates in last 24h (per source_key + anomaly_type)
-- OR magnitude: worst severity_pct ≥ 200
-- Cooldown: 12h since last (source_key, anomaly_type) row in anomalies
INSERT INTO `chronicle-self.log_health.anomalies`
  (detected_at, source_key, anomaly_type, hour_bucket, log_count, lower_bound, upper_bound, anomaly_probability)
WITH unpromoted AS (
  SELECT * FROM `chronicle-self.log_health.anomaly_candidates`
  WHERE promoted = FALSE
    AND detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
),
ranked AS (
  SELECT source_key, anomaly_type,
          COUNT(*) AS hits_24h,
          MAX(severity_pct) AS worst_severity,
          ARRAY_AGG(STRUCT(hour_bucket, log_count, lower_bound, upper_bound,
                          anomaly_probability, severity_pct)
                    ORDER BY severity_pct DESC LIMIT 1)[OFFSET(0)] AS worst
  FROM unpromoted
  GROUP BY source_key, anomaly_type
)
SELECT CURRENT_TIMESTAMP(), source_key, anomaly_type,
        worst.hour_bucket, worst.log_count, worst.lower_bound,
        worst.upper_bound, worst.anomaly_probability
FROM ranked
WHERE (hits_24h >= 4 OR worst_severity >= 200)
  AND NOT EXISTS (
    SELECT 1 FROM `chronicle-self.log_health.anomalies` x
    WHERE x.source_key = ranked.source_key
      AND x.anomaly_type = ranked.anomaly_type
      AND x.detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR)
  );

-- Mark contributing candidates as promoted (24h window matches persistence, not cooldown)
UPDATE `chronicle-self.log_health.anomaly_candidates` c
SET promoted = TRUE
WHERE c.promoted = FALSE
  AND c.detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND EXISTS (
    SELECT 1 FROM `chronicle-self.log_health.anomalies` x
    WHERE x.source_key = c.source_key
      AND x.anomaly_type = c.anomaly_type
      AND x.detected_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  );

Note

NEW_SOURCE arrivals window: 24h vs 1h. The NEW_SOURCE detection query scans ingestion_metrics over the last 24 hours (start_time >= NOW() - 24 HOUR, HAVING events_24h > 0). Combined with the 24h dedup filter on anomalies at the bottom of that query, this gives one alert per calendar day per unclassified source: a friendly daily reminder to classify or exclude. Side effect: a source you just deprecated will keep qualifying for up to ~24h until the trailing edge of the window clears, so it can fire once or twice after the feed stops before going quiet. For most environments this is fine and it self-heals without intervention. If you deprecate sources often and want them to fall off the same hour they go silent, shrink the arrivals window to INTERVAL 1 HOUR and rename the accumulator (events_24h → events_1h). The 24h dedup filter stays either way; it’s what makes NEW_SOURCE a daily reminder for live-but-unclassified sources.

log-health-pipeline-health-weekly

CREATE OR REPLACE TABLE `chronicle-self.log_health.pipeline_health_status` AS
WITH recent_jobs AS (
  SELECT statement_type, MAX(creation_time) AS last_run, MAX(state) AS last_state
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    AND query LIKE '%log_health%'
  GROUP BY statement_type
)
SELECT *, CURRENT_TIMESTAMP() AS checked_at
FROM recent_jobs
WHERE last_state != 'DONE'
    OR last_run < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 25 HOUR);

log-health-unspecified-growth-weekly

CREATE OR REPLACE TABLE `chronicle-self.log_health.unspecified_growth_status` AS
WITH this_week AS (
  SELECT SUM(event_count) AS events_7d
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE log_type = 'UNSPECIFIED_LOG_TYPE'
    AND start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
),
prev_week AS (
  SELECT SUM(event_count) AS events_7d
  FROM `chronicle-google.datalake.ingestion_metrics`
  WHERE log_type = 'UNSPECIFIED_LOG_TYPE'
    AND start_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
    AND start_time <  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
)
SELECT t.events_7d AS current_week,
        p.events_7d AS prior_week,
        ROUND(SAFE_DIVIDE(t.events_7d - p.events_7d, p.events_7d) * 100, 1) AS growth_pct,
        CURRENT_TIMESTAMP() AS checked_at
FROM this_week t, prev_week p
WHERE SAFE_DIVIDE(t.events_7d - p.events_7d, p.events_7d) > 0.20;

References

Reuse

CC BY-SA 4.0

Citation

BibTeX citation:

@online{lopes2026,
  author = {Lopes, Joe},
  title = {Log {Health} {Monitoring}},
  date = {2026-07-16},
  url = {https://lopes.id/log/log-health-monitoring/},
  langid = {en}
}

For attribution, please cite this work as:

Lopes, Joe. 2026. “Log Health Monitoring.” July 16. https://lopes.id/log/log-health-monitoring/.

Context

Research

Design

Assessment

Deployment

Setup

Notifications

Validate

Operations

Monitoring

Tuning

Raise Silence Threshold

Raise ARIMA+ Threshold

Demote ARIMA+ to HEARTBEAT

Add a Source

Exclude a Source Permanently

Re-Include an Excluded Source

Suppress a Source Temporarily

Quarterly Reassessment

Export Current State from BigQuery

Re-Discovery Against Post-Deployment Data

Generate the Replan

Review and Edit replan.csv

Emit MERGE Statements

Apply

Retrain ARIMA+ on the New Cohort

Document Decisions

How I Used AI Here

Takeaways

Future Work

Appendix: Scheduled Queries

log-health-retrain-monthly

log-health-detect-hourly

log-health-pipeline-health-weekly

log-health-unspecified-growth-weekly

References

Reuse

Citation

Review and Edit `replan.csv`