Observe MCP Server

--- name: field-extraction-parsing description: Extract structured fields from unstructured log data using OPAL parsing functions. Covers extract_regex() for pattern matching with type casting, split() for delimited data, parse_json() for JSON logs, and JSONPath for navigating parsed structures. Use when you need to convert raw log text into queryable fields for analysis, filtering, or aggregation. --- # Field Extraction and Parsing ## Overview This skill covers converting unstructured log data into structured, queryable fields using OPAL's extraction and parsing functions. **Core Functions**: - `extract_regex()` - Extract fields using regex patterns with named capture groups - `split()` / `split_part()` - Split delimited strings - `parse_json()` - Parse JSON strings into objects - JSONPath / Array access - Navigate parsed structures ## When to Use This Skill Use field extraction when you need to: - **Parse log formats** - Extract timestamp, level, message from structured logs - **Extract identifiers** - Pull out request IDs, trace IDs, user IDs for correlation - **Parse metrics from text** - Extract numbers, durations, status codes from logs - **Structure unstructured data** - Convert free-form text into queryable columns - **Parse embedded JSON** - Extract fields from JSON-formatted log messages ## Function 1: extract_regex() The most powerful extraction function. Uses POSIX regex with named capture groups to create new columns. ### Syntax ```opal extract_regex source_column, /(?P<field_name>pattern)/ extract_regex source_column, /(?P<field_name::typecast>pattern)/ ``` **Key Features**: - Named capture groups create new columns: `(?P<column_name>pattern)` - Type casting in capture group: `(?P<name::int64>pattern)` - Multiple captures in one regex - Regex uses forward slashes `/pattern/` not quotes ### Supported Type Casts - `string` (default) - `int64`, `float64` - `parse_isotime` (for ISO timestamps) - `duration`, `duration_ms`, `duration_sec`, `duration_min`, `duration_hr` - `parse_json` ### Pattern 1: Extract Timestamp and Log Level **Use Case**: Parse structured application logs **Log Format**: `[2025-11-16 01:58:12,204] INFO [Component] Message...` ```opal filter container = "kafka" extract_regex body, /\[(?P<log_time>[\d\-: ,]+)\] (?P<level>\w+) / ``` **Creates Columns**: - `log_time`: "2025-11-16 02:02:45,266" - `level`: "INFO" **Use For**: Java logs, Kafka logs, structured application logs --- ### Pattern 2: Extract with Type Casting **Use Case**: Extract numeric values as integers **Log Format**: `[SnapshotEmitter id=1] Message...` ```opal extract_regex body, /\[(?P<component>\w+) id=(?P<component_id::int64>\d+)\]/ ``` **Creates Columns**: - `component`: "SnapshotEmitter" (string) - `component_id`: 1 (int64) **Key Point**: `::int64` casts the extracted value to integer immediately --- ### Pattern 3: Extract HTTP Request Details **Use Case**: Parse access log patterns **Log Format**: `GET /api/users/123 200 15ms` ```opal extract_regex body, /(?P<method>\w+) (?P<path>[\w\/\-\.]+) (?P<status::int64>\d{3}) (?P<duration_ms::int64>\d+)ms/ ``` **Creates Columns**: - `method`: "GET" - `path`: "/api/users/123" - `status`: 200 (int64) - `duration_ms`: 15 (int64) **Use For**: Nginx, Apache, application access logs --- ### Pattern 4: Extract Request ID for Correlation **Use Case**: Pull out trace/request IDs for distributed tracing **Log Format**: `request_id=GHhaU0_7TcVSXpICZ9lh [info] GET /api` ```opal extract_regex body, /request_id=(?P<request_id>[\w\-]+)/ ``` **Creates Columns**: - `request_id`: "GHhaU0_7TcVSXpICZ9lh" **Then correlate**: ```opal extract_regex body, /request_id=(?P<request_id>[\w\-]+) \[info\] (?P<method>\w+) (?P<path>[\w\/]+)/ statsby count(), group_by(request_id, method, path) ``` **Use For**: Request correlation, distributed tracing, debugging user sessions --- ### Pattern 5: Extract Key=Value Pairs **Use Case**: Parse structured key=value log formats **Log Format**: `user=john action=login result=success duration=150ms` ```opal extract_regex body, /user=(?P<user>\w+) action=(?P<action>\w+) result=(?P<result>\w+) duration=(?P<duration_ms::int64>\d+)ms/ ``` **Creates Columns**: - `user`: "john" - `action`: "login" - `result`: "success" - `duration_ms`: 150 (int64) **Use For**: Audit logs, security logs, custom application logs --- ### Pattern 6: Extract IP Addresses **Use Case**: Parse network information from logs **Log Format**: `Connection from IP=192.168.1.100 to Destination="10.0.0.5"` ```opal extract_regex body, /IP=(?P<source_ip>[\d\.]+) to Destination="(?P<dest_ip>[\d\.]+)"/ ``` **Creates Columns**: - `source_ip`: "192.168.1.100" - `dest_ip`: "10.0.0.5" **Use For**: Network logs, firewall logs, connection tracking --- ## Function 2: split() and split_part() Split delimited strings into arrays or extract specific parts. ### Syntax ```opal split(string, delimiter) # Returns JSON array split_part(string, delimiter, N) # Returns Nth part (1-based) ``` **Key Differences**: - `split()` returns entire array: `["a", "b", "c"]` - `split_part()` returns single element (1-based indexing) - Negative indices in `split_part()` count from end: `-1` = last ### Pattern 7: Split IP Address into Octets **Use Case**: Parse IP address components ```opal extract_regex body, /IP=(?P<ip>[\d\.]+)/ make_col octets:split(ip, ".") make_col first_octet:split_part(ip, ".", 1) make_col last_octet:split_part(ip, ".", -1) ``` **Results**: - `ip`: "95.217.183.1" - `octets`: `["95", "217", "183", "1"]` - `first_octet`: "95" - `last_octet`: "1" **Use For**: Network analysis, IP classification --- ### Pattern 8: Parse Path Components **Use Case**: Extract parts of file paths or URLs ```opal make_col path_parts:split("/var/log/app/error.log", "/") make_col filename:split_part("/var/log/app/error.log", "/", -1) make_col directory:split_part("/var/log/app/error.log", "/", -2) ``` **Results**: - `path_parts`: `["", "var", "log", "app", "error.log"]` - `filename`: "error.log" - `directory`: "app" **Use For**: File path analysis, URL parsing --- ### Pattern 9: Parse CSV-Like Data **Use Case**: Extract fields from comma-separated values in logs ```opal extract_regex body, /data:(?P<csv_data>[\w,]+)/ make_col fields:split(csv_data, ",") make_col field1:split_part(csv_data, ",", 1) make_col field2:split_part(csv_data, ",", 2) make_col field3:split_part(csv_data, ",", 3) ``` **Use For**: Legacy systems, CSV exports in logs --- ## Function 3: parse_json() Parse JSON strings into queryable objects. ### Syntax ```opal parse_json(json_string) ``` **Returns**: OPAL object that can be accessed with JSONPath ### Pattern 10: Parse JSON from Logs **Use Case**: Extract fields from JSON-formatted log messages **Log Format**: `MetricsExporter {"kind": "exporter", "data_type": "metrics", "metrics": 23, "data points": 61}` ```opal filter body ~ /MetricsExporter/ extract_regex body, /MetricsExporter.(?P<json_data>\{.*\})/ make_col parsed:parse_json(json_data) ``` **Result**: - `json_data`: `{"kind": "exporter", "data_type": "metrics", ...}` (string) - `parsed`: `{"kind": "exporter", "data_type": "metrics", ...}` (object) **Next**: Access fields using JSONPath (see below) --- ## Function 4: JSONPath and Array Access Navigate parsed JSON objects and arrays. ### Syntax ```opal object.field_name # Simple field object."field with spaces" # Quoted for special chars array[0] # Zero-based array indexing object.nested.field # Nested access ``` **Critical**: Field names with spaces or special characters MUST be quoted ### Pattern 11: Access JSON Fields **Use Case**: Extract specific fields from parsed JSON ```opal extract_regex body, /MetricsExporter.(?P<json_data>\{.*\})/ make_col parsed:parse_json(json_data) make_col data_type:string(parsed."data_type") make_col metrics_count:int64(parsed.metrics) make_col data_points:int64(parsed."data points") ``` **Results**: - `data_type`: "metrics" (string) - `metrics_count`: 23 (int64) - `data_points`: 61 (int64) **Key Points**: - Use quotes for `"data_type"` and `"data points"` (special chars/spaces) - Type cast with `int64()`, `string()`, etc. --- ### Pattern 12: Array Access with Split **Use Case**: Access specific array elements ```opal extract_regex body, /IP=(?P<ip>[\d\.]+)/ make_col parts:split(ip, ".") make_col first_octet:int64(parts[0]) make_col second_octet:int64(parts[1]) make_col third_octet:int64(parts[2]) make_col fourth_octet:int64(parts[3]) ``` **Key Point**: Array indexing is **zero-based** (`[0]` = first element) **Difference from split_part()**: - `split_part()`: 1-based (first element = 1) - Array `[N]`: 0-based (first element = 0) --- ## Complete Examples ### Example 1: Parse Application Errors **Goal**: Extract error codes and messages from application logs ```opal filter body ~ /ERROR/ extract_regex body, /\[(?P<log_time>[\d\-: ,]+)\] (?P<level>\w+) \[(?P<component>\w+)\] (?P<error_code>\w+): (?P<message>.*)/ statsby error_count:count(), sample:any(message), group_by(error_code, component) sort desc(error_count) ``` **Use Case**: Error analysis, identifying most common errors --- ### Example 2: Parse and Analyze HTTP Status Codes **Goal**: Analyze HTTP response codes and response times ```opal filter body ~ /\d{3} \d+ms/ extract_regex body, /(?P<method>\w+) (?P<path>[\w\/\-\.]+) (?P<status::int64>\d{3}) (?P<duration_ms::int64>\d+)ms/ make_col status_class:if(status >= 500, "5xx", if(status >= 400, "4xx", if(status >= 300, "3xx", if(status >= 200, "2xx", "other")))) statsby request_count:count(), avg_duration:avg(duration_ms), p95_duration:percentile(duration_ms, 0.95), group_by(status_class, path) sort desc(request_count) ``` **Use Case**: Performance analysis, identifying slow endpoints --- ### Example 3: Correlate Requests Across Services **Goal**: Track requests through multiple services using request_id ```opal filter body ~ /request_id=/ extract_regex body, /request_id=(?P<request_id>[\w\-]+) \[info\] (?P<method>\w+) (?P<path>[\w\/]+)/ make_col service:string(resource_attributes."k8s.deployment.name") statsby services:count_distinct(service), total_logs:count(), group_by(request_id) filter services > 1 sort desc(services) ``` **Use Case**: Distributed tracing, identifying cross-service requests --- ### Example 4: Parse JSON Metrics and Alert **Goal**: Extract metrics from JSON logs and find anomalies ```opal filter body ~ /MetricsExporter/ extract_regex body, /MetricsExporter.(?P<json_data>\{.*\})/ make_col parsed:parse_json(json_data) make_col data_points:int64(parsed."data points") make_col metrics_count:int64(parsed.metrics) filter data_points > 200 or metrics_count > 50 statsby high_count:count(), avg_points:avg(data_points), group_by(metrics_count) ``` **Use Case**: Monitoring metric collection, detecting unusual activity --- ### Example 5: Network Traffic Analysis **Goal**: Analyze network connections by IP range ```opal filter body ~ /IP=/ extract_regex body, /IP=(?P<ip>[\d\.]+)/ make_col octets:split(ip, ".") make_col network:split_part(ip, ".", 1) make_col is_private:if(network = "10" or network = "172" or network = "192", true, false) statsby connection_count:count(), unique_ips:count_distinct(ip), group_by(is_private, network) sort desc(connection_count) ``` **Use Case**: Security analysis, network traffic patterns --- ## Decision Tree: Which Function to Use? ``` Need to extract data from logs? │ ├─ Data has clear pattern (timestamp, IP, ID) │ └─ Use extract_regex() with named captures │ ├─ Data is delimited (CSV, path, separated values) │ ├─ Need all parts → Use split() │ └─ Need specific part → Use split_part() │ ├─ Data is JSON formatted │ ├─ Extract JSON first → extract_regex() │ ├─ Parse JSON → parse_json() │ └─ Access fields → JSONPath (object.field) │ └─ Data is mixed (pattern + delimited + JSON) └─ Combine: extract_regex() → split() → parse_json() ``` --- ## Common Mistakes and Solutions ### Mistake 1: Using Reserved Column Names **ERROR**: ```opal extract_regex body, /(?P<timestamp>[\d\-:]+)/ # Error: regex capture group 1 overwrites 'valid from' column "timestamp" ``` **FIX**: ```opal extract_regex body, /(?P<log_time>[\d\-:]+)/ ``` **Reserved names**: `timestamp`, `valid_from`, `valid_to`, `_c_bucket` --- ### Mistake 2: Forgetting Timestamp in pick_col **ERROR**: ```opal extract_regex body, /(?P<field>\w+)/ pick_col field # Error: need to pick 'valid from' column "timestamp" ``` **FIX**: ```opal extract_regex body, /(?P<field>\w+)/ pick_col timestamp, field ``` --- ### Mistake 3: Wrong Regex Delimiters **ERROR**: ```opal extract_regex body, "pattern" # Quotes don't work ``` **FIX**: ```opal extract_regex body, /pattern/ # Forward slashes required ``` --- ### Mistake 4: Tab Character in Regex **ERROR**: ```opal extract_regex body, /field\t(?P<value>.*)/ # Error: Unknown function 't()' ``` **FIX**: ```opal extract_regex body, /field.(?P<value>.*)/ # Use . for any char # OR extract_regex body, /field[\t ](?P<value>.*)/ # Character class ``` --- ### Mistake 5: JSONPath Without Quotes **ERROR**: ```opal string(parsed.data points) # Syntax error (space in name) ``` **FIX**: ```opal string(parsed."data points") # Quote field names with spaces ``` --- ### Mistake 6: Confusing split_part() vs Array Indexing **Remember**: - `split_part()` is **1-based** (first element = 1) - Array `[N]` is **0-based** (first element = 0) ```opal make_col parts:split("a.b.c", ".") make_col using_split_part:split_part("a.b.c", ".", 1) # "a" (1-based) make_col using_array:parts[0] # "a" (0-based) ``` --- ## Error Handling ### Regex Non-Matches When regex doesn't match a log line: - Extracted columns are **null** (not an error) - Original data is preserved - Filter nulls if needed: `filter extracted_field != null` ```opal extract_regex body, /user=(?P<user>\w+)/ # Logs without "user=" will have user=null filter user != null # Keep only matched logs ``` --- ### Invalid JSON When `parse_json()` receives invalid JSON: - Returns **null** (not an error) - Check before accessing: `filter parsed != null` ```opal make_col parsed:parse_json(maybe_json) filter parsed != null make_col field:string(parsed.field_name) ``` --- ### Array Out of Bounds When accessing `array[999]` on a smaller array: - Returns **null** (not an error) - No exception thrown ```opal make_col parts:split("a.b.c", ".") # ["a","b","c"] make_col safe:parts[0] # "a" make_col oob:parts[999] # null (no error) ``` --- ## Performance Tips ### 1. Filter Before Extracting Extract from relevant logs only: ```opal # GOOD - Filter first filter body ~ /ERROR/ extract_regex body, /ERROR: (?P<error_code>\w+)/ # BAD - Extract from all logs extract_regex body, /ERROR: (?P<error_code>\w+)/ filter error_code != null ``` --- ### 2. Single Regex with Multiple Captures One regex is faster than multiple: ```opal # GOOD - Single regex extract_regex body, /\[(?P<time>[\d: ,]+)\] (?P<level>\w+) (?P<msg>.*)/ # BAD - Multiple regexes extract_regex body, /\[(?P<time>.*)\]/ extract_regex body, /\] (?P<level>.*) / extract_regex body, /(?P<msg>.*)/ ``` --- ### 3. Anchor Patterns When Possible Anchored patterns (^, $) perform better: ```opal # GOOD - Anchored extract_regex body, /^(?P<timestamp>[\d\-:]+) / # SLOWER - Unanchored (searches entire string) extract_regex body, /(?P<timestamp>[\d\-:]+) / ``` --- ### 4. Only Parse JSON When Needed ```opal # GOOD - Filter, then parse filter body ~ /\{.*"error"/ make_col parsed:parse_json(body) # BAD - Parse everything make_col parsed:parse_json(body) filter parsed != null ``` --- ## Related Skills - **filtering-event-datasets** - Text search and filtering before extraction - **aggregating-event-datasets** - Aggregating extracted fields with statsby - **investigating-textual-data** - Error analysis workflows using extraction - **analyzing-text-patterns** - Pattern discovery to identify what to extract ## Key Takeaways 1. **extract_regex()** is most powerful - supports type casting and multiple captures 2. **Forward slashes** required for regex: `/pattern/` not `"pattern"` 3. **Type cast in capture group**: `(?P<name::int64>pattern)` for immediate conversion 4. **split_part()** is 1-based, array `[N]` is 0-based 5. **Quote JSONPath fields** with spaces or special characters 6. **Reserved names**: Avoid `timestamp`, `valid_from`, `valid_to` 7. **Nulls not errors**: Non-matches return null, not exceptions 8. **Filter before extract**: Better performance When in doubt about regex syntax or parsing functions, use `learn_observe_skill("OPAL extract_regex")` for official documentation.