summaryrefslogtreecommitdiffstats
path: root/docs/source/sqlext.rst
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 17:44:55 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 17:44:55 +0000
commit5068d34c08f951a7ea6257d305a1627b09a95817 (patch)
tree08213e2be853396a3b07ce15dbe222644dcd9a89 /docs/source/sqlext.rst
parentInitial commit. (diff)
downloadlnav-5068d34c08f951a7ea6257d305a1627b09a95817.tar.xz
lnav-5068d34c08f951a7ea6257d305a1627b09a95817.zip
Adding upstream version 0.11.1.upstream/0.11.1upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--docs/source/sqlext.rst184
1 files changed, 184 insertions, 0 deletions
diff --git a/docs/source/sqlext.rst b/docs/source/sqlext.rst
new file mode 100644
index 0000000..e0d7543
--- /dev/null
+++ b/docs/source/sqlext.rst
@@ -0,0 +1,184 @@
+
+.. _sql-ext:
+
+SQLite Interface
+================
+
+Log analysis in **lnav** can be done using the SQLite interface. Log messages
+can be accessed via `virtual tables <https://www.sqlite.org/vtab.html>`_ 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 :code:`access_log` table:
+
+.. csv-table::
+ :class: query-results
+ :header-rows: 1
+
+ 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: :code:`log_path`, :code:`log_text`, :code:`log_body`, and
+ :code:`log_raw_text`.
+
+You can activate the SQL prompt by pressing the :kbd:`;` key. At the
+prompt, you can start typing in the desired SQL statement and/or double-tap
+:kbd:`TAB` to activate auto-completion. A help window will appear above
+the prompt to guide you in the usage of SQL keywords and functions.
+
+.. figure:: sql-help.png
+ :align: center
+
+ Screenshot of the online help for the SQL prompt.
+
+.. figure:: group_concat-help.png
+ :align: center
+
+ Screenshot of the online help for the :code:`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:
+
+.. code-block:: custsqlite
+
+ ;SELECT c_ip, avg(sc_bytes), max(sc_bytes) FROM access_log GROUP BY c_ip
+
+After pressing :kbd:`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 :kbd:`q` to return to the log view and press :kbd:`v`
+to return to the log view. If the SQL results contain a
+:code:`log_line` column, you can press to :kbd:`Shift` + :kbd:`V` to
+switch between the DB view and the log
+
+.. figure:: query-results.png
+ :align: center
+
+ 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 :kbd:`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 :kbd:`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 :code:`syslog_log` format will have a table that is
+also named :code:`syslog_log`. There is also an :code:`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 :code:`.schema`
+command in the SQL prompt to examine a dump of the current database schema.
+
+The following columns are builtin and included in a :code:`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 :code:`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 :code:`UPDATE` or the :ref:`:comment<comment>` command.
+ :log_tags: A JSON list of tags for the message. This column can be changed
+ by an :code:`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 :code:`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.
+
+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 <http://www.sqlite.org/lang_corefunc.html>`_. The majority
+of the functions are from the
+`extensions-functions.c <http://www.sqlite.org/contrib>`_ file available from
+the `sqlite.org <http://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
+:code:`$VAR_NAME`. For example, to read the value of the "USER" variable, you
+can write:
+
+.. code-block:: custsqlite
+
+ ;SELECT $USER
+
+.. _collators:
+
+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:
+
+.. include:: ../../src/internals/sql-ref.rst