Parsing and Analyzing Server Log Files

You have a CSV export of server log entries. Maybe you pulled them from CloudWatch, maybe someone ran a script that dumped the last 50,000 lines into a spreadsheet. Each row has a single column with a raw log line like:

2024-03-15 09:23:45 ERROR [auth-service] Failed login for user@example.com from 192.168.1.1

That is a wall of text. You cannot filter by log level, you cannot group by service, you cannot chart errors over time. You need to break this apart into structured columns first, and then you can actually analyze it.

Here is how to go from raw log lines to structured, filterable data in ExploreMyData using eight pipeline steps.

Step 1: Extract the timestamp

Click the green + in the Pipeline panel and select Extract Text from the Transform group. This operation pulls substrings out of a text column using patterns.

1. Source column: log_line (or whatever your raw column is called)
2. Method: regex
3. Pattern: \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}
4. New column name: timestamp_raw

This regex matches the standard datetime format at the start of each log line. The result is a new VARCHAR column containing just the timestamp string, like 2024-03-15 09:23:45.

Step 2: Extract the log level

Apply Extract Text again. The log level (ERROR, WARN, INFO, DEBUG) sits between the timestamp and the bracketed service name. You have two options:

• Regex: pattern (ERROR|WARN|INFO|DEBUG)
• Between delimiters: after the timestamp's trailing space and before the [ character

Either way, name the result log_level. The regex approach is more reliable here because it matches the known set of levels regardless of extra whitespace.

Step 3: Extract the service name

One more Extract Text. The service name is wrapped in square brackets: [auth-service]. Use the between-delimiters method:

1. Method: between delimiters
2. Start delimiter: [
3. End delimiter: ]
4. New column name: service

Now you have three new columns alongside the original log line: timestamp_raw, log_level, and service.

Step 4: Extract the message

The last piece is everything after the service name. One more Extract Text, this time with the after-delimiter method:

1. Source column: log_line
2. Method: after delimiter
3. Delimiter: a closing bracket ] followed by a single space (type both characters into the field)
4. New column name: message

After-delimiter returns everything following the first occurrence of the delimiter, so Failed login for user@example.com from 192.168.1.1 comes through clean. Including the space in the delimiter is what saves you a trim afterwards. If a line has no bracketed service at all, the method returns an empty string rather than NULL, which is worth knowing before you go looking for missing values.

Step 5: Convert the timestamp

The extracted timestamp is still a string. Select Convert Type from the Transform group:

1. Column: timestamp_raw
2. Target type: date

DuckDB's TRY_CAST() handles the conversion. Any malformed timestamps become NULL instead of causing an error. Once this is a proper TIMESTAMP, you can sort chronologically, filter by date ranges, and extract date parts.

Step 6: Extract the hour for time-of-day analysis

Select Extract Date Part from the Date group:

1. Source column: timestamp_raw
2. Part to extract: hour
3. New column name: hour_of_day

This gives you a number from 0 to 23. Now you can group by hour to see when errors spike. Are most failures happening at 2 AM during batch jobs? Or at 9 AM when users start logging in?

Step 7: Count the entries per level

Before you dig into individual errors, get the shape of the file. Counting rows per level is a Group & Aggregate job, from the Aggregate group. Top / Bottom Rows will not do it: that operation slices off N rows, it does not count anything.

1. Group by: log_level
2. Aggregations: function COUNT, column log_line

There is no output-name field. The alias is always the column plus the function in lowercase, so the count arrives as log_line_count. Swap log_level for service in the Group by, or add it as a second grouping column, to get the same count broken out per service.

One thing to be deliberate about: this step replaces the grid with the summary. Four rows is all you have afterwards, and the per-line detail is gone from that point in the chain. So read the counts and then delete the step before continuing, or keep the detail in one view and put the summary in a view of its own. The next step assumes you are back on the detail rows.

Step 8: Filter to errors only

Select Filter from the Filter & Sort group:

1. Column: log_level
2. Operator: equals
3. Value: ERROR

That leaves the 3,187 error entries from the count in Step 7, each with structured columns you can sort and group. Want to drill into a specific service? Add another filter for service = 'auth-service'.

Going further

Once the data is structured, you can branch your analysis in several directions:

• Use Pivot to count errors per service per hour
• Use Regex Capture on message to pull out IP addresses or email addresses into columns of their own
• Use Rolling Window for a rolling error rate (see below)
• Export the structured version as a Parquet file for faster re-analysis later

A rolling error rate, done properly

Do not reach for Window Function here. Its dropdown holds exactly six entries: ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG and RUNNING TOTAL. There is no COUNT, no SUM, no AVG. The operation you want is Rolling Window, also in the Aggregate group, which does have COUNT, SUM, AVG, MIN, MAX, COUNT DISTINCT, MEDIAN and STRING_AGG.

A rate needs a numerator you can average, so first add the flag with Add Column, named is_error:

CASE WHEN "log_level" = 'ERROR' THEN 1 ELSE 0 END

Then add Rolling Window with column is_error, function AVG, window size 100, and Order by timestamp_raw. Order by is required on this operation, not optional, which makes sense: a rolling window over unordered rows means nothing. The output name defaults to avg_is_error_rolling_100 and the generated SQL is:

AVG("is_error") OVER (ORDER BY "timestamp_raw" ROWS BETWEEN 99 PRECEDING AND CURRENT ROW)

Each row now carries the share of the last 100 log lines that were errors: 0.03 is background noise, 0.40 is an incident. Switch the function to SUM if you want the raw number of errors in the window rather than the share (COUNT would just hand you the size of the window, since is_error is never NULL), and add a Partition by on service to keep each service's window separate.

The full pipeline

1. Extract Text - regex for timestamp
2. Extract Text - regex for log level
3. Extract Text - between delimiters for service name
4. Extract Text - after delimiter for the message
5. Convert Type - timestamp_raw to TIMESTAMP
6. Extract Date Part - hour from timestamp_raw
7. Group & Aggregate - COUNT of log_line grouped by log_level
8. Filter - log_level = ERROR, on the detail rows

That is a blob of text turned into a table you can filter, group and chart, without grep, awk or a terminal full of half-remembered regex flags. Every step stays on screen and stays editable, and you can read the SQL each one generates whenever you want to check what it actually did.

Analyze your log files →