SQLite Interface

Log analysis in lnav can be done using the SQLite interface. Log messages can be accessed via virtual tables that are created for each file format. The tables have the same name as the log format and each message is its own row in the table. For example, given the following log message from an Apache access log:

127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326

These columns would be available for its row in the access_log table:

log_line	log_part	log_time	log_idle_msecs	log_level	log_mark	log_comment	log_tags	log_filters	c_ip	cs_method	cs_referer	cs_uri_query	cs_uri_stem	cs_user_agent	cs_username	cs_version	sc_bytes	sc_status
0	<NULL>	2000-10-10 13:55:36.000	0	info	1	<NULL>	<NULL>	<NULL>	127.0.0.1	GET	<NULL>	<NULL>	/apache_pb.gif	<NULL>	frank	HTTP/1.0	2326	200

Note

Some columns are hidden by default to reduce the amount of noise in results, but they can still be accessed when explicitly used. The hidden columns are: log_path, log_text, log_body, and log_raw_text.

You can activate the SQL prompt by pressing the ; key. At the prompt, you can start typing in the desired SQL statement and/or double-tap TAB to activate auto-completion. A help window will appear above the prompt to guide you in the usage of SQL keywords and functions.

Screenshot of the online help for the SQL prompt.

Screenshot of the online help for the group_concat() function.

A simple query to perform on an Apache access log might be to get the average and maximum number of bytes returned by the server, grouped by IP address:

;SELECT c_ip, avg(sc_bytes), max(sc_bytes) FROM access_log GROUP BY c_ip

After pressing Enter, SQLite will execute the query using lnav's virtual table implementation to extract the data directly from the log files. Once the query has finished, the main window will switch to the DB view to show the results. Press q to return to the log view and press v to return to the log view. If the SQL results contain a log_line column, you can press to Shift + V to switch between the DB view and the log

Screenshot of the SQL results view.

The DB view has the following display features:

Column headers stick to the top of the view when scrolling.
A stacked bar chart of the numeric column values is displayed underneath the rows. Pressing TAB will cycle through displaying no columns, each individual column, or all columns.
JSON columns in the top row can be pretty-printed by pressing p. The display will show the value and JSON-Pointer path that can be passed to the `jget`_ function.

Log Tables

Each log format has its own database table that can be used to access log messages that match that format. The table name is the same as the format name, for example, the syslog_log format will have a table that is also named syslog_log. There is also an all_logs table that provides access to all messages from all formats.

Note

Only the displayed log messages are reflected in the SQLite interface. Any log messages that have been filtered out are not accessible.

The columns in the log tables are made up of several builtins along with the values captured by the log format specification. Use the .schema command in the SQL prompt to examine a dump of the current database schema.

The following columns are builtin and included in a SELECT *:

log_line: The line number for the message in the log view.

log_part: The partition the message is in. This column can be changed by an UPDATE or the :ref:`:parition-name<partition_name>` command.

log_time: The adjusted timestamp for the log message. This time can differ from the log message's time stamp if it arrived out-of-order and the log format expects log files to be time-ordered.

log_actual_time: The log messages original timestamp in the file.

log_idle_msecs: The difference in time between this messages and the previous. The unit of time is milliseconds.

log_level: The log message level.

log_mark: True if the log message was marked by the user.

log_comment: The comment for the message. This column can be changed by an UPDATE or the :ref:`:comment<comment>` command.

log_tags: A JSON list of tags for the message. This column can be changed by an UPDATE or the :ref:`:tag<tag>` command.

log_filters: A JSON list of filter IDs that matched this message

log_line:	The line number for the message in the log view.
log_part:	The partition the message is in. This column can be changed by an `UPDATE` or the :ref:`:parition-name<partition_name>` command.
log_time:	The adjusted timestamp for the log message. This time can differ from the log message's time stamp if it arrived out-of-order and the log format expects log files to be time-ordered.
log_actual_time:	The log messages original timestamp in the file.
log_idle_msecs:	The difference in time between this messages and the previous. The unit of time is milliseconds.
log_level:	The log message level.
log_mark:	True if the log message was marked by the user.
log_comment:	The comment for the message. This column can be changed by an `UPDATE` or the :ref:`:comment<comment>` command.
log_tags:	A JSON list of tags for the message. This column can be changed by an `UPDATE` or the :ref:`:tag<tag>` command.
log_filters:	A JSON list of filter IDs that matched this message

The following columns are builtin and are hidden, so they will not be included in a SELECT *:

log_time_msecs: The adjusted timestamp for the log message as the number of milliseconds from the epoch. This column can be more efficient to use for time-related operations, like :ref:`timeslice()<timeslice>`.

log_path: The path to the log file this message is from.

log_text: The full text of the log message.

log_body: The body of the log message.

log_raw_text: The raw text of this message from the log file. In this case of JSON and CSV logs, this will be the exact line of JSON-Line and CSV text from the file.

log_time_msecs:	The adjusted timestamp for the log message as the number of milliseconds from the epoch. This column can be more efficient to use for time-related operations, like :ref:`timeslice()<timeslice>`.
log_path:	The path to the log file this message is from.
log_text:	The full text of the log message.
log_body:	The body of the log message.
log_raw_text:	The raw text of this message from the log file. In this case of JSON and CSV logs, this will be the exact line of JSON-Line and CSV text from the file.

Extensions

To make it easier to analyze log data from within lnav, there are several built-in extensions that provide extra functions and collators beyond those provided by SQLite. The majority of the functions are from the extensions-functions.c file available from the sqlite.org web site.

Tip

You can include a SQLite database file on the command-line and use lnav's interface to perform queries. The database will be attached with a name based on the database file name.

Commands

A SQL command is an internal macro implemented by lnav.

.schema - Open the schema view. This view contains a dump of the schema for the internal tables and any tables in attached databases.
.msgformats - Executes a canned query that groups and counts log messages by the format of their message bodies. This command can be useful for quickly finding out the types of messages that are most common in a log file.

Variables

The following variables are available in SQL statements:

$LINES - The number of lines in the terminal window.
$COLS - The number of columns in the terminal window.

Environment

Environment variables can be accessed in queries using the usual syntax of $VAR_NAME. For example, to read the value of the "USER" variable, you can write:

;SELECT $USER

Collators

naturalcase - Compare strings "naturally" so that number values in the string are compared based on their numeric value and not their character values. For example, "foo10" would be considered greater than "foo2".
naturalnocase - The same as naturalcase, but case-insensitive.
ipaddress - Compare IPv4/IPv6 addresses.

Reference

The following is a reference of the SQL syntax and functions that are available:

lnav/docs/source/sqlext.rst