Imported from hreis00/cortex-library (.github/skills/syslog-normalization/SKILL.md). Install upstream with npx skills add hreis00/cortex-library --skill syslog-normalization. Copyright stays with the author.

Syslog Normalization

Core Concepts

Project: overseer-d-011017 — syslog Dataset

The syslog dataset in overseer-d-011017 holds raw and partially-normalized log data from a large fleet of network devices (set-top boxes, routers). The source table f_syslog is unstructured, high-volume, and expensive to scan — treat it as a production-sensitive table at all times.

Critical cost constraint: f_syslog is ~43 TB and ~297 billion rows. A full table scan costs approximately $270 at on-demand pricing. Every query against f_syslog must include a WHERE filter on hour_part (the HOUR partition column). No exceptions.

Normalization Pipeline (Current State)

f_syslog.msg
    │
    ▼ Step 1 — Cleaning (r_msg_rules)
clean_msg  (REGEXP_REPLACE chain, 12 rules, applied in run_order)
    │
    ▼ Step 2 — Structure Identification + Field Extraction (r_patterns)
struct_msg  (RECORD: type, name, level, source_file, function_name, notes)
    │
    ▼ Step 3 — Planned: Tokenization → template (DAII architecture)
template   MD5(template) → fingerprint

Steps 1 and 2 are partially implemented — struct_msg in f_syslog is populated for known log structures. Step 3 (tokenization and fingerprinting) is planned but not yet running.

Log Structure Types

r_patterns currently identifies three log structure types by their structure field:

Coverage is incomplete — many app_name values produce logs that match no structure, leaving struct_msg.type as NULL.

Table Reference

f_syslog — Raw source

r_msg_rules — Cleaning rules (12 rules)

Applied in run_order (ascending integer) to msg, each rule removes a known noise prefix from log messages.

Current rules (in order): ANSI escape codes → 6 timestamp format variants → thread ID [tid=N] → hex prefix [0xABC] → leading colons/whitespace.

r_patterns — Field extraction patterns

Each row is one (structure, field, value) rule: if msg matches search_pattern, extract value regex capture into field.

r_patterns_field_priority — Field priority ordering

When multiple r_patterns rows match the same field for a single message, the row with the lowest rank wins.

t_syslog — Normalized target (no struct_msg)

Same columns as f_syslog except it omits struct_msg. This table represents the output of applying r_msg_rules without struct field extraction — a cleaned but flat staging table.

t_msg_sample — Test messages

Single column: msg STRING. Used to validate new cleaning rules and pattern matches safely, without scanning f_syslog.

Best Practices

Cost Control (Non-Negotiable on f_syslog)

1. Always filter on hour_part using a direct comparison. Never wrap it in a function.

   -- GOOD: partition pruning active
   WHERE hour_part >= '2026-03-01 00:00:00 UTC'
     AND hour_part <  '2026-03-02 00:00:00 UTC'
   
   -- BAD: disables partition pruning entirely
   WHERE DATE(hour_part) = '2026-03-01'

2. Combine clustering keys in filters when available. After the partition filter, add AND hw_model = '...' or AND severity = 'ERROR' to use block pruning.

3. Dry-run before every execution. f_syslog is 43 TB — one day of data can exceed 1 GB depending on device fleet size that day.

4. Use t_msg_sample to test regex patterns before applying them against f_syslog. Insert candidate messages, run the regex against t_msg_sample, verify output, only then write the rule.

5. Prefer APPROX_COUNT_DISTINCT for cardinality estimates on large windows.

Coverage Analysis

Coverage means: what fraction of f_syslog rows have struct_msg.type populated (i.e., matched at least one r_patterns structure)?

-- Coverage rate for a specific hour window
SELECT
  COUNTIF(struct_msg.type IS NOT NULL) AS covered,
  COUNT(*)                              AS total,
  ROUND(
    COUNTIF(struct_msg.type IS NOT NULL) / COUNT(*) * 100,
    2
  ) AS coverage_pct
FROM `overseer-d-011017.syslog.f_syslog`
WHERE hour_part >= '2026-03-01 00:00:00 UTC'
  AND hour_part <  '2026-03-02 00:00:00 UTC';

Coverage by app_name reveals which applications lack normalization rules:

SELECT
  app_name,
  COUNT(*)                                                    AS total,
  COUNTIF(struct_msg.type IS NOT NULL)                        AS covered,
  ROUND(COUNTIF(struct_msg.type IS NOT NULL) / COUNT(*) * 100, 1) AS pct
FROM `overseer-d-011017.syslog.f_syslog`
WHERE hour_part >= '2026-03-01 00:00:00 UTC'
  AND hour_part <  '2026-03-02 00:00:00 UTC'
GROUP BY app_name
ORDER BY total DESC
LIMIT 30;

Pattern Testing Workflow

Before inserting a new rule or pattern into r_msg_rules or r_patterns:

1. Populate t_msg_sample with representative uncovered messages.
2. Test the cleaning rule against the sample:

   SELECT
     msg AS original,
     REGEXP_REPLACE(msg, r'YOUR_REGEX', 'REPLACEMENT') AS cleaned
   FROM `overseer-d-011017.syslog.t_msg_sample`
   LIMIT 20;

3. Test the extraction regex against the cleaned message.
4. Verify no regression: the new rule must not corrupt already-covered messages. Run the cleaned-message check against a sample that includes covered messages.
5. Only then INSERT — with an explicit confirmation gate.

Patterns

Apply the Full r_msg_rules Cleaning Chain

The cleaning pipeline applies all r_msg_rules rules in sequence. BigQuery requires nested REGEXP_REPLACE calls (no loop). Build a UDF or a correlated subquery over the rules table. Use a UDF for maintenance:

-- Scalar UDF: applies all r_msg_rules in run_order
CREATE OR REPLACE FUNCTION `overseer-d-011017.syslog.fn_clean_msg`(raw_msg STRING)
RETURNS STRING
LANGUAGE js AS r"""
// rules injected at call-time via array; kept here as reference pattern
// Real usage: pre-materialize rules into an array and call this via JS UDF
// or apply REGEXP_REPLACE chain via a scripting block
return raw_msg;
""";

For one-off analysis without a UDF — apply known rules inline:

-- Apply the first 4 cleaning rules inline (expand to full 12 for production)
-- estimated: TBD
SELECT
  msg AS raw,
  REGEXP_REPLACE(
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        REGEXP_REPLACE(msg,
          r'#033\[[0-9;]*[a-zA-Z]', ''),   -- rule 0: ANSI escape codes
        r'^\d{6}-\d{2}:\d{2}:\d{2}\.\d{6}', ''),  -- rule 1: yymmdd-HH:MM:SS.µs
      r'^\[[A-Za-z]{3}, \d{1,2} [A-Za-z]{3} \d{4} \d{2}:\d{2}:\d{2}\s*\]', ''),  -- rule 2
    r'^\s*\d{2}:\d{2}:\d{2}\.\d{3}', '')   -- rule 3: HH:MM:SS.mmm
  AS clean_msg
FROM `overseer-d-011017.syslog.f_syslog`
WHERE hour_part >= '2026-03-01 00:00:00 UTC'
  AND hour_part <  '2026-03-01 01:00:00 UTC'
  AND app_name = 'dvb_manager'
LIMIT 50;

Find Uncovered Messages for a Specific App

-- estimated: TBD — run dry-run first, narrow to 1 hour
SELECT
  msg,
  severity,
  hw_model
FROM `overseer-d-011017.syslog.f_syslog`
WHERE hour_part >= '2026-03-01 00:00:00 UTC'
  AND hour_part <  '2026-03-01 01:00:00 UTC'
  AND app_name   = @app_name
  AND struct_msg.type IS NULL
ORDER BY msg
LIMIT 100;

Profile Distinct App Names and Volumes

-- estimated: TBD — narrow window to 1 day
SELECT
  app_name,
  APPROX_COUNT_DISTINCT(device_id) AS unique_devices,
  COUNT(*)                          AS total_logs,
  APPROX_COUNT_DISTINCT(msg)        AS approx_unique_msgs,
  COUNTIF(severity = 'ERROR')       AS error_count,
  COUNTIF(struct_msg.type IS NOT NULL) AS covered_count
FROM `overseer-d-011017.syslog.f_syslog`
WHERE hour_part >= '2026-03-01 00:00:00 UTC'
  AND hour_part <  '2026-03-02 00:00:00 UTC'
GROUP BY app_name
ORDER BY total_logs DESC
LIMIT 50;

Test a New Cleaning Rule Against t_msg_sample

-- Safe: scans only t_msg_sample, no partition filter needed (no partition)
SELECT
  msg                                               AS original,
  REGEXP_REPLACE(msg, r'YOUR_NEW_REGEX', '')        AS after_rule,
  REGEXP_CONTAINS(msg, r'YOUR_NEW_REGEX')           AS rule_matched
FROM `overseer-d-011017.syslog.t_msg_sample`
WHERE REGEXP_CONTAINS(msg, r'YOUR_NEW_REGEX');

Insert a New Cleaning Rule (DML gate required)

-- APPROVED: <reason> | <author> | <date>
-- estimated: negligible (small reference table)
INSERT INTO `overseer-d-011017.syslog.r_msg_rules`
  (run_order, rule, replacement, description)
VALUES
  (12, r'YOUR_REGEX', '', 'Description of what this rule removes');

Never execute without explicit CONFIRM. Verify run_order does not collide with an existing value.

Insert a New Extraction Pattern (DML gate required)

-- APPROVED: <reason> | <author> | <date>
INSERT INTO `overseer-d-011017.syslog.r_patterns`
  (structure, search_pattern, field, value, description)
VALUES
  ('NewStructure', r'^IDENTIFYING_REGEX', 'type',          'NewStructure',          'Log type label'),
  ('NewStructure', r'^IDENTIFYING_REGEX', 'name',          r'CAPTURE_REGEX_FOR_NAME', 'Module name'),
  ('NewStructure', r'^IDENTIFYING_REGEX', 'function_name', r'(\w+)\(\)',              'Function name from msg');

Planned: Tokenization to Template (DAII Step 3)

Tokenization replaces variable values with typed tokens, producing a stable template string. This enables grouping of semantically identical log shapes regardless of their runtime values.

-- Inline tokenization — apply token replacements in sequence
-- The full pipeline requires a complete REGEXP_REPLACE chain ordered by token specificity
-- (more specific patterns — e.g., UUIDs — must be applied before generic ones — e.g., <HEX>)
SELECT
  clean_msg,
  REGEXP_REPLACE(
  REGEXP_REPLACE(
  REGEXP_REPLACE(
  REGEXP_REPLACE(
  REGEXP_REPLACE(
  REGEXP_REPLACE(
  REGEXP_REPLACE(
  REGEXP_REPLACE(
    clean_msg,
    r'[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}', '<UUID>'),   -- UUIDs first
    r'(?:[0-9]{1,3}\.){3}[0-9]{1,3}(?::\d+)?', '<IP>'),                             -- IPv4 (:port)
    r'[0-9a-fA-F]{2}(?::[0-9a-fA-F]{2}){5}', '<MAC>'),                              -- MAC addresses
    r'0x[0-9a-fA-F]+', '<HEX>'),                                                     -- hex literals
    r'\b\d{4}-\d{2}-\d{2}[T ]\d{2}:\d{2}:\d{2}(?:\.\d+)?(?:Z|[+-]\d{2}:\d{2})?\b', '<TS>'),  -- ISO ts
    r'\b\d+\.\d+\b', '<FLOAT>'),                                                     -- floats before ints
    r'\b\d+\b', '<NUM>'),                                                             -- integer literals
    r'(?<=[=:\s])\"[^\"]*\"', '<STR>')                                               -- quoted strings
  AS template
FROM /* your cleaned message source */;

Planned: Fingerprinting (DAII Step 3 continued)

-- MD5 fingerprint of the template — identifies unique log shapes
SELECT
  template,
  TO_HEX(MD5(template)) AS fingerprint,
  COUNT(*)              AS occurrence_count
FROM (
  /* tokenization subquery from above */
)
GROUP BY template, fingerprint
ORDER BY occurrence_count DESC
LIMIT 50;

Anti-Patterns

Missing Partition Filter

-- NEVER: full-scan on 43 TB, ~$270 on-demand
SELECT app_name, COUNT(*) FROM `overseer-d-011017.syslog.f_syslog` GROUP BY 1;

-- ALWAYS: restrict to a known hourly window
SELECT app_name, COUNT(*)
FROM `overseer-d-011017.syslog.f_syslog`
WHERE hour_part >= '2026-03-01 00:00:00 UTC'
  AND hour_part <  '2026-03-02 00:00:00 UTC'
GROUP BY 1;

Partition Column Wrapped in a Function

-- BAD: disables partition pruning
WHERE DATE(hour_part) = '2026-03-01'
WHERE TIMESTAMP_TRUNC(hour_part, DAY) = '2026-03-01'

-- GOOD: direct range comparison
WHERE hour_part >= '2026-03-01 00:00:00 UTC'
  AND hour_part <  '2026-03-02 00:00:00 UTC'

SELECT * on f_syslog

SELECT * on f_syslog always scans all columns — including the large msg STRING column. Enumerate only the columns you need.

Testing Regex on f_syslog Instead of t_msg_sample

Always validate new regex rules against t_msg_sample first. Never use f_syslog as a live regex sandbox.

Inserting into r_msg_rules Without a run_order Gap Check

Always verify the intended run_order value is not already taken before inserting:

SELECT MAX(run_order) AS max_order FROM `overseer-d-011017.syslog.r_msg_rules`;

Tools & Commands

# Inspect schema
bq show --schema --format=prettyjson overseer-d-011017:syslog.f_syslog

# Preview 10 rows — no billing
bq head -n 10 overseer-d-011017:syslog.f_syslog

# Dry-run a query
bq query --use_legacy_sql=false --dry_run --project_id=overseer-d-011017 'YOUR_QUERY'

# Execute with a byte cap
bq query \
  --use_legacy_sql=false \
  --maximum_bytes_billed=1073741824 \
  --project_id=overseer-d-011017 \
  --format=prettyjson \
  'YOUR_QUERY'

# Count rules
bq query --use_legacy_sql=false --project_id=overseer-d-011017 \
  'SELECT COUNT(*) FROM `overseer-d-011017.syslog.r_msg_rules`'

# List tables in syslog dataset
bq ls --project_id=overseer-d-011017 syslog