Log Parsing and Report Generation with grep, awk, and sed

Why Log Parsing Matters for Database Administrators

Database logs are the primary evidence trail for performance problems, security incidents, and compliance audits. PostgreSQL writes messages to its server log with severity labels such as `LOG`, `WARNING`, `ERROR`, and `FATAL`. Oracle writes similar events to its alert log. A data-governance audit — the kind covered in the DAMA CDMP Body of Knowledge under Data Quality and Data Security — may require you to prove how many errors occurred in a given window, confirm that personally identifiable information (PII) was not exposed in log files, and show which queries are degrading system performance. Manual review of a 10,000-line log file is impractical. Shell tools — `grep`, `awk`, and `sed` — let you automate that work reliably and repeatably.

grep: Searching and Filtering

`grep` reads lines from a file or standard input and prints those that match a pattern. The `-E` flag enables extended regular expressions (ERE), which support alternation (`|`), quantifiers (`+`, `?`), and grouping without backslashes.

```bash

grep -E 'ERROR|FATAL' /var/log/postgresql/postgresql.log

```

This command prints every line containing either `ERROR` or `FATAL`. Useful flags:

• `-c` — print only the count of matching lines
• `-i` — case-insensitive match
• `-n` — prefix output with line numbers
• `-o` — print only the matched portion (not the whole line)

Piping `grep` output into another tool is a core pattern:

```bash

grep -E 'ERROR|FATAL' postgresql.log | wc -l

```

awk: Field-Based Processing and Counting

`awk` treats each input line as a record and splits it into fields by a delimiter (default: whitespace). Fields are accessed as `$1`, `$2`, and so on. `awk` programs follow the pattern `condition { action }`.

PostgreSQL log lines begin with a timestamp such as `2024-03-15 14:32:07.441 UTC`. The hour is the second colon-delimited segment of the time field. To count errors by hour:

```bash

grep -E 'ERROR|FATAL' postgresql.log | \

awk '{split($2, t, ":"); hour[t[1]]++} \

END {for (h in hour) print h, hour[h]}' | \

sort

```

Here `split($2, t, ":")` breaks the time field into an array `t`; `t[1]` is the hour. The associative array `hour` accumulates counts. The `END` block runs after all lines are processed.

For CSV input (such as a `pg_stat_statements` export), set the field separator with `-F','`:

```bash

awk -F',' '{sum[$1] += $5; count[$1]++} \

END {for (q in sum) print q, sum[q]/count[q]}' stats.csv

```

To produce a top-10 table, pipe the awk output to `sort -t' ' -k2 -rn | head -10`.

sed: Stream Editing and Redaction

`sed` applies editing commands to each line of input. The substitution command `s/pattern/replacement/flags` is most common. The `g` flag replaces all occurrences on a line.

To redact IPv4 addresses — a privacy-by-design measure aligned with DAMA data-governance principles — use:

```bash

sed -E 's/([0-9]{1,3}\.){3}[0-9]{1,3}/[REDACTED]/g' postgresql.log

```

Breaking down the regex:

• `[0-9]{1,3}` — one to three digits
• `\.` — a literal dot
• `{3}` — exactly three repetitions of the digit-dot group
• `[0-9]{1,3}` — final octet

Redirecting to a new file preserves the original:

```bash

sed -E 's/([0-9]{1,3}\.){3}[0-9]{1,3}/[REDACTED]/g' \

postgresql.log > postgresql_redacted.log

```

Never overwrite your only copy of a log file. Always redirect to a new file or use `sed -i` only after testing on a copy.

Automating with cron

`cron` schedules commands to run automatically. The user's crontab is edited with `crontab -e`. A weekly job that runs every Monday at 06:00 looks like:

```

0 6 1 /home/dba/scripts/slow_query_report.awk /var/log/pg_stats.csv >> /reports/weekly_slow.txt

```

Make the awk script executable (`chmod +x`) and include a shebang line (`#!/usr/bin/awk -f`) so the operating system knows how to run it directly.

Safety and Data-Handling Considerations

Log files frequently contain sensitive data: usernames, partial query text, and IP addresses. Before sharing any log excerpt outside your team:

1. Redact PII using the `sed` pattern above or an equivalent tool.

2. Restrict file permissions — log files should be readable only by the `postgres` OS user and authorized DBAs (`chmod 640`).

3. Store reports on encrypted volumes when they are retained for audit purposes.

4. Do not run parsing scripts as root unless required; use the principle of least privilege.

5. Validate scripts on a copy of production logs in a non-production environment before scheduling them as cron jobs.

These practices align with the DAMA CDMP domains of Data Security and Data Quality, and they also reflect controls expected by Oracle and Microsoft database certification exam content on auditing and access management.

Putting It Together: The Workflow

1. `grep` filters the raw log down to relevant lines.

2. `awk` aggregates and computes statistics.

3. `sed` sanitizes output before distribution.

4. Redirection (`>`, `>>`) or a cron job delivers the finished report.

This three-tool pipeline is portable across any Linux system where PostgreSQL or Oracle runs, which is why it appears in DBA job descriptions and aligns with the command-line skills tested in EnterpriseDB and Microsoft DP-300 exam objectives.