SQLite Tables Reference

In addition to the tables generated for each log format, lnav includes the following tables/views:

These extra tables provide useful information and can let you manipulate lnav’s internal state. You can get a dump of the entire database schema by executing the ‘.schema’ SQL command, like so:

;.schema

Note

The tables created by lnav are in the lnav_db database. The main SQLite database is left empty for your own use. If you end up creating any tables while processing your logs, you can use the ;.save path SQL command to save the main database to a SQLite database file.

environ

The environ table gives you access to the lnav process’ environment variables. You can SELECT, INSERT, and UPDATE environment variables, like so:

;SELECT * FROM environ WHERE name = 'SHELL'
 name   value
SHELL /bin/tcsh

;UPDATE environ SET value = '/bin/sh' WHERE name = 'SHELL'

Environment variables can be used to store simple values or pass values from lnav’s SQL environment to lnav’s commands. For example, the :open command will do variable substitution, so you can insert a variable named “FILENAME” and then open it in lnav by referencing it with “$FILENAME”:

;INSERT INTO environ VALUES ('FILENAME', '/path/to/file')
:open $FILENAME

fstat(<path|pattern>)

The fstat table-valued function provides access to the local file system. The function takes a file path or a glob pattern and returns the results of lstat(2) for the matching files. If the parameter is a pattern that matches nothing, no rows will be returned. If the parameter is a path for a non-existent file, a row will be returned with the error column set and the stat columns as NULL. To read the contents of a file, you can SELECT the hidden data column.

all_logs

The all_logs table lets you query the format derived from the lnav log message parser that is used to automatically extract data, see Extracting Data for more details.

all_metrics

The all_metrics table is a long-format view over every metrics_log file that lnav currently has open. Each (file, row, column) combination produces one virtual row, so a single query can scan metric values across multiple files without having to know which file each metric came from.

The columns are as follows:

log_line:

The line number in the LOG view of the sample row. The value is shared across all metrics at that timestamp, including sibling files whose rows were folded into the visible line.

log_time:

The timestamp for the metric sample.

log_path:

The path of the file the sample came from.

source:

The file stem shown above the sample’s column in the focused-row overlay.

metric:

The column name from the source file.

value:

The parsed numeric value. Integers pass through as INTEGER; floats and unit-suffixed cells (e.g. 20.0KB) are REAL.

raw_value:

(hidden) The original cell text from the file. Collated with measure_with_units, so ORDER BY raw_value sorts by magnitude rather than lexicographically.

log_mark:

(hidden) True when the sample’s visible row is user-marked. Marking a metric row fans the mark out to every sibling sample at that timestamp.

;SELECT metric, count(*) AS n, min(value) AS lo, max(value) AS hi
     FROM all_metrics
     GROUP BY metric

;SELECT log_time, source, metric, value
     FROM all_metrics
     WHERE metric = 'cpu_pct'
     ORDER BY log_time

all_opids

The all_opids table contains information about all opids that were found in the log files or set via the log_opid column on the log vtables. The information in this table is the same as available through the TIMELINE view. The description column can be SET in an UPDATE statement to customize the description shown in the timeline.

all_thread_ids

The all_thread_ids table contains information about all the thread identifiers that were found in logs. Log formats can specify which field is a thread identifier with the thread-id-field property.

all_thread_opids

The all_thread_opids table has a row for each thread and operation ID pair found in the logs: the operations each thread logged messages for, with the first and last time it did and how many messages, errors, and warnings it logged in each. A thread’s messages without an operation ID have a row with a NULL opid. It’s built from the thread and operation IDs recorded when the logs were indexed, so it’s quick to query even for large logs, and the rows for given threads can be looked up with thread_id = ? or thread_id IN (...). Like all_thread_ids, it doesn’t follow the filters. For example, to find the operations a thread took part in:

;SELECT opid, earliest, duration, total FROM all_thread_opids
     WHERE thread_id = 'worker-1' ORDER BY earliest

http_status_codes

The http_status_codes table is a handy reference that can be used to turn HTTP status codes into human-readable messages.

regexp_capture(<string>, <regex>)

The regexp_capture() table-valued function applies the regular expression to the given string and returns detailed results for the captured portions of the string.