mirror of https://github.com/tstack/lnav.git
4014 lines
177 KiB
Plaintext
4014 lines
177 KiB
Plaintext
|
|
lnav
|
|
|
|
A fancy log file viewer for the terminal.
|
|
|
|
Overview
|
|
|
|
The Logfile Navigator, lnav, is an enhanced log file viewer that takes
|
|
advantage of any semantic information that can be gleaned from the
|
|
files being viewed, such as timestamps and log levels. Using this
|
|
extra semantic information, lnav can do things like interleaving
|
|
messages from different files, generate histograms of messages over
|
|
time, and providing hotkeys for navigating through the file. It is
|
|
hoped that these features will allow the user to quickly and
|
|
efficiently zero in on problems.
|
|
|
|
Opening Paths/URLs
|
|
|
|
The main arguments to lnav are the files, directories, glob patterns,
|
|
or URLs to be viewed. If no arguments are given, the default syslog
|
|
file for your system will be opened. These arguments will be polled
|
|
periodically so that any new data or files will be automatically
|
|
loaded. If a previously loaded file is removed or replaced, it will be
|
|
closed and the replacement opened.
|
|
|
|
Note: When opening SFTP URLs, if the password is not provided for the
|
|
host, the SSH agent can be used to do authentication.
|
|
|
|
Options
|
|
|
|
Lnav takes a list of files to view and/or you can use the flag
|
|
arguments to load well-known log files, such as the syslog log files.
|
|
The flag arguments are:
|
|
|
|
• -a Load all of the most recent log file types.
|
|
• -r Recursively load files from the given directory
|
|
hierarchies.
|
|
• -R Load older rotated log files as well.
|
|
|
|
When using the flag arguments, lnav will look for the files relative
|
|
to the current directory and its parent directories. In other words,
|
|
if you are working within a directory that has the well-known log
|
|
files, those will be preferred over any others.
|
|
|
|
If you do not want the default syslog file to be loaded when no files
|
|
are specified, you can pass the -N flag.
|
|
|
|
Any files given on the command-line are scanned to determine their log
|
|
file format and to create an index for each line in the file. You do
|
|
not have to manually specify the log file format. The currently
|
|
supported formats are: syslog, apache, strace, tcsh history, and
|
|
generic log files with timestamps.
|
|
|
|
Lnav will also display data piped in on the standard input. The
|
|
following options are available when doing so:
|
|
|
|
• -t Prepend timestamps to the lines of data being read
|
|
in on the standard input.
|
|
• -w file Write the contents of the standard input to
|
|
this file.
|
|
|
|
To automatically execute queries or lnav commands after the files have
|
|
been loaded, you can use the following options:
|
|
|
|
• -c cmd A command, query, or file to execute. The
|
|
first character determines the type of operation: a colon
|
|
( : ) is used for the built-in commands; a semi-colon ( ;
|
|
) for SQL queries; and a pipe symbol ( | ) for executing
|
|
a file containing other commands. For example, to open
|
|
the file "foo.log" and go to the tenth line in the file,
|
|
you can do:
|
|
|
|
┃lnav -c ':goto 10' foo.log
|
|
|
|
This option can be given multiple times to execute
|
|
multiple operations in sequence.
|
|
• -f file A file that contains commands, queries, or
|
|
files to execute. This option is a shortcut for -c '|file'
|
|
. You can use a dash ( - ) to execute commands from the
|
|
standard input.
|
|
|
|
To execute commands/queries without the opening the interactive text
|
|
UI, you can pass the -n option. This combination of options allows
|
|
you to write scripts for processing logs with lnav. For example, to
|
|
get a list of IP addresses that dhclient has bound to in CSV format:
|
|
|
|
┃#! /usr/bin/lnav -nf
|
|
┃
|
|
┃# Usage: dhcp_ip.lnav /var/log/messages
|
|
┃# Only include lines that look like:
|
|
┃# Apr 29 00:31:56 example-centos5 dhclient: bound to 10.1.10.103 -- renewal in 9938 seconds.
|
|
┃
|
|
┃:filter-in dhclient: bound to
|
|
┃
|
|
┃# The log message parser will extract the IP address
|
|
┃# as col_0, so we select that and alias it to "dhcp_ip".
|
|
┃;select distinct col_0 as dhcp_ip from logline;
|
|
┃
|
|
┃# Finally, write the results of the query to stdout.
|
|
┃:write-csv-to -
|
|
|
|
Display
|
|
|
|
The main part of the display shows the log lines from the files
|
|
interleaved based on time-of-day. New lines are automatically loaded
|
|
as they are appended to the files and, if you are viewing the bottom
|
|
of the files, lnav will scroll down to display the new lines, much
|
|
like tail -f .
|
|
|
|
On color displays, the lines will be highlighted as follows:
|
|
|
|
• Errors will be colored in red;
|
|
• warnings will be yellow;
|
|
• boundaries between days will be underlined; and
|
|
• various color highlights will be applied to: IP
|
|
addresses, SQL keywords, XML tags, file and line numbers
|
|
in Java backtraces, and quoted strings.
|
|
|
|
To give you an idea of where you are spatially, the right side of the
|
|
display has a proportionally sized 'scroll bar' that indicates your
|
|
current position in the files. The scroll bar will also show areas of
|
|
the file where warnings or errors are detected by coloring the bar
|
|
yellow or red, respectively. Tick marks will also be added to the left
|
|
and right hand side of the bar, for search hits and bookmarks.
|
|
|
|
A bar on the left side is color coded and broken up to indicate which
|
|
messages are from the same file. Pressing the left-arrow or h will
|
|
reveal the source file names for each message and pressing again will
|
|
show the full paths.
|
|
|
|
Above and below the main body are status lines that display:
|
|
|
|
• the current time;
|
|
• the name of the file the top line was pulled from;
|
|
• the log format for the top line;
|
|
• the current view;
|
|
• the line number for the top line in the display;
|
|
• the current search hit, the total number of hits, and
|
|
the search term;
|
|
|
|
If the view supports filtering, there will be a status line showing
|
|
the following:
|
|
|
|
• the number of enabled filters and the total number of
|
|
filters;
|
|
• the number of lines not displayed because of filtering.
|
|
|
|
To edit the filters, you can press TAB to change the focus from the
|
|
main view to the filter editor. The editor allows you to create,
|
|
enable/disable, and delete filters easily.
|
|
|
|
Finally, the last line on the display is where you can enter search
|
|
patterns and execute internal commands, such as converting a unix-
|
|
timestamp into a human-readable date. The command-line is implemented
|
|
using the readline library, so the usual set of keyboard shortcuts are
|
|
available. Most commands and searches also support tab-completion.
|
|
|
|
The body of the display is also used to display other content, such
|
|
as: the help file, histograms of the log messages over time, and SQL
|
|
results. The views are organized into a stack so that any time you
|
|
activate a new view with a key press or command, the new view is
|
|
pushed onto the stack. Pressing the same key again will pop the view
|
|
off of the stack and return you to the previous view. Note that you
|
|
can always use q to pop the top view off of the stack.
|
|
|
|
Default Key Bindings
|
|
|
|
Views
|
|
|
|
Key(s) Action
|
|
═══════════════════════════════════════════════════════════
|
|
? View/leave this help message.
|
|
q Leave the current view or quit the program when in
|
|
the log file view.
|
|
Q Similar to q , except it will try to sync the top
|
|
time between the current and former views. For
|
|
example, when leaving the spectrogram view with Q
|
|
, the top time in that view will be matched to the
|
|
top time in the log view.
|
|
TAB Toggle focusing on the filter editor or the main
|
|
view.
|
|
a/A Restore the view that was previously popped with q
|
|
/ Q . The A hotkey will try to match the top
|
|
times between the two views.
|
|
X Close the current text file or log file.
|
|
|
|
Spatial Navigation
|
|
|
|
Key(s) Action
|
|
═══════════════════════════════════════════════════════════════
|
|
g/Home Move to the top of the file.
|
|
G/End Move to the end of the file. If the view is
|
|
already at the end, it will move to the last line.
|
|
SPACE/PgDn Move down a page.
|
|
b/PgUp Move up a page.
|
|
j/↓ Move down a line.
|
|
k/↑ Move up a line.
|
|
h/← Move to the left. In the log view, moving left
|
|
will reveal the source log file names for each
|
|
line. Pressing again will reveal the full path.
|
|
l/→ Move to the right.
|
|
H/Shift ← Move to the left by a smaller increment.
|
|
L/Shift → Move to the right by a smaller increment.
|
|
e/E Move to the next/previous error.
|
|
w/W Move to the next/previous warning.
|
|
n/N Move to the next/previous search hit. When pressed
|
|
repeatedly within a short time, the view will move
|
|
at least a full page at a time instead of moving
|
|
to the next hit.
|
|
f/F Move to the next/previous file. In the log view,
|
|
this moves to the next line from a different file.
|
|
In the text view, this rotates the view to the
|
|
next file.
|
|
>/< Move horizontally to the next/previous search hit.
|
|
o/O Move forward/backward to the log message with a
|
|
matching 'operation ID' (opid) field.
|
|
u/U Move forward/backward through any user bookmarks
|
|
you have added using the 'm' key. This hotkey will
|
|
also jump to the start of any log partitions that
|
|
have been created with the 'partition-name'
|
|
command.
|
|
s/S Move to the next/previous "slow down" in the log
|
|
message rate. A slow down is detected by measuring
|
|
how quickly the message rate has changed over the
|
|
previous several messages. For example, if one
|
|
message is logged every second for five seconds
|
|
and then the last message arrives five seconds
|
|
later, the last message will be highlighted as a
|
|
slow down.
|
|
{/} Move to the previous/next location in history.
|
|
Whenever you jump to a new location in the view,
|
|
the location will be added to the history. The
|
|
history is not updated when using only the arrow
|
|
keys.
|
|
|
|
Chronological Navigation
|
|
|
|
Key(s) Action
|
|
══════════════════════════════════════════════════════════════════
|
|
d/D Move forward/backward 24 hours from the current
|
|
position in the log file.
|
|
1-6/Shift 1-6 Move to the next/previous n'th ten minute of the
|
|
hour. For example, '4' would move to the first log
|
|
line in the fortieth minute of the current hour in
|
|
the log. And, '6' would move to the next hour
|
|
boundary.
|
|
7/8 Move to the previous/next minute.
|
|
0/Shift 0 Move to the next/previous day boundary.
|
|
r/R Move forward/backward based on the relative time
|
|
that was last used with the 'goto' command. For
|
|
example, executing ':goto a minute later' will
|
|
move the log view forward a minute and then
|
|
pressing 'r' will move it forward a minute again.
|
|
Pressing 'R' will then move the view in the
|
|
opposite direction, so backwards a minute.
|
|
|
|
Bookmarks
|
|
|
|
Key(s) Action
|
|
═══════════════════════════════════════════════════════════
|
|
m Mark/unmark the line at the top of the display.
|
|
The line will be highlighted with reverse video to
|
|
indicate that it is a user bookmark. You can use
|
|
the u hotkey to iterate through marks you have
|
|
added.
|
|
M Mark/unmark all the lines between the top of the
|
|
display and the last line marked/unmarked.
|
|
J Mark/unmark the next line after the previously
|
|
marked line.
|
|
K Like J except it toggles the mark on the
|
|
previous line.
|
|
c Copy the marked text to the X11 selection buffer
|
|
or OS X clipboard.
|
|
C Clear all marked lines.
|
|
|
|
Display options
|
|
|
|
Key(s) Action
|
|
══════════════════════════════════════════════════════════════════
|
|
P Switch to/from the pretty-printed view of the log
|
|
or text files currently displayed. In this view,
|
|
structured data, such as XML, will be reformatted
|
|
to make it easier to read.
|
|
t Switch to/from the text file view. The text file
|
|
view is for any files that are not recognized as
|
|
log files.
|
|
= Pause/unpause loading of new file data.
|
|
Ctrl-L (Lo-fi mode) Exit screen-mode and write the
|
|
displayed log lines in plain text to the terminal
|
|
until a key is pressed. Useful for copying long
|
|
lines from the terminal without picking up any of
|
|
the extra decorations.
|
|
T Toggle the display of the "elapsed time" column
|
|
that shows the time elapsed since the beginning of
|
|
the logs or the offset from the previous bookmark.
|
|
Sharp changes in the message rate are highlighted
|
|
by coloring the separator between the time column
|
|
and the log message. A red highlight means the
|
|
message rate has slowed down and green means it
|
|
has sped up. You can use the "s/S" hotkeys to scan
|
|
through the slow downs.
|
|
i View/leave a histogram of the log messages over
|
|
time. The histogram counts the number of displayed
|
|
log lines for each bucket of time. The bars are
|
|
layed out horizontally with colored segments
|
|
representing the different log levels. You can use
|
|
the z hotkey to change the size of the time
|
|
buckets (e.g. ten minutes, one hour, one day).
|
|
I Switch between the log and histogram views while
|
|
keeping the time displayed at the top of each view
|
|
in sync. For example, if the top line in the log
|
|
view is "11:40", hitting I will switch to the
|
|
histogram view and scrolled to display "11:00" at
|
|
the top (if the zoom level is hours).
|
|
z/Shift Z Zoom in or out one step in the histogram view.
|
|
v Switch to/from the SQL result view.
|
|
V Switch between the log and SQL result views while
|
|
keeping the top line number in the log view in
|
|
sync with the log_line column in the SQL view. For
|
|
example, doing a query that selects for "
|
|
log_idle_msecs" and "log_line", you can move the
|
|
top of the SQL view to a line and hit 'V' to
|
|
switch to the log view and move to the line number
|
|
that was selected in the "log_line" column. If
|
|
there is no "log_line" column, lnav will find the
|
|
first column with a timestamp and move to
|
|
corresponding time in the log view.
|
|
TAB/Shift TAB In the SQL result view, cycle through the columns
|
|
that are graphed. Initially, all number values are
|
|
displayed in a stacked graph. Pressing TAB will
|
|
change the display to only graph the first column.
|
|
Repeatedly pressing TAB will cycle through the
|
|
columns until they are all graphed again.
|
|
p In the log view: enable or disable the display of
|
|
the fields that the log message parser knows about
|
|
or has discovered. This overlay is temporarily
|
|
enabled when the semicolon key (;) is pressed so
|
|
that it is easier to write queries.
|
|
In the DB view: enable or disable the display of
|
|
values in columns containing JSON-encoded values
|
|
in the top row. The overlay will display the JSON-
|
|
Pointer reference and value for all fields in the
|
|
JSON data.
|
|
CTRL-W Toggle word-wrapping.
|
|
CTRL-P Show/hide the data preview panel that may be
|
|
opened when entering commands or SQL queries.
|
|
CTRL-F Toggle the enabled/disabled state of all filters
|
|
in the current view.
|
|
x Toggle the hiding of log message fields. The
|
|
hidden fields will be replaced with three bullets
|
|
and highlighted in yellow.
|
|
F2 Toggle mouse support.
|
|
|
|
Query
|
|
|
|
Key(s) Action
|
|
═════════════════════════════════════════════════════════════════════════════
|
|
/regexp Start a search for the given regular expression.
|
|
The search is live, so when there is a pause in
|
|
typing, the currently running search will be
|
|
canceled and a new one started. The first ten
|
|
lines that match the search will be displayed in
|
|
the preview window at the bottom of the view.
|
|
History is maintained for your searches so you can
|
|
rerun them easily. Words that are currently
|
|
displayed are also available for tab-completion,
|
|
so you can easily search for values without
|
|
needing to copy-and-paste the string. If there is
|
|
an error encountered while trying to interpret the
|
|
expression, the error will be displayed in red on
|
|
the status line. While the search is active, the '
|
|
hits' field in the status line will be green, when
|
|
finished it will turn back to black.
|
|
:<command> Execute an internal command. The commands are
|
|
listed below. History is also supported in this
|
|
context as well as tab-completion for commands and
|
|
some arguments. The result of the command replaces
|
|
the command you typed.
|
|
;<sql> Execute an SQL query. Most supported log file
|
|
formats provide a sqlite virtual table backend
|
|
that can be used in queries. See the SQL section
|
|
below for more information.
|
|
|<script> [arg1 .. argN] Execute an lnav script contained in a format
|
|
directory (e.g. ~/.lnav/formats/default). The
|
|
script can contain lines starting with : , ; ,
|
|
or | to execute commands, SQL queries or execute
|
|
other files in lnav. Any values after the script
|
|
name are treated as arguments can be referenced in
|
|
the script using $1 , $2 , and so on, like in a
|
|
shell script.
|
|
CTRL+], ESCAPE Abort command-line entry started with / , :
|
|
, ;
|
|
, or | .
|
|
|
|
┃ Note: The regular expression format used by is PCRE
|
|
┃ (Perl-Compatible Regular Expressions). For example,
|
|
┃ if you wanted to search for ethernet device names,
|
|
┃ regardless of their ID number, you can type:
|
|
┃
|
|
┃ eth\d+
|
|
┃
|
|
┃ You can find more information about Perl regular
|
|
┃ expressions at:
|
|
┃
|
|
┃ http://perldoc.perl.org/perlre.html
|
|
┃
|
|
┃ If the search string is not valid PCRE, a search
|
|
┃ is done for the exact string instead of doing a
|
|
┃ regex search.
|
|
|
|
Session
|
|
|
|
Key(s) Action
|
|
═══════════════════════════════════════════════════════════
|
|
CTRL-R Reset the session state. This will save the
|
|
current session state (filters, highlights) and
|
|
then reset the state to the factory default.
|
|
|
|
Filter Editor
|
|
|
|
The following hotkeys are only available when the focus is on the
|
|
filter editor. You can change the focus by pressing TAB.
|
|
|
|
Key(s) Action
|
|
═══════════════════════════════════════════════════════════
|
|
q Switch the focus back to the main view.
|
|
j/↓ Select the next filter.
|
|
k/↑ Select the previous filter.
|
|
o Create a new "out" filter.
|
|
i Create a new "in" filter .
|
|
SPACE Toggle the enabled/disabled state of the currently
|
|
selected filter.
|
|
t Toggle the type of filter between "in" and "out".
|
|
ENTER Edit the selected filter.
|
|
D Delete the selected filter.
|
|
|
|
Mouse Support (experimental)
|
|
|
|
If you are using Xterm, or a compatible terminal, you can use the
|
|
mouse to mark lines of text and move the view by grabbing the
|
|
scrollbar.
|
|
|
|
NOTE: You need to manually enable this feature by setting the LNAV_EXP
|
|
environment variable to "mouse". F2 toggles mouse support.
|
|
|
|
SQL Queries (experimental)
|
|
|
|
Lnav has support for performing SQL queries on log files using the
|
|
Sqlite3 "virtual" table feature. For all supported log file types,
|
|
lnav will create tables that can be queried using the subset of SQL
|
|
that is supported by Sqlite3. For example, to get the top ten URLs
|
|
being accessed in any loaded Apache log files, you can execute:
|
|
|
|
┃;select cs_uri_stem, count(*) as total from access_log
|
|
┃ group by cs_uri_stem order by total desc limit 10;
|
|
|
|
The query result view shows the results and graphs any numeric values
|
|
found in the result, much like the histogram view.
|
|
|
|
The builtin set of log tables are listed below. Note that only the log
|
|
messages that match a particular format can be queried by a particular
|
|
table. You can find the file format and table name for the top log
|
|
message by looking in the upper right hand corner of the log file
|
|
view.
|
|
|
|
Some commonly used format tables are:
|
|
|
|
Name Description
|
|
════════════════════════════════════════════════════════════════
|
|
access_log Apache common access log format
|
|
syslog_log Syslog format
|
|
strace_log Strace log format
|
|
generic_log 'Generic' log format. This table contains messages
|
|
from files that have a very simple format with a
|
|
leading timestamp followed by the message.
|
|
|
|
NOTE: You can get a dump of the schema for the internal tables, and
|
|
any attached databases, by running the .schema SQL command.
|
|
|
|
The columns available for the top log line in the view will
|
|
automatically be displayed after pressing the semicolon ( ; ) key. All
|
|
log tables contain at least the following columns:
|
|
|
|
Column Description
|
|
═══════════════════════════════════════════════════════════════════
|
|
log_line The line number in the file, starting at zero.
|
|
log_part The name of the partition. You can change this
|
|
column using an UPDATE SQL statement or with the '
|
|
partition-name' command. After a value is set,
|
|
the following log messages will have the same
|
|
partition name up until another name is set.
|
|
log_time The time of the log entry.
|
|
log_idle_msecs The amount of time, in milliseconds, between the
|
|
current log message and the previous one.
|
|
log_level The log level (e.g. info, error, etc...).
|
|
log_mark The bookmark status for the line. This column can
|
|
be written to using an UPDATE query.
|
|
log_path The full path to the file.
|
|
log_text The raw line of text. Note that this column is
|
|
not included in the result of a 'select *', but it
|
|
does exist.
|
|
|
|
The following tables include the basic columns as listed above and
|
|
include a few more columns since the log file format is more
|
|
structured.
|
|
|
|
• syslog_log
|
|
|
|
Column Description
|
|
═════════════════════════════════════════════════════════════════
|
|
log_hostname The hostname the message was received from.
|
|
log_procname The name of the process that sent the message.
|
|
log_pid The process ID of the process that sent the
|
|
message.
|
|
• access_log (The column names are the same as those in
|
|
the Microsoft LogParser tool.)
|
|
|
|
Column Description
|
|
══════════════════════════════════════════════════════════
|
|
c_ip The client IP address.
|
|
cs_username The client user name.
|
|
cs_method The HTTP method.
|
|
cs_uri_stem The stem portion of the URI.
|
|
cs_uri_query The query portion of the URI.
|
|
cs_version The HTTP version string.
|
|
sc_status The status number returned to the client.
|
|
sc_bytes The number of bytes sent to the client.
|
|
cs_referrer The URL of the referring page.
|
|
cs_user_agent The user agent string.
|
|
• strace_log (Currently, you need to run strace with
|
|
the -tt -T options so there are timestamps for each
|
|
function call.)
|
|
|
|
Column Description
|
|
═══════════════════════════════════════════════════════
|
|
funcname The name of the syscall.
|
|
result The result code.
|
|
duration The amount of time spent in the syscall.
|
|
arg0 - arg9 The arguments passed to the syscall.
|
|
|
|
These tables are created dynamically and not stored in memory or on
|
|
disk. If you would like to persist some information from the tables,
|
|
you can attach another database and create tables in that database.
|
|
For example, if you wanted to save the results from the earlier
|
|
example of a top ten query into the "/tmp/topten.db" file, you can do:
|
|
|
|
┃;attach database "/tmp/topten.db" as topten;
|
|
┃;create table topten.foo as select cs_uri_stem, count(*) as total
|
|
┃ from access_log group by cs_uri_stem order by total desc
|
|
┃ limit 10;
|
|
|
|
Dynamic logline Table (experimental)
|
|
|
|
(NOTE: This feature is still very new and not completely reliable yet,
|
|
use with care.)
|
|
|
|
For log formats that lack message structure, lnav can parse the log
|
|
message and attempt to extract any data fields that it finds. This
|
|
feature is available through the logline log table. This table is
|
|
dynamically created and defined based on the message at the top of the
|
|
log view. For example, given the following log message from "sudo",
|
|
lnav will create the "logline" table with columns for "TTY", "PWD", "
|
|
USER", and "COMMAND":
|
|
|
|
┃May 24 06:48:38 Tim-Stacks-iMac.local sudo[76387]: stack : TTY=ttys003 ; PWD=/Users/stack/github/lbuild ; USER=root ; COMMAND=/bin/echo Hello, World!
|
|
|
|
Queries executed against this table will then only return results for
|
|
other log messages that have the same format. So, if you were to
|
|
execute the following query while viewing the above line, you might
|
|
get the following results:
|
|
|
|
┃;select USER,COMMAND from logline;
|
|
|
|
USER COMMAND
|
|
═════════════════════════════════
|
|
root /bin/echo Hello, World!
|
|
mal /bin/echo Goodbye, World!
|
|
|
|
The log parser works by examining each message for key/value pairs
|
|
separated by an equal sign (=) or a colon (:). For example, in the
|
|
previous example of a "sudo" message, the parser sees the "USER=root"
|
|
string as a pair where the key is "USER" and the value is "root". If
|
|
no pairs can be found, then anything that looks like a value is
|
|
extracted and assigned a numbered column. For example, the following
|
|
line is from "dhcpd":
|
|
|
|
┃Sep 16 22:35:57 drill dhcpd: DHCPDISCOVER from 00:16:ce:54:4e:f3 via hme3
|
|
|
|
In this case, the lnav parser recognizes that "DHCPDISCOVER", the MAC
|
|
address and the "hme3" device name are values and not normal words.
|
|
So, it builds a table with three columns for each of these values. The
|
|
regular words in the message, like "from" and "via", are then used to
|
|
find other messages with a similar format.
|
|
|
|
If you would like to execute queries against log messages of different
|
|
formats at the same time, you can use the 'create-logline-table'
|
|
command to permanently create a table using the top line of the log
|
|
view as a template.
|
|
|
|
Other SQL Features
|
|
|
|
Environment variables can be used in SQL statements by prefixing the
|
|
variable name with a dollar-sign ($). For example, to read the value
|
|
of the HOME variable, you can do:
|
|
|
|
┃;SELECT $HOME;
|
|
|
|
To select the syslog messages that have a hostname field that is equal
|
|
to the HOSTNAME variable:
|
|
|
|
┃;SELECT * FROM syslog_log WHERE log_hostname = $HOSTNAME;
|
|
|
|
NOTE: Variable substitution is done for fields in the query and is not
|
|
a plain text substitution. For example, the following statement WILL
|
|
NOT WORK:
|
|
|
|
┃;SELECT * FROM $TABLE_NAME; -- Syntax error
|
|
|
|
Access to lnav's environment variables is also available via the "
|
|
environ" table. The table has two columns (name, value) and can be
|
|
read and written to using SQL SELECT, INSERT, UPDATE, and DELETE
|
|
statements. For example, to set the "FOO" variable to the value "BAR":
|
|
|
|
┃;INSERT INTO environ SELECT 'FOO', 'BAR';
|
|
|
|
As a more complex example, you can set the variable "LAST" to the last
|
|
syslog line number by doing:
|
|
|
|
┃;INSERT INTO environ SELECT 'LAST', (SELECT max(log_line) FROM syslog_log);
|
|
|
|
A delete will unset the environment variable:
|
|
|
|
┃;DELETE FROM environ WHERE name='LAST';
|
|
|
|
The table allows you to easily use the results of a SQL query in lnav
|
|
commands, which is especially useful when scripting lnav.
|
|
|
|
Contact
|
|
|
|
For more information, visit the lnav website at:
|
|
|
|
http://lnav.org[1]
|
|
|
|
┃[1] - http://lnav.org
|
|
|
|
For support questions, email:
|
|
|
|
lnav@googlegroups.com[1] support@lnav.org[2]
|
|
|
|
┃[1] - mailto:lnav@googlegroups.com
|
|
┃[2] - mailto:support@lnav.org
|
|
|
|
Command Reference
|
|
|
|
:adjust-log-time timestamp
|
|
══════════════════════════════════════════════════════════════════════
|
|
Change the timestamps of the top file to be relative to the given
|
|
date
|
|
Parameter
|
|
timestamp The new timestamp for the top line in the view
|
|
|
|
Examples
|
|
#1 To set the top timestamp to a given date:
|
|
:adjust-log-time 2017-01-02T05:33:00
|
|
|
|
|
|
#2 To set the top timestamp back an hour:
|
|
:adjust-log-time -1h
|
|
|
|
|
|
|
|
:alt-msg msg
|
|
══════════════════════════════════════════════════════════════════════
|
|
Display a message in the alternate command position
|
|
Parameter
|
|
msg The message to display
|
|
See Also
|
|
:echo, :eval, :redirect-to, :write-csv-to, :write-json-to,
|
|
:write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to,
|
|
:write-to, :write-view-to
|
|
Example
|
|
#1 To display 'Press t to switch to the text view' on the bottom right:
|
|
:alt-msg Press t to switch to the text view
|
|
|
|
|
|
|
|
:append-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Append marked lines in the current view to the given file
|
|
Parameter
|
|
path The path to the file to append to
|
|
See Also
|
|
:echo, :pipe-line-to, :pipe-to, :redirect-to, :write-csv-to,
|
|
:write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to,
|
|
:write-table-to, :write-to, :write-view-to
|
|
Example
|
|
#1 To append marked lines to the file /tmp/interesting-lines.txt:
|
|
:append-to /tmp/interesting-lines.txt
|
|
|
|
|
|
|
|
:clear-comment
|
|
══════════════════════════════════════════════════════════════════════
|
|
Clear the comment attached to the top log line
|
|
See Also
|
|
:comment, :tag
|
|
|
|
:clear-filter-expr
|
|
══════════════════════════════════════════════════════════════════════
|
|
Clear the filter expression
|
|
See Also
|
|
:filter-expr, :filter-in, :filter-out, :hide-lines-after,
|
|
:hide-lines-before, :hide-unmarked-lines, :toggle-filtering
|
|
|
|
:clear-highlight pattern
|
|
══════════════════════════════════════════════════════════════════════
|
|
Remove a previously set highlight regular expression
|
|
Parameter
|
|
pattern The regular expression previously used with :highlight
|
|
See Also
|
|
:enable-word-wrap, :hide-fields, :highlight
|
|
Example
|
|
#1 To clear the highlight with the pattern 'foobar':
|
|
:clear-highlight foobar
|
|
|
|
|
|
|
|
:clear-mark-expr
|
|
══════════════════════════════════════════════════════════════════════
|
|
Clear the mark expression
|
|
See Also
|
|
:hide-unmarked-lines, :mark, :mark-expr, :next-mark, :prev-mark
|
|
|
|
:clear-partition
|
|
══════════════════════════════════════════════════════════════════════
|
|
Clear the partition the top line is a part of
|
|
|
|
|
|
:close
|
|
══════════════════════════════════════════════════════════════════════
|
|
Close the top file in the view
|
|
|
|
|
|
:comment text
|
|
══════════════════════════════════════════════════════════════════════
|
|
Attach a comment to the top log line
|
|
Parameter
|
|
text The comment text
|
|
See Also
|
|
:clear-comment, :tag
|
|
Example
|
|
#1 To add the comment 'This is where it all went wrong' to the top line:
|
|
:comment This is where it all went wrong
|
|
|
|
|
|
|
|
:config option [value]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Read or write a configuration option
|
|
Parameters
|
|
option The path to the option to read or write
|
|
value The value to write. If not given, the current value is
|
|
returned
|
|
See Also
|
|
:reset-config
|
|
Examples
|
|
#1 To read the configuration of the '/ui/clock-format' option:
|
|
:config /ui/clock-format
|
|
|
|
|
|
#2 To set the '/ui/dim-text' option to 'false':
|
|
:config /ui/dim-text false
|
|
|
|
|
|
|
|
:create-logline-table table-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Create an SQL table using the top line of the log view as a template
|
|
|
|
Parameter
|
|
table-name The name for the new table
|
|
See Also
|
|
:create-search-table, :create-search-table, :write-csv-to,
|
|
:write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to,
|
|
:write-table-to, :write-view-to
|
|
Example
|
|
#1 To create a logline-style table named 'task_durations':
|
|
:create-logline-table task_durations
|
|
|
|
|
|
|
|
:create-search-table table-name [pattern]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Create an SQL table based on a regex search
|
|
Parameters
|
|
table-name The name of the table to create
|
|
pattern The regular expression used to capture the table
|
|
columns. If not given, the current search pattern is
|
|
used.
|
|
See Also
|
|
:create-logline-table, :create-logline-table, :delete-search-table,
|
|
:delete-search-table, :write-csv-to, :write-json-to,
|
|
:write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To create a table named 'task_durations' that matches log messages with the pattern '
|
|
duration=(?<duration>\d+)':
|
|
:create-search-table task_durations duration=(?<duration>\d+)
|
|
|
|
|
|
|
|
:current-time
|
|
══════════════════════════════════════════════════════════════════════
|
|
Print the current time in human-readable form and seconds since the
|
|
epoch
|
|
|
|
|
|
:delete-filter pattern
|
|
══════════════════════════════════════════════════════════════════════
|
|
Delete the filter created with :filter-in or :filter-out
|
|
Parameter
|
|
pattern The regular expression to match
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-after, :hide-lines-before,
|
|
:hide-unmarked-lines, :toggle-filtering
|
|
Example
|
|
#1 To delete the filter with the pattern 'last message repeated':
|
|
:delete-filter last message repeated
|
|
|
|
|
|
|
|
:delete-logline-table table-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Delete a table created with create-logline-table
|
|
Parameter
|
|
table-name The name of the table to delete
|
|
See Also
|
|
:create-logline-table, :create-logline-table, :create-search-table,
|
|
:create-search-table, :write-csv-to, :write-json-to,
|
|
:write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To delete the logline-style table named 'task_durations':
|
|
:delete-logline-table task_durations
|
|
|
|
|
|
|
|
:delete-search-table table-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Create an SQL table based on a regex search
|
|
Parameter
|
|
table-name The name of the table to create
|
|
See Also
|
|
:create-logline-table, :create-logline-table, :create-search-table,
|
|
:create-search-table, :write-csv-to, :write-json-to,
|
|
:write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To delete the search table named 'task_durations':
|
|
:delete-search-table task_durations
|
|
|
|
|
|
|
|
:delete-tags tag1 [... tagN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Remove the given tags from all log lines
|
|
Parameter
|
|
tag The tags to delete
|
|
See Also
|
|
:comment, :tag
|
|
Example
|
|
#1 To remove the tags '#BUG123' and '#needs-review' from all log lines:
|
|
:delete-tags #BUG123 #needs-review
|
|
|
|
|
|
|
|
:disable-filter pattern
|
|
══════════════════════════════════════════════════════════════════════
|
|
Disable a filter created with filter-in/filter-out
|
|
Parameter
|
|
pattern The regular expression used in the filter command
|
|
See Also
|
|
:enable-filter, :filter-in, :filter-out, :hide-lines-after,
|
|
:hide-lines-before, :hide-unmarked-lines, :toggle-filtering
|
|
Example
|
|
#1 To disable the filter with the pattern 'last message repeated':
|
|
:disable-filter last message repeated
|
|
|
|
|
|
|
|
:disable-word-wrap
|
|
══════════════════════════════════════════════════════════════════════
|
|
Disable word-wrapping for the current view
|
|
See Also
|
|
:enable-word-wrap, :hide-fields, :highlight
|
|
|
|
:echo msg
|
|
══════════════════════════════════════════════════════════════════════
|
|
Echo the given message to the screen or, if :redirect-to has been
|
|
called, to output file specified in the redirect. Variable
|
|
substitution is performed on the message. Use a backslash to escape
|
|
any special characters, like '$'
|
|
Parameter
|
|
msg The message to display
|
|
See Also
|
|
:alt-msg, :append-to, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-json-to,
|
|
:write-json-to, :write-jsonlines-to, :write-jsonlines-to,
|
|
:write-raw-to, :write-raw-to, :write-screen-to, :write-screen-to,
|
|
:write-table-to, :write-table-to, :write-to, :write-to, :write-view-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To output 'Hello, World!':
|
|
:echo Hello, World!
|
|
|
|
|
|
|
|
:enable-filter pattern
|
|
══════════════════════════════════════════════════════════════════════
|
|
Enable a previously created and disabled filter
|
|
Parameter
|
|
pattern The regular expression used in the filter command
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-after, :hide-lines-before,
|
|
:hide-unmarked-lines, :toggle-filtering
|
|
Example
|
|
#1 To enable the disabled filter with the pattern 'last message repeated':
|
|
:enable-filter last message repeated
|
|
|
|
|
|
|
|
:enable-word-wrap
|
|
══════════════════════════════════════════════════════════════════════
|
|
Enable word-wrapping for the current view
|
|
See Also
|
|
:disable-word-wrap, :hide-fields, :highlight
|
|
|
|
:eval command
|
|
══════════════════════════════════════════════════════════════════════
|
|
Evaluate the given command/query after doing environment variable
|
|
substitution
|
|
Parameter
|
|
command The command or query to perform substitution on.
|
|
See Also
|
|
:alt-msg, :echo, :redirect-to, :write-csv-to, :write-json-to,
|
|
:write-jsonlines-to, :write-raw-to, :write-screen-to, :write-table-to,
|
|
:write-to, :write-view-to
|
|
Example
|
|
#1 To substitute the table name from a variable:
|
|
:eval ;SELECT * FROM ${table}
|
|
|
|
|
|
|
|
:filter-expr expr
|
|
══════════════════════════════════════════════════════════════════════
|
|
Set the filter expression
|
|
Parameter
|
|
expr The SQL expression to evaluate for each log message. The
|
|
message values can be accessed using column names prefixed
|
|
with a colon
|
|
See Also
|
|
:clear-filter-expr, :filter-in, :filter-out, :hide-lines-after,
|
|
:hide-lines-before, :hide-unmarked-lines, :toggle-filtering
|
|
Examples
|
|
#1 To set a filter expression that matched syslog messages from 'syslogd':
|
|
:filter-expr :log_procname = 'syslogd'
|
|
|
|
|
|
#2 To set a filter expression that matches log messages where 'id' is followed by a number
|
|
and contains the string 'foo':
|
|
:filter-expr :log_body REGEXP 'id\d+' AND :log_body REGEXP 'foo'
|
|
|
|
|
|
|
|
:filter-in pattern
|
|
══════════════════════════════════════════════════════════════════════
|
|
Only show lines that match the given regular expression in the
|
|
current view
|
|
Parameter
|
|
pattern The regular expression to match
|
|
See Also
|
|
:delete-filter, :disable-filter, :filter-out, :hide-lines-after,
|
|
:hide-lines-before, :hide-unmarked-lines, :toggle-filtering
|
|
Example
|
|
#1 To filter out log messages that do not have the string 'dhclient':
|
|
:filter-in dhclient
|
|
|
|
|
|
|
|
:filter-out pattern
|
|
══════════════════════════════════════════════════════════════════════
|
|
Remove lines that match the given regular expression in the current
|
|
view
|
|
Parameter
|
|
pattern The regular expression to match
|
|
See Also
|
|
:delete-filter, :disable-filter, :filter-in, :hide-lines-after,
|
|
:hide-lines-before, :hide-unmarked-lines, :toggle-filtering
|
|
Example
|
|
#1 To filter out log messages that contain the string 'last message repeated':
|
|
:filter-out last message repeated
|
|
|
|
|
|
|
|
:goto line#|N%|date
|
|
══════════════════════════════════════════════════════════════════════
|
|
Go to the given location in the top view
|
|
Parameter
|
|
line#|N%|date A line number, percent into the file, or a timestamp
|
|
|
|
See Also
|
|
:next-location, :next-mark, :prev-location, :prev-mark, :relative-goto
|
|
Examples
|
|
#1 To go to line 22:
|
|
:goto 22
|
|
|
|
|
|
#2 To go to the line 75% of the way into the view:
|
|
:goto 75%
|
|
|
|
|
|
#3 To go to the first message on the first day of 2017:
|
|
:goto 2017-01-01
|
|
|
|
|
|
|
|
:help
|
|
══════════════════════════════════════════════════════════════════════
|
|
Open the help text view
|
|
|
|
|
|
:hide-fields field-name1 [... field-nameN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Hide log message fields by replacing them with an ellipsis
|
|
Parameter
|
|
field-name The name of the field to hide in the format for the top
|
|
log line. A qualified name can be used where the field
|
|
name is prefixed by the format name and a dot to hide
|
|
any field.
|
|
See Also
|
|
:enable-word-wrap, :highlight, :show-fields
|
|
Examples
|
|
#1 To hide the log_procname fields in all formats:
|
|
:hide-fields log_procname
|
|
|
|
|
|
#2 To hide only the log_procname field in the syslog format:
|
|
:hide-fields syslog_log.log_procname
|
|
|
|
|
|
|
|
:hide-file path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Hide the given file(s) and skip indexing until it is shown again.
|
|
If no path is given, the current file in the view is hidden
|
|
Parameter
|
|
path A path or glob pattern that specifies the files to hide
|
|
|
|
|
|
:hide-lines-after date
|
|
══════════════════════════════════════════════════════════════════════
|
|
Hide lines that come after the given date
|
|
Parameter
|
|
date An absolute or relative date
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-before, :hide-unmarked-lines,
|
|
:show-lines-before-and-after, :toggle-filtering
|
|
Examples
|
|
#1 To hide the lines after the top line in the view:
|
|
:hide-lines-after here
|
|
|
|
|
|
#2 To hide the lines after 6 AM today:
|
|
:hide-lines-after 6am
|
|
|
|
|
|
|
|
:hide-lines-before date
|
|
══════════════════════════════════════════════════════════════════════
|
|
Hide lines that come before the given date
|
|
Parameter
|
|
date An absolute or relative date
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-after, :hide-unmarked-lines,
|
|
:show-lines-before-and-after, :toggle-filtering
|
|
Examples
|
|
#1 To hide the lines before the top line in the view:
|
|
:hide-lines-before here
|
|
|
|
|
|
#2 To hide the log messages before 6 AM today:
|
|
:hide-lines-before 6am
|
|
|
|
|
|
|
|
:hide-unmarked-lines
|
|
══════════════════════════════════════════════════════════════════════
|
|
Hide lines that have not been bookmarked
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-after, :hide-lines-before, :mark,
|
|
:next-mark, :prev-mark, :toggle-filtering
|
|
|
|
:highlight pattern
|
|
══════════════════════════════════════════════════════════════════════
|
|
Add coloring to log messages fragments that match the given regular
|
|
expression
|
|
Parameter
|
|
pattern The regular expression to match
|
|
See Also
|
|
:clear-highlight, :enable-word-wrap, :hide-fields
|
|
Example
|
|
#1 To highlight numbers with three or more digits:
|
|
:highlight \d{3,}
|
|
|
|
|
|
|
|
:load-session
|
|
══════════════════════════════════════════════════════════════════════
|
|
Load the latest session state
|
|
|
|
|
|
:mark
|
|
══════════════════════════════════════════════════════════════════════
|
|
Toggle the bookmark state for the top line in the current view
|
|
See Also
|
|
:hide-unmarked-lines, :next-mark, :prev-mark
|
|
|
|
:mark-expr expr
|
|
══════════════════════════════════════════════════════════════════════
|
|
Set the bookmark expression
|
|
Parameter
|
|
expr The SQL expression to evaluate for each log message. The
|
|
message values can be accessed using column names prefixed
|
|
with a colon
|
|
See Also
|
|
:clear-mark-expr, :hide-unmarked-lines, :mark, :next-mark, :prev-mark
|
|
Example
|
|
#1 To mark lines from 'dhclient' that mention 'eth0':
|
|
:mark-expr :log_procname = 'dhclient' AND :log_body LIKE '%eth0%'
|
|
|
|
|
|
|
|
:next-location
|
|
══════════════════════════════════════════════════════════════════════
|
|
Move to the next position in the location history
|
|
See Also
|
|
:goto, :next-mark, :prev-location, :prev-mark, :relative-goto
|
|
|
|
:next-mark type1 [... typeN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Move to the next bookmark of the given type in the current view
|
|
Parameter
|
|
type The type of bookmark -- error, warning, search, user, file,
|
|
meta
|
|
See Also
|
|
:goto, :hide-unmarked-lines, :mark, :next-location, :prev-location,
|
|
:prev-mark, :prev-mark, :relative-goto
|
|
Example
|
|
#1 To go to the next error:
|
|
:next-mark error
|
|
|
|
|
|
|
|
:open path1 [... pathN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Open the given file(s) in lnav. Opening files on machines
|
|
accessible via SSH can be done using the syntax: [user@]host:/path/
|
|
to/logs
|
|
Parameter
|
|
path The path to the file to open
|
|
|
|
Examples
|
|
#1 To open the file '/path/to/file':
|
|
:open /path/to/file
|
|
|
|
|
|
#2 To open the remote file '/var/log/syslog.log':
|
|
:open dean@host1.example.com:/var/log/syslog.log
|
|
|
|
|
|
|
|
:partition-name name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Mark the top line in the log view as the start of a new partition
|
|
with the given name
|
|
Parameter
|
|
name The name for the new partition
|
|
|
|
Example
|
|
#1 To mark the top line as the start of the partition named 'boot #1':
|
|
:partition-name boot #1
|
|
|
|
|
|
|
|
:pipe-line-to shell-cmd
|
|
══════════════════════════════════════════════════════════════════════
|
|
Pipe the top line to the given shell command
|
|
Parameter
|
|
shell-cmd The shell command-line to execute
|
|
See Also
|
|
:append-to, :echo, :pipe-to, :redirect-to, :write-csv-to,
|
|
:write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to,
|
|
:write-table-to, :write-to, :write-view-to
|
|
Example
|
|
#1 To write the top line to 'sed' for processing:
|
|
:pipe-line-to sed -e 's/foo/bar/g'
|
|
|
|
|
|
|
|
:pipe-to shell-cmd
|
|
══════════════════════════════════════════════════════════════════════
|
|
Pipe the marked lines to the given shell command
|
|
Parameter
|
|
shell-cmd The shell command-line to execute
|
|
See Also
|
|
:append-to, :echo, :pipe-line-to, :redirect-to, :write-csv-to,
|
|
:write-json-to, :write-jsonlines-to, :write-raw-to, :write-screen-to,
|
|
:write-table-to, :write-to, :write-view-to
|
|
Example
|
|
#1 To write marked lines to 'sed' for processing:
|
|
:pipe-to sed -e s/foo/bar/g
|
|
|
|
|
|
|
|
:prev-location
|
|
══════════════════════════════════════════════════════════════════════
|
|
Move to the previous position in the location history
|
|
See Also
|
|
:goto, :next-location, :next-mark, :prev-mark, :relative-goto
|
|
|
|
:prev-mark type1 [... typeN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Move to the previous bookmark of the given type in the current view
|
|
Parameter
|
|
type The type of bookmark -- error, warning, search, user, file,
|
|
meta
|
|
See Also
|
|
:goto, :hide-unmarked-lines, :mark, :next-location, :next-mark,
|
|
:next-mark, :prev-location, :relative-goto
|
|
Example
|
|
#1 To go to the previous error:
|
|
:prev-mark error
|
|
|
|
|
|
|
|
:prompt type [--alt] [prompt] [initial-value]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Open the given prompt
|
|
Parameters
|
|
type The type of prompt -- command, script, search, sql,
|
|
user
|
|
--alt Perform the alternate action for this prompt by
|
|
default
|
|
prompt The prompt to display
|
|
initial-value The initial value to fill in for the prompt
|
|
|
|
Examples
|
|
#1 To open the command prompt with 'filter-in' already filled in:
|
|
:prompt command : 'filter-in '
|
|
|
|
|
|
#2 To ask the user a question:
|
|
:prompt user 'Are you sure? '
|
|
|
|
|
|
|
|
:quit
|
|
══════════════════════════════════════════════════════════════════════
|
|
Quit lnav
|
|
|
|
|
|
:quit
|
|
══════════════════════════════════════════════════════════════════════
|
|
Quit lnav
|
|
|
|
|
|
:quit
|
|
══════════════════════════════════════════════════════════════════════
|
|
Quit lnav
|
|
|
|
|
|
:redirect-to [path]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Redirect the output of commands that write to stdout to the given
|
|
file
|
|
Parameter
|
|
path The path to the file to write. If not specified, the current
|
|
redirect will be cleared
|
|
See Also
|
|
:alt-msg, :append-to, :echo, :echo, :eval, :pipe-line-to, :pipe-to,
|
|
:write-csv-to, :write-csv-to, :write-json-to, :write-json-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to,
|
|
:write-screen-to, :write-screen-to, :write-table-to, :write-table-to,
|
|
:write-to, :write-to, :write-view-to, :write-view-to
|
|
Example
|
|
#1 To write the output of lnav commands to the file /tmp/script-output.txt:
|
|
:redirect-to /tmp/script-output.txt
|
|
|
|
|
|
|
|
:redraw
|
|
══════════════════════════════════════════════════════════════════════
|
|
Do a full redraw of the screen
|
|
|
|
|
|
:relative-goto line-count|N%
|
|
══════════════════════════════════════════════════════════════════════
|
|
Move the current view up or down by the given amount
|
|
Parameter
|
|
line-count|N% The amount to move the view by.
|
|
See Also
|
|
:goto, :next-location, :next-mark, :prev-location, :prev-mark
|
|
Examples
|
|
#1 To move 22 lines down in the view:
|
|
:relative-goto +22
|
|
|
|
|
|
#2 To move 10 percent back in the view:
|
|
:relative-goto -10%
|
|
|
|
|
|
|
|
:reset-config option
|
|
══════════════════════════════════════════════════════════════════════
|
|
Reset the configuration option to its default value
|
|
Parameter
|
|
option The path to the option to reset
|
|
See Also
|
|
:config
|
|
Example
|
|
#1 To reset the '/ui/clock-format' option back to the builtin default:
|
|
:reset-config /ui/clock-format
|
|
|
|
|
|
|
|
:reset-session
|
|
══════════════════════════════════════════════════════════════════════
|
|
Reset the session state, clearing all filters, highlights, and
|
|
bookmarks
|
|
|
|
|
|
:save-session
|
|
══════════════════════════════════════════════════════════════════════
|
|
Save the current state as a session
|
|
|
|
|
|
:session lnav-command
|
|
══════════════════════════════════════════════════════════════════════
|
|
Add the given command to the session file (~/.lnav/session)
|
|
Parameter
|
|
lnav-command The lnav command to save.
|
|
|
|
Example
|
|
#1 To add the command ':highlight foobar' to the session file:
|
|
:session :highlight foobar
|
|
|
|
|
|
|
|
:set-min-log-level log-level
|
|
══════════════════════════════════════════════════════════════════════
|
|
Set the minimum log level to display in the log view
|
|
Parameter
|
|
log-level The new minimum log level
|
|
|
|
Example
|
|
#1 To set the minimum log level displayed to error:
|
|
:set-min-log-level error
|
|
|
|
|
|
|
|
:show-fields field-name1 [... field-nameN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Show log message fields that were previously hidden
|
|
Parameter
|
|
field-name The name of the field to show
|
|
See Also
|
|
:enable-word-wrap, :hide-fields, :highlight
|
|
Example
|
|
#1 To show all the log_procname fields in all formats:
|
|
:show-fields log_procname
|
|
|
|
|
|
|
|
:show-file path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Show the given file(s) and resume indexing.
|
|
Parameter
|
|
path The path or glob pattern that specifies the files to show
|
|
|
|
|
|
:show-lines-before-and-after
|
|
══════════════════════════════════════════════════════════════════════
|
|
Show lines that were hidden by the 'hide-lines' commands
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-after, :hide-lines-before,
|
|
:hide-unmarked-lines, :toggle-filtering
|
|
|
|
:show-only-this-file
|
|
══════════════════════════════════════════════════════════════════════
|
|
Show only the file for the top line in the view
|
|
|
|
|
|
:show-unmarked-lines
|
|
══════════════════════════════════════════════════════════════════════
|
|
Show lines that have not been bookmarked
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-after, :hide-lines-before,
|
|
:hide-unmarked-lines, :hide-unmarked-lines, :mark, :next-mark,
|
|
:prev-mark, :toggle-filtering
|
|
|
|
:spectrogram field-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Visualize the given message field using a spectrogram
|
|
Parameter
|
|
field-name The name of the numeric field to visualize.
|
|
|
|
Example
|
|
#1 To visualize the sc_bytes field in the access_log format:
|
|
:spectrogram sc_bytes
|
|
|
|
|
|
|
|
:summarize column-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Execute a SQL query that computes the characteristics of the values
|
|
in the given column
|
|
Parameter
|
|
column-name The name of the column to analyze.
|
|
|
|
Example
|
|
#1 To get a summary of the sc_bytes column in the access_log table:
|
|
:summarize sc_bytes
|
|
|
|
|
|
|
|
:switch-to-view view-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Switch to the given view
|
|
Parameter
|
|
view-name The name of the view to switch to.
|
|
|
|
Example
|
|
#1 To switch to the 'schema' view:
|
|
:switch-to-view schema
|
|
|
|
|
|
|
|
:tag tag1 [... tagN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Attach tags to the top log line
|
|
Parameter
|
|
tag The tags to attach
|
|
See Also
|
|
:comment, :delete-tags, :untag
|
|
Example
|
|
#1 To add the tags '#BUG123' and '#needs-review' to the top line:
|
|
:tag #BUG123 #needs-review
|
|
|
|
|
|
|
|
:toggle-filtering
|
|
══════════════════════════════════════════════════════════════════════
|
|
Toggle the filtering flag for the current view
|
|
See Also
|
|
:filter-in, :filter-out, :hide-lines-after, :hide-lines-before,
|
|
:hide-unmarked-lines
|
|
|
|
:toggle-view view-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Switch to the given view or, if it is already displayed, switch to
|
|
the previous view
|
|
Parameter
|
|
view-name The name of the view to toggle the display of.
|
|
|
|
Example
|
|
#1 To switch to the 'schema' view if it is not displayed or switch back to the previous
|
|
view:
|
|
:toggle-view schema
|
|
|
|
|
|
|
|
:unix-time seconds
|
|
══════════════════════════════════════════════════════════════════════
|
|
Convert epoch time to a human-readable form
|
|
Parameter
|
|
seconds The epoch timestamp to convert
|
|
|
|
Example
|
|
#1 To convert the epoch time 1490191111:
|
|
:unix-time 1490191111
|
|
|
|
|
|
|
|
:untag tag1 [... tagN]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Detach tags from the top log line
|
|
Parameter
|
|
tag The tags to detach
|
|
See Also
|
|
:comment, :tag
|
|
Example
|
|
#1 To remove the tags '#BUG123' and '#needs-review' from the top line:
|
|
:untag #BUG123 #needs-review
|
|
|
|
|
|
|
|
:write-table-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Write SQL results to the given file in a tabular format
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-csv-to,
|
|
:write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to,
|
|
:write-raw-to, :write-screen-to, :write-screen-to, :write-screen-to,
|
|
:write-to, :write-to, :write-view-to, :write-view-to, :write-view-to
|
|
Example
|
|
#1 To write SQL results as text to /tmp/table.txt:
|
|
:write-table-to /tmp/table.txt
|
|
|
|
|
|
|
|
:write-csv-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Write SQL results to the given file in CSV format
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-json-to, :write-json-to, :write-json-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-jsonlines-to,
|
|
:write-raw-to, :write-raw-to, :write-raw-to, :write-screen-to,
|
|
:write-screen-to, :write-screen-to, :write-table-to, :write-table-to,
|
|
:write-table-to, :write-to, :write-to, :write-view-to, :write-view-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To write SQL results as CSV to /tmp/table.csv:
|
|
:write-csv-to /tmp/table.csv
|
|
|
|
|
|
|
|
:write-json-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Write SQL results to the given file in JSON format
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-csv-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-jsonlines-to,
|
|
:write-raw-to, :write-raw-to, :write-raw-to, :write-screen-to,
|
|
:write-screen-to, :write-screen-to, :write-table-to, :write-table-to,
|
|
:write-table-to, :write-to, :write-to, :write-view-to, :write-view-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To write SQL results as JSON to /tmp/table.json:
|
|
:write-json-to /tmp/table.json
|
|
|
|
|
|
|
|
:write-jsonlines-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Write SQL results to the given file in JSON Lines format
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-csv-to,
|
|
:write-json-to, :write-json-to, :write-json-to, :write-raw-to,
|
|
:write-raw-to, :write-raw-to, :write-screen-to, :write-screen-to,
|
|
:write-screen-to, :write-table-to, :write-table-to, :write-table-to,
|
|
:write-to, :write-to, :write-view-to, :write-view-to, :write-view-to
|
|
Example
|
|
#1 To write SQL results as JSON Lines to /tmp/table.json:
|
|
:write-jsonlines-to /tmp/table.json
|
|
|
|
|
|
|
|
:write-raw-to [--view={log,db}] path
|
|
══════════════════════════════════════════════════════════════════════
|
|
In the log view, write the original log file content of the marked
|
|
messages to the file. In the DB view, the contents of the cells are
|
|
written to the output file.
|
|
Parameters
|
|
--view={log,db} The view to use as the source of data
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-csv-to,
|
|
:write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-screen-to,
|
|
:write-screen-to, :write-screen-to, :write-table-to, :write-table-to,
|
|
:write-table-to, :write-to, :write-to, :write-view-to, :write-view-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To write the marked lines in the log view to /tmp/table.txt:
|
|
:write-raw-to /tmp/table.txt
|
|
|
|
|
|
|
|
:write-screen-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Write the displayed text or SQL results to the given file without
|
|
any formatting
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-csv-to,
|
|
:write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to,
|
|
:write-raw-to, :write-table-to, :write-table-to, :write-table-to,
|
|
:write-to, :write-to, :write-view-to, :write-view-to, :write-view-to
|
|
Example
|
|
#1 To write only the displayed text to /tmp/table.txt:
|
|
:write-screen-to /tmp/table.txt
|
|
|
|
|
|
|
|
:write-table-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Write SQL results to the given file in a tabular format
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-csv-to,
|
|
:write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to,
|
|
:write-raw-to, :write-screen-to, :write-screen-to, :write-screen-to,
|
|
:write-to, :write-to, :write-view-to, :write-view-to, :write-view-to
|
|
Example
|
|
#1 To write SQL results as text to /tmp/table.txt:
|
|
:write-table-to /tmp/table.txt
|
|
|
|
|
|
|
|
:write-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Overwrite the given file with any marked lines in the current view
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :echo, :echo, :eval, :pipe-line-to, :pipe-to,
|
|
:redirect-to, :redirect-to, :write-csv-to, :write-csv-to,
|
|
:write-json-to, :write-json-to, :write-jsonlines-to,
|
|
:write-jsonlines-to, :write-raw-to, :write-raw-to, :write-screen-to,
|
|
:write-screen-to, :write-table-to, :write-table-to, :write-view-to,
|
|
:write-view-to
|
|
Example
|
|
#1 To write marked lines to the file /tmp/interesting-lines.txt:
|
|
:write-to /tmp/interesting-lines.txt
|
|
|
|
|
|
|
|
:write-view-to path
|
|
══════════════════════════════════════════════════════════════════════
|
|
Write the text in the top view to the given file without any
|
|
formatting
|
|
Parameter
|
|
path The path to the file to write
|
|
See Also
|
|
:alt-msg, :append-to, :create-logline-table, :create-search-table,
|
|
:echo, :echo, :eval, :pipe-line-to, :pipe-to, :redirect-to,
|
|
:redirect-to, :write-csv-to, :write-csv-to, :write-csv-to,
|
|
:write-json-to, :write-json-to, :write-json-to, :write-jsonlines-to,
|
|
:write-jsonlines-to, :write-jsonlines-to, :write-raw-to, :write-raw-to,
|
|
:write-raw-to, :write-screen-to, :write-screen-to, :write-screen-to,
|
|
:write-table-to, :write-table-to, :write-table-to, :write-to,
|
|
:write-to
|
|
Example
|
|
#1 To write the top view to /tmp/table.txt:
|
|
:write-view-to /tmp/table.txt
|
|
|
|
|
|
|
|
:zoom-to zoom-level
|
|
══════════════════════════════════════════════════════════════════════
|
|
Zoom the histogram view to the given level
|
|
Parameter
|
|
zoom-level The zoom level
|
|
|
|
Example
|
|
#1 To set the zoom level to '1-week':
|
|
:zoom-to 1-week
|
|
|
|
|
|
SQL Reference
|
|
|
|
CAST(expr AS type-name)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Convert the value of the given expression to a different storage
|
|
class specified by type-name.
|
|
Parameters
|
|
expr The value to convert.
|
|
type-name The name of the type to convert to.
|
|
|
|
Example
|
|
#1 To cast the value 1.23 as an integer:
|
|
;SELECT CAST(1.23 AS INTEGER)
|
|
|
|
|
|
|
|
OVER([base-window-name] PARTITION BY expr, ... ORDER BY expr, ...,
|
|
[frame-spec])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Executes the preceding function over a window
|
|
Parameters
|
|
base-window-name The name of the window definition
|
|
expr The values to use for partitioning
|
|
expr The values used to order the rows in the window
|
|
frame-spec Determines which output rows are read by an
|
|
aggregate window function
|
|
|
|
|
|
abs(x)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Return the absolute value of the argument
|
|
Parameter
|
|
x The number to convert
|
|
See Also
|
|
acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the absolute value of -1:
|
|
;SELECT abs(-1)
|
|
|
|
|
|
|
|
acos(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the arccosine of a number, in radians
|
|
Parameter
|
|
num A cosine value that is between -1 and 1
|
|
See Also
|
|
abs(), acosh(), asin(), asinh(), atan(), atan2(), atanh(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the arccosine of 0.2:
|
|
;SELECT acos(0.2)
|
|
|
|
|
|
|
|
acosh(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the hyperbolic arccosine of a number
|
|
Parameter
|
|
num A number that is one or more
|
|
See Also
|
|
abs(), acos(), asin(), asinh(), atan(), atan2(), atanh(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the hyperbolic arccosine of 1.2:
|
|
;SELECT acosh(1.2)
|
|
|
|
|
|
|
|
asin(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the arcsine of a number, in radians
|
|
Parameter
|
|
num A sine value that is between -1 and 1
|
|
See Also
|
|
abs(), acos(), acosh(), asinh(), atan(), atan2(), atanh(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the arcsine of 0.2:
|
|
;SELECT asin(0.2)
|
|
|
|
|
|
|
|
asinh(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the hyperbolic arcsine of a number
|
|
Parameter
|
|
num The number
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), atan(), atan2(), atanh(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the hyperbolic arcsine of 0.2:
|
|
;SELECT asinh(0.2)
|
|
|
|
|
|
|
|
atan(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the arctangent of a number, in radians
|
|
Parameter
|
|
num The number
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan2(), atanh(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the arctangent of 0.2:
|
|
;SELECT atan(0.2)
|
|
|
|
|
|
|
|
atan2(y, x)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the angle in the plane between the positive X axis and the
|
|
ray from (0, 0) to the point (x, y)
|
|
Parameters
|
|
y The y coordinate of the point
|
|
x The x coordinate of the point
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atanh(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the angle, in degrees, for the point at (5, 5):
|
|
;SELECT degrees(atan2(5, 5))
|
|
|
|
|
|
|
|
atanh(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the hyperbolic arctangent of a number
|
|
Parameter
|
|
num The number
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atn2(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the hyperbolic arctangent of 0.2:
|
|
;SELECT atanh(0.2)
|
|
|
|
|
|
|
|
atn2(y, x)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the angle in the plane between the positive X axis and the
|
|
ray from (0, 0) to the point (x, y)
|
|
Parameters
|
|
y The y coordinate of the point
|
|
x The x coordinate of the point
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
avg(), ceil(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the angle, in degrees, for the point at (5, 5):
|
|
;SELECT degrees(atn2(5, 5))
|
|
|
|
|
|
|
|
avg(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the average value of all non-NULL numbers within a group.
|
|
Parameter
|
|
X The value to compute the average of.
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), ceil(), degrees(), exp(), floor(), log(), log10(), max(),
|
|
min(), pi(), power(), radians(), round(), sign(), square(), sum(),
|
|
total()
|
|
Examples
|
|
#1 To get the average of the column 'ex_duration' from the table 'lnav_example_log':
|
|
;SELECT avg(ex_duration) FROM lnav_example_log
|
|
|
|
|
|
#2 To get the average of the column 'ex_duration' from the table 'lnav_example_log' when
|
|
grouped by 'ex_procname':
|
|
;SELECT ex_procname, avg(ex_duration) FROM lnav_example_log GROUP BY ex_procname
|
|
|
|
|
|
|
|
basename(path)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Extract the base portion of a pathname.
|
|
Parameter
|
|
path The path
|
|
See Also
|
|
dirname(), joinpath(), readlink(), realpath()
|
|
Examples
|
|
#1 To get the base of a plain file name:
|
|
;SELECT basename('foobar')
|
|
|
|
|
|
#2 To get the base of a path:
|
|
;SELECT basename('foo/bar')
|
|
|
|
|
|
#3 To get the base of a directory:
|
|
;SELECT basename('foo/bar/')
|
|
|
|
|
|
#4 To get the base of an empty string:
|
|
;SELECT basename('')
|
|
|
|
|
|
#5 To get the base of a Windows path:
|
|
;SELECT basename('foo\bar')
|
|
|
|
|
|
#6 To get the base of the root directory:
|
|
;SELECT basename('/')
|
|
|
|
|
|
|
|
ceil(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the smallest integer that is not less than the argument
|
|
Parameter
|
|
num The number to raise to the ceiling
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), degrees(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the ceiling of 1.23:
|
|
;SELECT ceil(1.23)
|
|
|
|
|
|
|
|
changes()
|
|
══════════════════════════════════════════════════════════════════════
|
|
The number of database rows that were changed, inserted, or deleted
|
|
by the most recent statement.
|
|
|
|
|
|
char(X, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string composed of characters having the given unicode
|
|
code point values
|
|
Parameter
|
|
X The unicode code point values
|
|
See Also
|
|
charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(),
|
|
substr(), trim(), unicode(), upper(), xpath()
|
|
Example
|
|
#1 To get a string with the code points 0x48 and 0x49:
|
|
;SELECT char(0x48, 0x49)
|
|
|
|
|
|
|
|
charindex(needle, haystack, [start])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Finds the first occurrence of the needle within the haystack and
|
|
returns the number of prior characters plus 1, or 0 if Y is nowhere
|
|
found within X
|
|
Parameters
|
|
needle The string to look for in the haystack
|
|
haystack The string to search within
|
|
start The one-based index within the haystack to start the
|
|
search
|
|
See Also
|
|
char(), endswith(), extract(), group_concat(), group_spooky_hash(),
|
|
gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(),
|
|
logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(),
|
|
proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To search for the string 'abc' within 'abcabc' and starting at position 2:
|
|
;SELECT charindex('abc', 'abcabc', 2)
|
|
|
|
|
|
#2 To search for the string 'abc' within 'abcdef' and starting at position 2:
|
|
;SELECT charindex('abc', 'abcdef', 2)
|
|
|
|
|
|
|
|
coalesce(X, Y, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a copy of its first non-NULL argument, or NULL if all
|
|
arguments are NULL
|
|
Parameters
|
|
X A value to check for NULL-ness
|
|
Y A value to check for NULL-ness
|
|
|
|
Example
|
|
#1 To get the first non-null value from three parameters:
|
|
;SELECT coalesce(null, 0, null)
|
|
|
|
|
|
|
|
count(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
If the argument is '*', the total number of rows in the group is
|
|
returned. Otherwise, the number of times the argument is non-NULL.
|
|
Parameter
|
|
X The value to count.
|
|
|
|
Examples
|
|
#1 To get the count of the non-NULL rows of 'lnav_example_log':
|
|
;SELECT count(*) FROM lnav_example_log
|
|
|
|
|
|
#2 To get the count of the non-NULL values of 'log_part' from 'lnav_example_log':
|
|
;SELECT count(log_part) FROM lnav_example_log
|
|
|
|
|
|
|
|
cume_dist()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the cumulative distribution
|
|
See Also
|
|
dense_rank(), first_value(), lag(), last_value(), lead(), nth_value(),
|
|
ntile(), percent_rank(), rank(), row_number()
|
|
|
|
date(timestring, modifier, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the date in this format: YYYY-MM-DD.
|
|
Parameters
|
|
timestring The string to convert to a date.
|
|
modifier A transformation that is applied to the value to the
|
|
left.
|
|
See Also
|
|
datetime(), julianday(), strftime(), time(), timediff(), timeslice()
|
|
Examples
|
|
#1 To get the date portion of the timestamp '2017-01-02T03:04:05':
|
|
;SELECT date('2017-01-02T03:04:05')
|
|
|
|
|
|
#2 To get the date portion of the timestamp '2017-01-02T03:04:05' plus one day:
|
|
;SELECT date('2017-01-02T03:04:05', '+1 day')
|
|
|
|
|
|
#3 To get the date portion of the epoch timestamp 1491341842:
|
|
;SELECT date(1491341842, 'unixepoch')
|
|
|
|
|
|
|
|
datetime(timestring, modifier, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the date and time in this format: YYYY-MM-DD HH:MM:SS.
|
|
Parameters
|
|
timestring The string to convert to a date with time.
|
|
modifier A transformation that is applied to the value to the
|
|
left.
|
|
See Also
|
|
date(), julianday(), strftime(), time(), timediff(), timeslice()
|
|
Examples
|
|
#1 To get the date and time portion of the timestamp '2017-01-02T03:04:05':
|
|
;SELECT datetime('2017-01-02T03:04:05')
|
|
|
|
|
|
#2 To get the date and time portion of the timestamp '2017-01-02T03:04:05' plus one minute
|
|
:
|
|
;SELECT datetime('2017-01-02T03:04:05', '+1 minute')
|
|
|
|
|
|
#3 To get the date and time portion of the epoch timestamp 1491341842:
|
|
;SELECT datetime(1491341842, 'unixepoch')
|
|
|
|
|
|
|
|
degrees(radians)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Converts radians to degrees
|
|
Parameter
|
|
radians The radians value to convert to degrees
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), exp(), floor(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To convert PI to degrees:
|
|
;SELECT degrees(pi())
|
|
|
|
|
|
|
|
dense_rank()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the row_number() of the first peer in each group without
|
|
gaps
|
|
See Also
|
|
cume_dist(), first_value(), lag(), last_value(), lead(), nth_value(),
|
|
ntile(), percent_rank(), rank(), row_number()
|
|
|
|
dirname(path)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Extract the directory portion of a pathname.
|
|
Parameter
|
|
path The path
|
|
See Also
|
|
basename(), joinpath(), readlink(), realpath()
|
|
Examples
|
|
#1 To get the directory of a relative file path:
|
|
;SELECT dirname('foo/bar')
|
|
|
|
|
|
#2 To get the directory of an absolute file path:
|
|
;SELECT dirname('/foo/bar')
|
|
|
|
|
|
#3 To get the directory of a file in the root directory:
|
|
;SELECT dirname('/bar')
|
|
|
|
|
|
#4 To get the directory of a Windows path:
|
|
;SELECT dirname('foo\bar')
|
|
|
|
|
|
#5 To get the directory of an empty path:
|
|
;SELECT dirname('')
|
|
|
|
|
|
|
|
endswith(str, suffix)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Test if a string ends with the given suffix
|
|
Parameters
|
|
str The string to test
|
|
suffix The suffix to check in the string
|
|
See Also
|
|
char(), charindex(), extract(), group_concat(), group_spooky_hash(),
|
|
gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(),
|
|
logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(),
|
|
proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To test if the string 'notbad.jpg' ends with '.jpg':
|
|
;SELECT endswith('notbad.jpg', '.jpg')
|
|
|
|
|
|
#2 To test if the string 'notbad.png' starts with '.jpg':
|
|
;SELECT endswith('notbad.png', '.jpg')
|
|
|
|
|
|
|
|
exp(x)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the value of e raised to the power of x
|
|
Parameter
|
|
x The exponent
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), floor(), log(), log10(), max(),
|
|
min(), pi(), power(), radians(), round(), sign(), square(), sum(),
|
|
total()
|
|
Example
|
|
#1 To raise e to 2:
|
|
;SELECT exp(2)
|
|
|
|
|
|
|
|
extract(str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Automatically Parse and extract data from a string
|
|
Parameter
|
|
str The string to parse
|
|
See Also
|
|
char(), charindex(), endswith(), group_concat(), group_spooky_hash(),
|
|
gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(),
|
|
logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(),
|
|
proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To extract key/value pairs from a string:
|
|
;SELECT extract('foo=1 bar=2 name="Rolo Tomassi"')
|
|
|
|
|
|
#2 To extract columnar data from a string:
|
|
;SELECT extract('1.0 abc 2.0')
|
|
|
|
|
|
|
|
first_value(expr)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the result of evaluating the expression against the first
|
|
row in the window frame.
|
|
Parameter
|
|
expr The expression to execute over the first row
|
|
See Also
|
|
cume_dist(), dense_rank(), lag(), last_value(), lead(), nth_value(),
|
|
ntile(), percent_rank(), rank(), row_number()
|
|
|
|
floor(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the largest integer that is not greater than the argument
|
|
Parameter
|
|
num The number to lower to the floor
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), log(), log10(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the floor of 1.23:
|
|
;SELECT floor(1.23)
|
|
|
|
|
|
|
|
generate_series(start, stop, [step])
|
|
══════════════════════════════════════════════════════════════════════
|
|
A table-valued-function that returns the whole numbers between a
|
|
lower and upper bound, inclusive
|
|
Parameters
|
|
start The starting point of the series
|
|
stop The stopping point of the series
|
|
step The increment between each value
|
|
Result
|
|
value The number in the series
|
|
|
|
Examples
|
|
#1 To generate the numbers in the range [10, 14]:
|
|
;SELECT value FROM generate_series(10, 14)
|
|
|
|
|
|
#2 To generate every other number in the range [10, 14]:
|
|
;SELECT value FROM generate_series(10, 14, 2)
|
|
|
|
|
|
#3 To count down from five to 1:
|
|
;SELECT value FROM generate_series(1, 5, -1)
|
|
|
|
|
|
|
|
gethostbyaddr(hostname)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Get the hostname for the given IP address
|
|
Parameter
|
|
hostname The IP address to lookup.
|
|
See Also
|
|
gethostbyname()
|
|
Example
|
|
#1 To get the hostname for the IP '127.0.0.1':
|
|
;SELECT gethostbyaddr('127.0.0.1')
|
|
|
|
|
|
|
|
gethostbyname(hostname)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Get the IP address for the given hostname
|
|
Parameter
|
|
hostname The DNS hostname to lookup.
|
|
See Also
|
|
gethostbyaddr()
|
|
Example
|
|
#1 To get the IP address for 'localhost':
|
|
;SELECT gethostbyname('localhost')
|
|
|
|
|
|
|
|
glob(pattern, str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Match a string against Unix glob pattern
|
|
Parameters
|
|
pattern The glob pattern
|
|
str The string to match
|
|
|
|
Example
|
|
#1 To test if the string 'abc' matches the glob 'a*':
|
|
;SELECT glob('a*', 'abc')
|
|
|
|
|
|
|
|
group_concat(X, [sep])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string which is the concatenation of all non-NULL values
|
|
of X separated by a comma or the given separator.
|
|
Parameters
|
|
X The value to concatenate.
|
|
sep The separator to place between the values.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_spooky_hash(),
|
|
gunzip(), gzip(), humanize_file_size(), instr(), leftstr(), length(),
|
|
logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(),
|
|
proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To concatenate the values of the column 'ex_procname' from the table 'lnav_example_log'
|
|
:
|
|
;SELECT group_concat(ex_procname) FROM lnav_example_log
|
|
|
|
|
|
#2 To join the values of the column 'ex_procname' using the string ', ':
|
|
;SELECT group_concat(ex_procname, ', ') FROM lnav_example_log
|
|
|
|
|
|
#3 To concatenate the distinct values of the column 'ex_procname' from the table '
|
|
lnav_example_log':
|
|
;SELECT group_concat(DISTINCT ex_procname) FROM lnav_example_log
|
|
|
|
|
|
|
|
group_spooky_hash(str, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Compute the hash value for the given arguments
|
|
Parameter
|
|
str The string to hash
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(), gunzip(),
|
|
gzip(), humanize_file_size(), instr(), leftstr(), length(),
|
|
logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(),
|
|
proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To produce a hash of all of the values of 'column1':
|
|
;SELECT group_spooky_hash(column1) FROM (VALUES ('abc'), ('123'))
|
|
|
|
|
|
|
|
gunzip(b, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Decompress a gzip file
|
|
Parameter
|
|
b The blob to decompress
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gzip(), humanize_file_size(), instr(), leftstr(),
|
|
length(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
|
|
gzip(value, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Compress a string into a gzip file
|
|
Parameter
|
|
value The value to compress
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(),
|
|
substr(), trim(), unicode(), upper(), xpath()
|
|
|
|
hex(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string which is the upper-case hexadecimal rendering of
|
|
the content of its argument.
|
|
Parameter
|
|
X The blob to convert to hexadecimal
|
|
|
|
Example
|
|
#1 To get the hexadecimal rendering of the string 'abc':
|
|
;SELECT hex('abc')
|
|
|
|
|
|
|
|
humanize_file_size(value)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Format the given file size as a human-friendly string
|
|
Parameter
|
|
value The file size to format
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), instr(), leftstr(), length(),
|
|
logfmt2json(), lower(), ltrim(), padc(), padl(), padr(), printf(),
|
|
proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To format an amount:
|
|
;SELECT humanize_file_size(10 * 1024 * 1024)
|
|
|
|
|
|
|
|
ifnull(X, Y)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a copy of its first non-NULL argument, or NULL if both
|
|
arguments are NULL
|
|
Parameters
|
|
X A value to check for NULL-ness
|
|
Y A value to check for NULL-ness
|
|
|
|
Example
|
|
#1 To get the first non-null value between null and zero:
|
|
;SELECT ifnull(null, 0)
|
|
|
|
|
|
|
|
instr(haystack, needle)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Finds the first occurrence of the needle within the haystack and
|
|
returns the number of prior characters plus 1, or 0 if the needle
|
|
was not found
|
|
Parameters
|
|
haystack The string to search within
|
|
needle The string to look for in the haystack
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), leftstr(),
|
|
length(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To test get the position of 'b' in the string 'abc':
|
|
;SELECT instr('abc', 'b')
|
|
|
|
|
|
|
|
jget(json, ptr, [default])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Get the value from a JSON object using a JSON-Pointer.
|
|
Parameters
|
|
json The JSON object to query.
|
|
ptr The JSON-Pointer to lookup in the object.
|
|
default The default value if the value was not found
|
|
See Also
|
|
json_concat(), json_contains(), json_group_array(),
|
|
json_group_object()
|
|
Examples
|
|
#1 To get the root of a JSON value:
|
|
;SELECT jget('1', '')
|
|
|
|
|
|
#2 To get the property named 'b' in a JSON object:
|
|
;SELECT jget('{ "a": 1, "b": 2 }', '/b')
|
|
|
|
|
|
#3 To get the 'msg' property and return a default if it does not exist:
|
|
;SELECT jget(null, '/msg', 'Hello')
|
|
|
|
|
|
|
|
joinpath(path, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Join components of a path together.
|
|
Parameter
|
|
path One or more path components to join together. If an argument
|
|
starts with a forward or backward slash, it will be
|
|
considered an absolute path and any preceding elements will
|
|
be ignored.
|
|
See Also
|
|
basename(), dirname(), readlink(), realpath()
|
|
Examples
|
|
#1 To join a directory and file name into a relative path:
|
|
;SELECT joinpath('foo', 'bar')
|
|
|
|
|
|
#2 To join an empty component with other names into a relative path:
|
|
;SELECT joinpath('', 'foo', 'bar')
|
|
|
|
|
|
#3 To create an absolute path with two path components:
|
|
;SELECT joinpath('/', 'foo', 'bar')
|
|
|
|
|
|
#4 To create an absolute path from a path component that starts with a forward slash:
|
|
;SELECT joinpath('/', 'foo', '/bar')
|
|
|
|
|
|
|
|
json_concat(json, value, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns an array with the given values concatenated onto the end.
|
|
If the initial value is null, the result will be an array with the
|
|
given elements. If the initial value is an array, the result will
|
|
be an array with the given values at the end. If the initial value
|
|
is not null or an array, the result will be an array with two
|
|
elements: the initial value and the given value.
|
|
Parameters
|
|
json The initial JSON value.
|
|
value The value(s) to add to the end of the array.
|
|
See Also
|
|
jget(), json_contains(), json_group_array(), json_group_object()
|
|
Examples
|
|
#1 To append the number 4 to null:
|
|
;SELECT json_concat(NULL, 4)
|
|
|
|
|
|
#2 To append 4 and 5 to the array [1, 2, 3]:
|
|
;SELECT json_concat('[1, 2, 3]', 4, 5)
|
|
|
|
|
|
#3 To concatenate two arrays together:
|
|
;SELECT json_concat('[1, 2, 3]', json('[4, 5]'))
|
|
|
|
|
|
|
|
json_contains(json, value)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Check if a JSON value contains the given element.
|
|
Parameters
|
|
json The JSON value to query.
|
|
value The value to look for in the first argument
|
|
See Also
|
|
jget(), json_concat(), json_group_array(), json_group_object()
|
|
Examples
|
|
#1 To test if a JSON array contains the number 4:
|
|
;SELECT json_contains('[1, 2, 3]', 4)
|
|
|
|
|
|
#2 To test if a JSON array contains the string 'def':
|
|
;SELECT json_contains('["abc", "def"]', 'def')
|
|
|
|
|
|
|
|
json_group_array(value, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Collect the given values from a query into a JSON array
|
|
Parameter
|
|
value The values to append to the array
|
|
See Also
|
|
jget(), json_concat(), json_contains(), json_group_object()
|
|
Examples
|
|
#1 To create an array from arguments:
|
|
;SELECT json_group_array('one', 2, 3.4)
|
|
|
|
|
|
#2 To create an array from a column of values:
|
|
;SELECT json_group_array(column1) FROM (VALUES (1), (2), (3))
|
|
|
|
|
|
|
|
json_group_object(name, value, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Collect the given values from a query into a JSON object
|
|
Parameters
|
|
name The property name for the value
|
|
value The value to add to the object
|
|
See Also
|
|
jget(), json_concat(), json_contains(), json_group_array()
|
|
Examples
|
|
#1 To create an object from arguments:
|
|
;SELECT json_group_object('a', 1, 'b', 2)
|
|
|
|
|
|
#2 To create an object from a pair of columns:
|
|
;SELECT json_group_object(column1, column2) FROM (VALUES ('a', 1), ('b', 2))
|
|
|
|
|
|
|
|
julianday(timestring, modifier, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the number of days since noon in Greenwich on November 24,
|
|
4714 B.C.
|
|
Parameters
|
|
timestring The string to convert to a date with time.
|
|
modifier A transformation that is applied to the value to the
|
|
left.
|
|
See Also
|
|
date(), datetime(), strftime(), time(), timediff(), timeslice()
|
|
Examples
|
|
#1 To get the julian day from the timestamp '2017-01-02T03:04:05':
|
|
;SELECT julianday('2017-01-02T03:04:05')
|
|
|
|
|
|
#2 To get the julian day from the timestamp '2017-01-02T03:04:05' plus one minute:
|
|
;SELECT julianday('2017-01-02T03:04:05', '+1 minute')
|
|
|
|
|
|
#3 To get the julian day from the timestamp 1491341842:
|
|
;SELECT julianday(1491341842, 'unixepoch')
|
|
|
|
|
|
|
|
lag(expr, [offset], [default])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the result of evaluating the expression against the previous
|
|
row in the partition.
|
|
Parameters
|
|
expr The expression to execute over the previous row
|
|
offset The offset from the current row in the partition
|
|
default The default value if the previous row does not exist
|
|
instead of NULL
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), last_value(), lead(),
|
|
nth_value(), ntile(), percent_rank(), rank(), row_number()
|
|
|
|
last_insert_rowid()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the ROWID of the last row insert from the database
|
|
connection which invoked the function
|
|
|
|
|
|
last_value(expr)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the result of evaluating the expression against the last row
|
|
in the window frame.
|
|
Parameter
|
|
expr The expression to execute over the last row
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), lag(), lead(), nth_value(),
|
|
ntile(), percent_rank(), rank(), row_number()
|
|
|
|
lead(expr, [offset], [default])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the result of evaluating the expression against the next row
|
|
in the partition.
|
|
Parameters
|
|
expr The expression to execute over the next row
|
|
offset The offset from the current row in the partition
|
|
default The default value if the next row does not exist instead
|
|
of NULL
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), lag(), last_value(),
|
|
nth_value(), ntile(), percent_rank(), rank(), row_number()
|
|
|
|
leftstr(str, N)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the N leftmost (UTF-8) characters in the given string.
|
|
Parameters
|
|
str The string to return subset.
|
|
N The number of characters from the left side of the string to
|
|
return.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
length(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To get the first character of the string 'abc':
|
|
;SELECT leftstr('abc', 1)
|
|
|
|
|
|
#2 To get the first ten characters of a string, regardless of size:
|
|
;SELECT leftstr('abc', 10)
|
|
|
|
|
|
|
|
length(str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the number of characters (not bytes) in the given string
|
|
prior to the first NUL character
|
|
Parameter
|
|
str The string to determine the length of
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), logfmt2json(), lower(), ltrim(), padc(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To get the length of the string 'abc':
|
|
;SELECT length('abc')
|
|
|
|
|
|
|
|
like(pattern, str, [escape])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Match a string against a pattern
|
|
Parameters
|
|
pattern The pattern to match. A percent symbol (%) will match
|
|
zero or more characters and an underscore (_) will match a
|
|
single character.
|
|
str The string to match
|
|
escape The escape character that can be used to prefix a literal
|
|
percent or underscore in the pattern.
|
|
|
|
Examples
|
|
#1 To test if the string 'aabcc' contains the letter 'b':
|
|
;SELECT like('%b%', 'aabcc')
|
|
|
|
|
|
#2 To test if the string 'aab%' ends with 'b%':
|
|
;SELECT like('%b:%', 'aab%', ':')
|
|
|
|
|
|
|
|
likelihood(value, probability)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Provides a hint to the query planner that the first argument is a
|
|
boolean that is true with the given probability
|
|
Parameters
|
|
value The boolean value to return
|
|
probability A floating point constant between 0.0 and 1.0
|
|
|
|
|
|
likely(value)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Short-hand for likelihood(X,0.9375)
|
|
Parameter
|
|
value The boolean value to return
|
|
|
|
|
|
lnav_top_file()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Return the name of the file that the top line in the current view
|
|
came from.
|
|
|
|
|
|
lnav_version()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Return the current version of lnav
|
|
|
|
|
|
load_extension(path, [entry-point])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Loads SQLite extensions out of the given shared library file using
|
|
the given entry point.
|
|
Parameters
|
|
path The path to the shared library containing the
|
|
extension.
|
|
entry-point
|
|
|
|
|
|
log(x)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the natural logarithm of x
|
|
Parameter
|
|
x The number
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log10(), max(),
|
|
min(), pi(), power(), radians(), round(), sign(), square(), sum(),
|
|
total()
|
|
Example
|
|
#1 To get the natual logarithm of 8:
|
|
;SELECT log(8)
|
|
|
|
|
|
|
|
log10(x)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the base-10 logarithm of X
|
|
Parameter
|
|
x The number
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), max(), min(),
|
|
pi(), power(), radians(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To get the logarithm of 100:
|
|
;SELECT log10(100)
|
|
|
|
|
|
|
|
log_top_datetime()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Return the timestamp of the line at the top of the log view.
|
|
|
|
|
|
log_top_line()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Return the line number at the top of the log view.
|
|
|
|
|
|
logfmt2json(str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Convert a logfmt-encoded string into JSON
|
|
Parameter
|
|
str The logfmt message to parse
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), lower(), ltrim(), padc(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To extract key/value pairs from a log message:
|
|
;SELECT logfmt2json('foo=1 bar=2 name="Rolo Tomassi"')
|
|
|
|
|
|
|
|
lower(str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a copy of the given string with all ASCII characters
|
|
converted to lower case.
|
|
Parameter
|
|
str The string to convert.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), ltrim(), padc(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To lowercase the string 'AbC':
|
|
;SELECT lower('AbC')
|
|
|
|
|
|
|
|
ltrim(str, [chars])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string formed by removing any and all characters that
|
|
appear in the second argument from the left side of the first.
|
|
Parameters
|
|
str The string to trim characters from the left side
|
|
chars The characters to trim. Defaults to spaces.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), padc(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To trim the leading whitespace from the string ' abc':
|
|
;SELECT ltrim(' abc')
|
|
|
|
|
|
#2 To trim the characters 'a' or 'b' from the left side of the string 'aaaabbbc':
|
|
;SELECT ltrim('aaaabbbc', 'ab')
|
|
|
|
|
|
|
|
max(X, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the argument with the maximum value, or return NULL if any
|
|
argument is NULL.
|
|
Parameter
|
|
X The numbers to find the maximum of. If only one argument is
|
|
given, this function operates as an aggregate.
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
min(), pi(), power(), radians(), round(), sign(), square(), sum(),
|
|
total()
|
|
Examples
|
|
#1 To get the largest value from the parameters:
|
|
;SELECT max(2, 1, 3)
|
|
|
|
|
|
#2 To get the largest value from an aggregate:
|
|
;SELECT max(status) FROM http_status_codes
|
|
|
|
|
|
|
|
min(X, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the argument with the minimum value, or return NULL if any
|
|
argument is NULL.
|
|
Parameter
|
|
X The numbers to find the minimum of. If only one argument is
|
|
given, this function operates as an aggregate.
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), pi(), power(), radians(), round(), sign(), square(), sum(),
|
|
total()
|
|
Examples
|
|
#1 To get the smallest value from the parameters:
|
|
;SELECT min(2, 1, 3)
|
|
|
|
|
|
#2 To get the smallest value from an aggregate:
|
|
;SELECT min(status) FROM http_status_codes
|
|
|
|
|
|
|
|
nth_value(expr, N)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the result of evaluating the expression against the nth row
|
|
in the window frame.
|
|
Parameters
|
|
expr The expression to execute over the nth row
|
|
N The row number
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(),
|
|
ntile(), percent_rank(), rank(), row_number()
|
|
|
|
ntile(groups)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the number of the group that the current row is a part of
|
|
Parameter
|
|
groups The number of groups
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(),
|
|
nth_value(), percent_rank(), rank(), row_number()
|
|
|
|
nullif(X, Y)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns its first argument if the arguments are different and NULL
|
|
if the arguments are the same.
|
|
Parameters
|
|
X The first argument to compare.
|
|
Y The argument to compare against the first.
|
|
|
|
Examples
|
|
#1 To test if 1 is different from 1:
|
|
;SELECT nullif(1, 1)
|
|
|
|
|
|
#2 To test if 1 is different from 2:
|
|
;SELECT nullif(1, 2)
|
|
|
|
|
|
|
|
padc(str, len)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Pad the given string with enough spaces to make it centered within
|
|
the given length
|
|
Parameters
|
|
str The string to pad
|
|
len The minimum desired length of the output string
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padl(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To pad the string 'abc' to a length of six characters:
|
|
;SELECT padc('abc', 6) || 'def'
|
|
|
|
|
|
#2 To pad the string 'abcdef' to a length of eight characters:
|
|
;SELECT padc('abcdef', 8) || 'ghi'
|
|
|
|
|
|
|
|
padl(str, len)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Pad the given string with leading spaces until it reaches the
|
|
desired length
|
|
Parameters
|
|
str The string to pad
|
|
len The minimum desired length of the output string
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padr(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To pad the string 'abc' to a length of six characters:
|
|
;SELECT padl('abc', 6)
|
|
|
|
|
|
#2 To pad the string 'abcdef' to a length of four characters:
|
|
;SELECT padl('abcdef', 4)
|
|
|
|
|
|
|
|
padr(str, len)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Pad the given string with trailing spaces until it reaches the
|
|
desired length
|
|
Parameters
|
|
str The string to pad
|
|
len The minimum desired length of the output string
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
printf(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To pad the string 'abc' to a length of six characters:
|
|
;SELECT padr('abc', 6) || 'def'
|
|
|
|
|
|
#2 To pad the string 'abcdef' to a length of four characters:
|
|
;SELECT padr('abcdef', 4) || 'ghi'
|
|
|
|
|
|
|
|
percent_rank()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns (rank - 1) / (partition-rows - 1)
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(),
|
|
nth_value(), ntile(), rank(), row_number()
|
|
|
|
pi()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the value of PI
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), power(), radians(), round(), sign(), square(), sum(),
|
|
total()
|
|
Example
|
|
#1 To get the value of PI:
|
|
;SELECT pi()
|
|
|
|
|
|
|
|
power(base, exp)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the base to the given exponent
|
|
Parameters
|
|
base The base number
|
|
exp The exponent
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), pi(), radians(), round(), sign(), square(), sum(),
|
|
total()
|
|
Example
|
|
#1 To raise two to the power of three:
|
|
;SELECT power(2, 3)
|
|
|
|
|
|
|
|
printf(format, X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string with this functions arguments substituted into the
|
|
given format. Substitution points are specified using percent (%)
|
|
options, much like the standard C printf() function.
|
|
Parameters
|
|
format The format of the string to return.
|
|
X The argument to substitute at a given position in the
|
|
format.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), proper(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Examples
|
|
#1 To substitute 'World' into the string 'Hello, %s!':
|
|
;SELECT printf('Hello, %s!', 'World')
|
|
|
|
|
|
#2 To right-align 'small' in the string 'align:' with a column width of 10:
|
|
;SELECT printf('align: % 10s', 'small')
|
|
|
|
|
|
#3 To format 11 with a width of five characters and leading zeroes:
|
|
;SELECT printf('value: %05d', 11)
|
|
|
|
|
|
|
|
proper(str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Capitalize the first character of words in the given string
|
|
Parameter
|
|
str The string to capitalize.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), regexp_capture(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To capitalize the words in the string 'hello, world!':
|
|
;SELECT proper('hello, world!')
|
|
|
|
|
|
|
|
quote(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the text of an SQL literal which is the value of its
|
|
argument suitable for inclusion into an SQL statement.
|
|
Parameter
|
|
X The string to quote.
|
|
|
|
Examples
|
|
#1 To quote the string 'abc':
|
|
;SELECT quote('abc')
|
|
|
|
|
|
#2 To quote the string 'abc'123':
|
|
;SELECT quote('abc''123')
|
|
|
|
|
|
|
|
radians(degrees)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Converts degrees to radians
|
|
Parameter
|
|
degrees The degrees value to convert to radians
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), pi(), power(), round(), sign(), square(), sum(), total()
|
|
Example
|
|
#1 To convert 180 degrees to radians:
|
|
;SELECT radians(180)
|
|
|
|
|
|
|
|
raise_error(msg)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Raises an error with the given message when executed
|
|
Parameter
|
|
msg The error message
|
|
|
|
|
|
random()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a pseudo-random integer between -9223372036854775808 and +
|
|
9223372036854775807.
|
|
|
|
|
|
randomblob(N)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Return an N-byte blob containing pseudo-random bytes.
|
|
Parameter
|
|
N The size of the blob in bytes.
|
|
|
|
|
|
rank()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the row_number() of the first peer in each group with gaps
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(),
|
|
nth_value(), ntile(), percent_rank(), row_number()
|
|
|
|
readlink(path)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Read the target of a symbolic link.
|
|
Parameter
|
|
path The path to the symbolic link.
|
|
See Also
|
|
basename(), dirname(), joinpath(), realpath()
|
|
|
|
realpath(path)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the resolved version of the given path, expanding symbolic
|
|
links and resolving '.' and '..' references.
|
|
Parameter
|
|
path The path to resolve.
|
|
See Also
|
|
basename(), dirname(), joinpath(), readlink()
|
|
|
|
regexp(re, str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Test if a string matches a regular expression
|
|
Parameters
|
|
re The regular expression to use
|
|
str The string to test against the regular expression
|
|
|
|
|
|
regexp_capture(string, pattern)
|
|
══════════════════════════════════════════════════════════════════════
|
|
A table-valued function that executes a regular-expression over a
|
|
string and returns the captured values. If the regex only matches a
|
|
subset of the input string, it will be rerun on the remaining parts
|
|
of the string until no more matches are found.
|
|
Parameters
|
|
string The string to match against the given pattern.
|
|
pattern The regular expression to match.
|
|
Results
|
|
match_index The match iteration. This value will increase each
|
|
time a new match is found in the input string.
|
|
capture_index The index of the capture in the regex.
|
|
capture_name The name of the capture in the regex.
|
|
capture_count The total number of captures in the regex.
|
|
range_start The start of the capture in the input string.
|
|
range_stop The stop of the capture in the input string.
|
|
content The captured value from the string.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_match(), regexp_replace(),
|
|
replace(), replicate(), reverse(), rightstr(), rtrim(), sparkline(),
|
|
spooky_hash(), startswith(), strfilter(), substr(), trim(), unicode(),
|
|
upper(), xpath()
|
|
Example
|
|
#1 To extract the key/value pairs 'a'/1 and 'b'/2 from the string 'a=1; b=2':
|
|
;SELECT * FROM regexp_capture('a=1; b=2', '(\w+)=(\d+)')
|
|
|
|
|
|
|
|
regexp_match(re, str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Match a string against a regular expression and return the capture
|
|
groups as JSON.
|
|
Parameters
|
|
re The regular expression to use
|
|
str The string to test against the regular expression
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_replace(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(),
|
|
substr(), trim(), unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To capture the digits from the string '123':
|
|
;SELECT regexp_match('(\d+)', '123')
|
|
|
|
|
|
#2 To capture a number and word into a JSON object with the properties 'col_0' and 'col_1'
|
|
:
|
|
;SELECT regexp_match('(\d+) (\w+)', '123 four')
|
|
|
|
|
|
#3 To capture a number and word into a JSON object with the named properties 'num' and '
|
|
str':
|
|
;SELECT regexp_match('(?<num>\d+) (?<str>\w+)', '123 four')
|
|
|
|
|
|
|
|
regexp_replace(str, re, repl)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Replace the parts of a string that match a regular expression.
|
|
Parameters
|
|
str The string to perform replacements on
|
|
re The regular expression to match
|
|
repl The replacement string. You can reference capture groups
|
|
with a backslash followed by the number of the group,
|
|
starting with 1.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_match(), replace(), replicate(), reverse(), rightstr(), rtrim(),
|
|
sparkline(), spooky_hash(), startswith(), strfilter(), substr(),
|
|
trim(), unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To replace the word at the start of the string 'Hello, World!' with 'Goodbye':
|
|
;SELECT regexp_replace('Hello, World!', '^(\w+)', 'Goodbye')
|
|
|
|
|
|
#2 To wrap alphanumeric words with angle brackets:
|
|
;SELECT regexp_replace('123 abc', '(\w+)', '<\1>')
|
|
|
|
|
|
|
|
replace(str, old, replacement)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string formed by substituting the replacement string for
|
|
every occurrence of the old string in the given string.
|
|
Parameters
|
|
str The string to perform substitutions on.
|
|
old The string to be replaced.
|
|
replacement The string to replace any occurrences of the old
|
|
string with.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replicate(), reverse(), rightstr(), rtrim(),
|
|
sparkline(), spooky_hash(), startswith(), strfilter(), substr(),
|
|
trim(), unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To replace the string 'x' with 'z' in 'abc':
|
|
;SELECT replace('abc', 'x', 'z')
|
|
|
|
|
|
#2 To replace the string 'a' with 'z' in 'abc':
|
|
;SELECT replace('abc', 'a', 'z')
|
|
|
|
|
|
|
|
replicate(str, N)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the given string concatenated N times.
|
|
Parameters
|
|
str The string to replicate.
|
|
N The number of times to replicate the string.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), reverse(), rightstr(), rtrim(),
|
|
sparkline(), spooky_hash(), startswith(), strfilter(), substr(),
|
|
trim(), unicode(), upper(), xpath()
|
|
Example
|
|
#1 To repeat the string 'abc' three times:
|
|
;SELECT replicate('abc', 3)
|
|
|
|
|
|
|
|
reverse(str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the reverse of the given string.
|
|
Parameter
|
|
str The string to reverse.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), rightstr(), rtrim(),
|
|
sparkline(), spooky_hash(), startswith(), strfilter(), substr(),
|
|
trim(), unicode(), upper(), xpath()
|
|
Example
|
|
#1 To reverse the string 'abc':
|
|
;SELECT reverse('abc')
|
|
|
|
|
|
|
|
rightstr(str, N)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the N rightmost (UTF-8) characters in the given string.
|
|
Parameters
|
|
str The string to return subset.
|
|
N The number of characters from the right side of the string to
|
|
return.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rtrim(),
|
|
sparkline(), spooky_hash(), startswith(), strfilter(), substr(),
|
|
trim(), unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To get the last character of the string 'abc':
|
|
;SELECT rightstr('abc', 1)
|
|
|
|
|
|
#2 To get the last ten characters of a string, regardless of size:
|
|
;SELECT rightstr('abc', 10)
|
|
|
|
|
|
|
|
round(num, [digits])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a floating-point value rounded to the given number of digits
|
|
to the right of the decimal point.
|
|
Parameters
|
|
num The value to round.
|
|
digits The number of digits to the right of the decimal to round
|
|
to.
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), pi(), power(), radians(), sign(), square(), sum(),
|
|
total()
|
|
Examples
|
|
#1 To round the number 123.456 to an integer:
|
|
;SELECT round(123.456)
|
|
|
|
|
|
#2 To round the number 123.456 to a precision of 1:
|
|
;SELECT round(123.456, 1)
|
|
|
|
|
|
#3 To round the number 123.456 to a precision of 5:
|
|
;SELECT round(123.456, 5)
|
|
|
|
|
|
|
|
row_number()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the number of the row within the current partition, starting
|
|
from 1.
|
|
See Also
|
|
cume_dist(), dense_rank(), first_value(), lag(), last_value(), lead(),
|
|
nth_value(), ntile(), percent_rank(), rank()
|
|
Example
|
|
#1 To number messages from a process:
|
|
;SELECT row_number() OVER (PARTITION BY ex_procname ORDER BY log_line) AS msg_num,
|
|
ex_procname, log_body FROM lnav_example_log
|
|
|
|
|
|
|
|
rtrim(str, [chars])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string formed by removing any and all characters that
|
|
appear in the second argument from the right side of the first.
|
|
Parameters
|
|
str The string to trim characters from the right side
|
|
chars The characters to trim. Defaults to spaces.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
sparkline(), spooky_hash(), startswith(), strfilter(), substr(),
|
|
trim(), unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To trim the whitespace from the end of the string 'abc ':
|
|
;SELECT rtrim('abc ')
|
|
|
|
|
|
#2 To trim the characters 'b' and 'c' from the string 'abbbbcccc':
|
|
;SELECT rtrim('abbbbcccc', 'bc')
|
|
|
|
|
|
|
|
sign(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the sign of the given number as -1, 0, or 1
|
|
Parameter
|
|
num The number
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), pi(), power(), radians(), round(), square(), sum(),
|
|
total()
|
|
Examples
|
|
#1 To get the sign of 10:
|
|
;SELECT sign(10)
|
|
|
|
|
|
#2 To get the sign of 0:
|
|
;SELECT sign(0)
|
|
|
|
|
|
#3 To get the sign of -10:
|
|
;SELECT sign(-10)
|
|
|
|
|
|
|
|
sparkline(value, [upper])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Function used to generate a sparkline bar chart. The non-aggregate
|
|
version converts a single numeric value on a range to a bar chart
|
|
character. The aggregate version returns a string with a bar
|
|
character for every numeric input
|
|
Parameters
|
|
value The numeric value to convert
|
|
upper The upper bound of the numeric range. The non-aggregate
|
|
version defaults to 100. The aggregate version uses the
|
|
largest value in the inputs.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), spooky_hash(), startswith(), strfilter(), substr(), trim(),
|
|
unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To get the unicode block element for the value 32 in the range of 0-128:
|
|
;SELECT sparkline(32, 128)
|
|
|
|
|
|
#2 To chart the values in a JSON array:
|
|
;SELECT sparkline(value) FROM json_each('[0, 1, 2, 3, 4, 5, 6, 7, 8]')
|
|
|
|
|
|
|
|
spooky_hash(str, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Compute the hash value for the given arguments.
|
|
Parameter
|
|
str The string to hash
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), startswith(), strfilter(), substr(), trim(),
|
|
unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To produce a hash for the string 'Hello, World!':
|
|
;SELECT spooky_hash('Hello, World!')
|
|
|
|
|
|
#2 To produce a hash for the parameters where one is NULL:
|
|
;SELECT spooky_hash('Hello, World!', NULL)
|
|
|
|
|
|
#3 To produce a hash for the parameters where one is an empty string:
|
|
;SELECT spooky_hash('Hello, World!', '')
|
|
|
|
|
|
#4 To produce a hash for the parameters where one is a number:
|
|
;SELECT spooky_hash('Hello, World!', 123)
|
|
|
|
|
|
|
|
sqlite_compileoption_get(N)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the N-th compile-time option used to build SQLite or NULL if
|
|
N is out of range.
|
|
Parameter
|
|
N The option number to get
|
|
|
|
|
|
sqlite_compileoption_used(option)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns true (1) or false (0) depending on whether or not that
|
|
compile-time option was used during the build.
|
|
Parameter
|
|
option The name of the compile-time option.
|
|
|
|
Example
|
|
#1 To check if the SQLite library was compiled with ENABLE_FTS3:
|
|
;SELECT sqlite_compileoption_used('ENABLE_FTS3')
|
|
|
|
|
|
|
|
sqlite_source_id()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string that identifies the specific version of the source
|
|
code that was used to build the SQLite library.
|
|
|
|
|
|
sqlite_version()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the version string for the SQLite library that is running.
|
|
|
|
|
|
square(num)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the square of the argument
|
|
Parameter
|
|
num The number to square
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), pi(), power(), radians(), round(), sign(), sum(),
|
|
total()
|
|
Example
|
|
#1 To get the square of two:
|
|
;SELECT square(2)
|
|
|
|
|
|
|
|
startswith(str, prefix)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Test if a string begins with the given prefix
|
|
Parameters
|
|
str The string to test
|
|
prefix The prefix to check in the string
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), strfilter(), substr(), trim(),
|
|
unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To test if the string 'foobar' starts with 'foo':
|
|
;SELECT startswith('foobar', 'foo')
|
|
|
|
|
|
#2 To test if the string 'foobar' starts with 'bar':
|
|
;SELECT startswith('foobar', 'bar')
|
|
|
|
|
|
|
|
strfilter(source, include)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the source string with only the characters given in the
|
|
second parameter
|
|
Parameters
|
|
source The string to filter
|
|
include The characters to include in the result
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), substr(), trim(),
|
|
unicode(), upper(), xpath()
|
|
Example
|
|
#1 To get the 'b', 'c', and 'd' characters from the string 'abcabc':
|
|
;SELECT strfilter('abcabc', 'bcd')
|
|
|
|
|
|
|
|
strftime(format, timestring, modifier, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the date formatted according to the format string specified
|
|
as the first argument.
|
|
Parameters
|
|
format A format string with substitutions similar to those
|
|
found in the strftime() standard C library.
|
|
timestring The string to convert to a date with time.
|
|
modifier A transformation that is applied to the value to the
|
|
left.
|
|
See Also
|
|
date(), datetime(), julianday(), time(), timediff(), timeslice()
|
|
Examples
|
|
#1 To get the year from the timestamp '2017-01-02T03:04:05':
|
|
;SELECT strftime('%Y', '2017-01-02T03:04:05')
|
|
|
|
|
|
#2 To create a string with the time from the timestamp '2017-01-02T03:04:05' plus one
|
|
minute:
|
|
;SELECT strftime('The time is: %H:%M:%S', '2017-01-02T03:04:05', '+1 minute')
|
|
|
|
|
|
#3 To create a string with the Julian day from the epoch timestamp 1491341842:
|
|
;SELECT strftime('Julian day: %J', 1491341842, 'unixepoch')
|
|
|
|
|
|
|
|
substr(str, start, [size])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a substring of input string X that begins with the Y-th
|
|
character and which is Z characters long.
|
|
Parameters
|
|
str The string to extract a substring from.
|
|
start The index within 'str' that is the start of the substring.
|
|
Indexes begin at 1. A negative value means that the
|
|
substring is found by counting from the right rather than
|
|
the left.
|
|
size The size of the substring. If not given, then all
|
|
characters through the end of the string are returned. If
|
|
the value is negative, then the characters before the start
|
|
are returned.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(), trim(),
|
|
unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To get the substring starting at the second character until the end of the string 'abc'
|
|
:
|
|
;SELECT substr('abc', 2)
|
|
|
|
|
|
#2 To get the substring of size one starting at the second character of the string 'abc':
|
|
;SELECT substr('abc', 2, 1)
|
|
|
|
|
|
#3 To get the substring starting at the last character until the end of the string 'abc':
|
|
;SELECT substr('abc', -1)
|
|
|
|
|
|
#4 To get the substring starting at the last character and going backwards one step of the
|
|
string 'abc':
|
|
;SELECT substr('abc', -1, -1)
|
|
|
|
|
|
|
|
sum(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the sum of the values in the group as an integer.
|
|
Parameter
|
|
X The values to add.
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), pi(), power(), radians(), round(), sign(), square(),
|
|
total()
|
|
Example
|
|
#1 To sum all of the values in the column 'ex_duration' from the table 'lnav_example_log':
|
|
;SELECT sum(ex_duration) FROM lnav_example_log
|
|
|
|
|
|
|
|
time(timestring, modifier, ...)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the time in this format: HH:MM:SS.
|
|
Parameters
|
|
timestring The string to convert to a time.
|
|
modifier A transformation that is applied to the value to the
|
|
left.
|
|
See Also
|
|
date(), datetime(), julianday(), strftime(), timediff(), timeslice()
|
|
Examples
|
|
#1 To get the time portion of the timestamp '2017-01-02T03:04:05':
|
|
;SELECT time('2017-01-02T03:04:05')
|
|
|
|
|
|
#2 To get the time portion of the timestamp '2017-01-02T03:04:05' plus one minute:
|
|
;SELECT time('2017-01-02T03:04:05', '+1 minute')
|
|
|
|
|
|
#3 To get the time portion of the epoch timestamp 1491341842:
|
|
;SELECT time(1491341842, 'unixepoch')
|
|
|
|
|
|
|
|
timediff(time1, time2)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Compute the difference between two timestamps in seconds
|
|
Parameters
|
|
time1 The first timestamp
|
|
time2 The timestamp to subtract from the first
|
|
See Also
|
|
date(), datetime(), julianday(), strftime(), time(), timeslice()
|
|
Examples
|
|
#1 To get the difference between two timestamps:
|
|
;SELECT timediff('2017-02-03T04:05:06', '2017-02-03T04:05:00')
|
|
|
|
|
|
#2 To get the difference between relative timestamps:
|
|
;SELECT timediff('today', 'yesterday')
|
|
|
|
|
|
|
|
timeslice(time, slice)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Return the start of the slice of time that the given timestamp falls
|
|
in. If the time falls outside of the slice, NULL is returned.
|
|
Parameters
|
|
time The timestamp to get the time slice for.
|
|
slice The size of the time slices
|
|
See Also
|
|
date(), datetime(), julianday(), strftime(), time(), timediff()
|
|
Examples
|
|
#1 To get the timestamp rounded down to the start of the ten minute slice:
|
|
;SELECT timeslice('2017-01-01T05:05:00', '10m')
|
|
|
|
|
|
#2 To group log messages into five minute buckets and count them:
|
|
;SELECT timeslice(log_time_msecs, '5m') AS slice, count(1)
|
|
FROM lnav_example_log GROUP BY slice
|
|
|
|
|
|
#3 To group log messages by those before 4:30am and after:
|
|
;SELECT timeslice(log_time_msecs, 'before 4:30am') AS slice, count(1) FROM
|
|
lnav_example_log GROUP BY slice
|
|
|
|
|
|
|
|
total(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the sum of the values in the group as a floating-point.
|
|
Parameter
|
|
X The values to add.
|
|
See Also
|
|
abs(), acos(), acosh(), asin(), asinh(), atan(), atan2(), atanh(),
|
|
atn2(), avg(), ceil(), degrees(), exp(), floor(), log(), log10(),
|
|
max(), min(), pi(), power(), radians(), round(), sign(), square(),
|
|
sum()
|
|
Example
|
|
#1 To total all of the values in the column 'ex_duration' from the table 'lnav_example_log
|
|
':
|
|
;SELECT total(ex_duration) FROM lnav_example_log
|
|
|
|
|
|
|
|
total_changes()
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the number of row changes caused by INSERT, UPDATE or DELETE
|
|
statements since the current database connection was opened.
|
|
|
|
|
|
trim(str, [chars])
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string formed by removing any and all characters that
|
|
appear in the second argument from the left and right sides of the
|
|
first.
|
|
Parameters
|
|
str The string to trim characters from the left and right sides.
|
|
|
|
chars The characters to trim. Defaults to spaces.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(),
|
|
substr(), unicode(), upper(), xpath()
|
|
Examples
|
|
#1 To trim whitespace from the start and end of the string ' abc ':
|
|
;SELECT trim(' abc ')
|
|
|
|
|
|
#2 To trim the characters '-' and '+' from the string '-+abc+-':
|
|
;SELECT trim('-+abc+-', '-+')
|
|
|
|
|
|
|
|
typeof(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a string that indicates the datatype of the expression X: "
|
|
null", "integer", "real", "text", or "blob".
|
|
Parameter
|
|
X The expression to check.
|
|
|
|
Examples
|
|
#1 To get the type of the number 1:
|
|
;SELECT typeof(1)
|
|
|
|
|
|
#2 To get the type of the string 'abc':
|
|
;SELECT typeof('abc')
|
|
|
|
|
|
|
|
unicode(X)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns the numeric unicode code point corresponding to the first
|
|
character of the string X.
|
|
Parameter
|
|
X The string to examine.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(),
|
|
substr(), trim(), upper(), xpath()
|
|
Example
|
|
#1 To get the unicode code point for the first character of 'abc':
|
|
;SELECT unicode('abc')
|
|
|
|
|
|
|
|
unlikely(value)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Short-hand for likelihood(X, 0.0625)
|
|
Parameter
|
|
value The boolean value to return
|
|
|
|
|
|
upper(str)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a copy of the given string with all ASCII characters
|
|
converted to upper case.
|
|
Parameter
|
|
str The string to convert.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(),
|
|
substr(), trim(), unicode(), xpath()
|
|
Example
|
|
#1 To uppercase the string 'aBc':
|
|
;SELECT upper('aBc')
|
|
|
|
|
|
|
|
xpath(xpath, xmldoc)
|
|
══════════════════════════════════════════════════════════════════════
|
|
A table-valued function that executes an xpath expression over an
|
|
XML string and returns the selected values.
|
|
Parameters
|
|
xpath The XPATH expression to evaluate over the XML document.
|
|
xmldoc The XML document as a string.
|
|
Results
|
|
result The result of the XPATH expression.
|
|
node_path The absolute path to the node containing the result.
|
|
node_attr The node's attributes stored in JSON object.
|
|
node_text The node's text value.
|
|
See Also
|
|
char(), charindex(), endswith(), extract(), group_concat(),
|
|
group_spooky_hash(), gunzip(), gzip(), humanize_file_size(), instr(),
|
|
leftstr(), length(), logfmt2json(), lower(), ltrim(), padc(), padl(),
|
|
padr(), printf(), proper(), regexp_capture(), regexp_match(),
|
|
regexp_replace(), replace(), replicate(), reverse(), rightstr(),
|
|
rtrim(), sparkline(), spooky_hash(), startswith(), strfilter(),
|
|
substr(), trim(), unicode(), upper()
|
|
Examples
|
|
#1 To select the XML nodes on the path '/abc/def':
|
|
;SELECT * FROM xpath('/abc/def', '<abc><def a="b">Hello</def><def>Bye</def></abc>')
|
|
|
|
|
|
#2 To select all 'a' attributes on the path '/abc/def':
|
|
;SELECT * FROM xpath('/abc/def/@a', '<abc><def a="b">Hello</def><def>Bye</def></abc>')
|
|
|
|
|
|
#3 To select the text nodes on the path '/abc/def':
|
|
;SELECT * FROM xpath('/abc/def/text()', '<abc><def a="b">Hello ★</def></abc>')
|
|
|
|
|
|
|
|
zeroblob(N)
|
|
══════════════════════════════════════════════════════════════════════
|
|
Returns a BLOB consisting of N bytes of 0x00.
|
|
Parameter
|
|
N The size of the BLOB.
|
|
|
|
|
|
ATTACH DATABASE filename AS schema-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Attach a database file to the current connection.
|
|
Parameters
|
|
filename The path to the database file.
|
|
schema-name The prefix for tables in this database.
|
|
|
|
Example
|
|
#1 To attach the database file '/tmp/customers.db' with the name customers:
|
|
;ATTACH DATABASE '/tmp/customers.db' AS customers
|
|
|
|
|
|
|
|
CASE [base-expr] WHEN cmp-expr1 THEN then-expr1 [... WHEN cmp-exprN THEN then-exprN] [ELSE else-expr] END
|
|
══════════════════════════════════════════════════════════════════════
|
|
Evaluate a series of expressions in order until one evaluates to
|
|
true and then return it's result. Similar to an IF-THEN-ELSE
|
|
construct in other languages.
|
|
Parameters
|
|
base-expr The base expression that is used for comparison in the
|
|
branches
|
|
cmp-expr The expression to test if this branch should be taken
|
|
else-expr The result of this CASE if no branches matched.
|
|
|
|
Example
|
|
#1 To evaluate the number one and return the string 'one':
|
|
;SELECT CASE 1 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' END
|
|
|
|
|
|
|
|
CREATE [TEMP] VIEW [IF NOT EXISTS] [schema-name.] view-name AS select-stmt
|
|
══════════════════════════════════════════════════════════════════════
|
|
Assign a name to a SELECT statement
|
|
Parameters
|
|
IF NOT EXISTS Do not create the view if it already exists
|
|
schema-name. The database to create the view in
|
|
view-name The name of the view
|
|
select-stmt The SELECT statement the view represents
|
|
|
|
|
|
DELETE FROM table-name [WHERE cond]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Delete rows from a table
|
|
Parameters
|
|
table-name The name of the table
|
|
cond The conditions used to delete the rows.
|
|
|
|
|
|
DETACH DATABASE schema-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Detach a database from the current connection.
|
|
Parameter
|
|
schema-name The prefix for tables in this database.
|
|
|
|
Example
|
|
#1 To detach the database named 'customers':
|
|
;DETACH DATABASE customers
|
|
|
|
|
|
|
|
DROP VIEW [IF EXISTS] [schema-name.] view-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Drop a view
|
|
Parameters
|
|
|
|
|
|
INSERT INTO [schema-name.] table-name [( column-name1 [, ... column-nameN] )] VALUES ( expr1 [, ... exprN] )
|
|
══════════════════════════════════════════════════════════════════════
|
|
Insert rows into a table
|
|
Parameters
|
|
|
|
Example
|
|
#1 To insert the pair containing 'MSG' and 'HELLO, WORLD!' into the 'environ'
|
|
table:
|
|
;INSERT INTO environ VALUES ('MSG', 'HELLO, WORLD!')
|
|
|
|
|
|
|
|
OVER window-name
|
|
══════════════════════════════════════════════════════════════════════
|
|
Executes the preceding function over a window
|
|
Parameter
|
|
window-name The name of the window definition
|
|
|
|
|
|
SELECT result-column1 [, ... result-columnN] [FROM table1 [, ... tableN]] [WHERE cond] [GROUP BY grouping-expr1 [, ... grouping-exprN]] [ORDER BY ordering-term1 [, ... ordering-termN]] [LIMIT limit-expr1 [, ... limit-exprN]]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Query the database and return zero or more rows of data.
|
|
Parameters
|
|
result-column The expression used to generate a result for this
|
|
column.
|
|
table The table(s) to query for data
|
|
cond The conditions used to select the rows to return.
|
|
grouping-expr The expression to use when grouping rows.
|
|
ordering-term The values to use when ordering the result set.
|
|
limit-expr The maximum number of rows to return.
|
|
|
|
Example
|
|
#1 To select all of the columns from the table 'syslog_log':
|
|
;SELECT * FROM syslog_log
|
|
|
|
|
|
|
|
UPDATE table SET column-name1 = expr1 [, ... column-nameN = exprN] [WHERE cond]
|
|
══════════════════════════════════════════════════════════════════════
|
|
Modify a subset of values in zero or more rows of the given table
|
|
Parameters
|
|
table The table to update
|
|
column-name The columns in the table to update.
|
|
cond The condition used to determine whether a row should
|
|
be updated.
|
|
|
|
Example
|
|
#1 To mark the syslog message at line 40:
|
|
;UPDATE syslog_log SET log_mark = 1 WHERE log_line = 40
|
|
|
|
|
|
|
|
WITH [RECURSIVE] cte-table-name AS select-stmt
|
|
══════════════════════════════════════════════════════════════════════
|
|
Create a temporary view that exists only for the duration of a SQL
|
|
statement.
|
|
Parameters
|
|
cte-table-name The name for the temporary table.
|
|
select-stmt The SELECT statement used to populate the temporary
|
|
table.
|
|
|