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.

lnav_events¶

The lnav_events table allows you to react to events that occur while lnav is running using SQLite triggers. For example, when a file is opened, a row is inserted into the lnav_events table that contains a timestamp and a JSON object with the event ID and the path of the file. The following columns are available in this table:

ts:

The timestamp of the event.

content:

A JSON object that contains the event information. See the Reference for more information about the types of events that are available.

lnav_file¶

The lnav_file table allows you to examine and perform limited updates to the metadata for the files that are currently loaded into lnav. The following columns are available in this table:

device:

The device the file is stored on.

inode:

The inode for the file on the device.

filepath:

If this is a real file, it will be the absolute path. Otherwise, it is a symbolic name. If it is a symbolic name, it can be UPDATEd so that this file will be considered when saving and loading session information.

mimetype:

The detected MIME type of the file.

content_id:

The hash of some unique content in the file.

format:

The log file format for the file.

lines:

The number of lines in the file.

time_offset:

The millisecond offset for timestamps. This column can be UPDATEd to change the offset of timestamps in the file.

options_path:

Options can be applied to files based on a path or glob pattern. If this file matches a set of options, the matching path/pattern is available in this column and the actual options themselves are in the options column.

options:

The options that are applicable to this file. Currently, the only options available are for the timezone set by the :set-file-timezone command.

lnav_file_metadata¶

The lnav_file_metadata table gives access to metadata associated with a loaded file. Currently,

filepath:

The path to the file.

descriptor:

A descriptor that identifies the source of the metadata. The following descriptors are supported:

net.zlib.gzip.header:

The header on a gzipped file. The content is a JSON object with the following properties:

name:

The original name of the file.

mtime:

The last modified time of the file when it was compressed.

comment:

A text comment associated with the file.

net.daringfireball.markdown.frontmatter:

The frontmatter on a markdown file. If the frontmatter is delimited by three dashes (---), the mimetype will be application/yaml. If the frontmatter is delimited by three pluses (+++) the mimetype will be application/toml.

mimetype:

The MIME type of the metadata.

content:

The metadata itself.

lnav_log_breakpoints¶

The lnav_log_breakpoints table allows you to view and manage breakpoints set on log messages. Breakpoints mark log messages that share a particular source file location or message schema, making it easy to navigate between related log lines using the F7 and F8 keys.

You can SELECT, INSERT, UPDATE, and DELETE breakpoints through this table. The columns in the table are as follows:

schema_id:: The schema identifier for the breakpoint. This value matches the log_msg_schema column in the all_logs table.
description:: A human-readable description of the breakpoint (e.g. format_name:file.cc:42).
type:: The source of the schema ID, either src_location or message_schema.
enabled:: Indicates whether the breakpoint is active (1 or 0).

;SELECT * FROM lnav_log_breakpoints

;UPDATE lnav_log_breakpoints SET enabled = 0 WHERE description LIKE '%main.cc%'

;DELETE FROM lnav_log_breakpoints WHERE description LIKE '%test%'

lnav_user_notifications¶

The lnav_user_notifications table allows you to display a custom message in the top-right corner of the UI. For example, to display “Hello, World!”, you can enter:

;REPLACE INTO lnav_user_notifications (message) VALUES ('Hello, World!')

There are additional columns to have finer control of what is displayed and when:

id:

The unique ID for the message, defaults to “org.lnav.user”. This is the primary key for the table, so more than one type of message is not allowed.

priority:

The priority of the message. Higher priority messages will be displayed until they are cleared or are expired.

created:

The time the message was created.

expiration:

The time when the message should expire or NULL if it should not automatically expire.

views:

A JSON array of view names where the message is applicable or NULL if the message should be shown in all views.

message:

The message itself.

This table will most likely be used in combination with Events (v0.11.0+) and the lnav_views_echo table.

lnav_views¶

The lnav_views table allows you to SELECT and UPDATE information related to lnav’s “views” (e.g. log, text, …). The following columns are available in this table:

name:: The name of the view.
top:: The line number at the top of the view. This value can be UPDATEd to move the view to the given line.
left:: The left-most column number to display. This value can be UPDATEd to move the view left or right.
height:: The number of lines that are displayed on the screen.
inner_height:: The number of lines of content being displayed.
top_time:: The timestamp of the top line in the view or NULL if the view is not time-based. This value can be UPDATEd to move the view to the given time.
top_file:: The file the top line in the view is from.
paused:: Indicates if the view is paused and will not load new data.
search:: The search string for this view. This value can be UPDATEd to initiate a text search in this view.
filtering:: Indicates if the view is applying filters.
movement:: The movement mode, either ‘top’ or ‘cursor’.
top_meta:: A JSON object that contains metadata related to the top line in the view.
selection:: The number of the line that is focused for selection.
options:: A JSON object that contains optional settings for this view.

lnav_views_echo¶

The lnav_views_echo table is a real SQLite table that you can create TRIGGERs on in order to react to users moving around in a view.

Note

The table is periodically updated to reflect the current state of the views. The changes are not performed immediately after the user action.

lnav_view_files¶

The lnav_view_files table provides access to details about the files displayed in a particular view. The main purpose of this table is to allow you to programmatically control which files are shown / hidden in the view. The following columns are available in this table:

view_name:: The name of the view.
filepath:: The file’s path.
visible:: Determines whether the file is visible in the view. This column can be changed using an UPDATE statement to hide or show the file.

lnav_view_stack¶

The lnav_view_stack table allows you to SELECT and DELETE from the stack of lnav “views” (e.g. log, text, …). The following columns are available in this table:

name:

The name of the view.

lnav_view_filters¶

The lnav_view_filters table allows you to manipulate the filters in the lnav views. The following columns are available in this table:

view_name:

The name of the view the filter is applied to.

filter_id:

The filter identifier. This will be assigned on insertion.

enabled:

Indicates whether this filter is enabled or disabled.

type:

The type of filter, either ‘in’ or ‘out’.

pattern:

The regular expression to filter on.

This table supports SELECT, INSERT, UPDATE, and DELETE on the table rows to read, create, update, and delete filters for the views.

lnav_view_filter_stats¶

The lnav_view_filter_stats table allows you to get information about how many lines matched a given filter. The following columns are available in this table:

view_name:

The name of the view.

filter_id:

The filter identifier.

hits:

The number of lines that matched this filter.

This table is read-only.

lnav_view_filters_and_stats¶

The lnav_view_filters_and_stats view joins the lnav_view_filters table with the lnav_view_filter_stats table into a single view for ease of use.

lnav_top_view¶

The lnav_top_view view returns the row for the top view on the view stack.

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_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.

lnav_focused_msg¶

The lnav_focused_msg view returns the row for the focused log message from the all_logs table.

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.