<commit_before>sysdbql(7)
==========
:doctype: manpage

NAME
----
sysdbql - the SysDB query language

SYNOPSIS
--------

  LIST hosts;

  FETCH host 'some.host.name';

  LOOKUP hosts MATCHING attribute.architecture = 'amd64'
               FILTER :age < 5 * :interval;

DESCRIPTION
-----------
include::sysdb-description.txt[]

The SysDB query language is a human-readable format for describing a request
to retrieve data from a SysDB daemon. It is very remotely similar to the
Standard Query Language (SQL) supported by relational database management
systems (RDBMS) but specialized for SysDB's use-case.

QUERY COMMANDS
--------------
Each command is terminated by a semicolon. The following commands are
available to retrieve information from SysDB:

*LIST* hosts [*FILTER* '<filter_condition>']::
Retrieve a sorted (by name) list of all hosts currently stored in SysDB. The
return value is a list of hosts where each host specification includes its
name, the timestamp of the last update of the object known to SysDB and an
approximation of the interval with which the host was updated. If a filter
condition is specified, only objects matching that filter will be included in
the reply. See the section "FILTER clause" for more details about how to
specify the search and filter conditions.

*FETCH* host '<hostname>' [*FILTER* '<filter_condition>']::
Retrieve detailed information about the specified host object. The return
value includes the hostname, a list of services referenced by the host, and a
list of attributes for the host and each service. If the host does not exist,
an error is returned. If a filter condition is specified, only objects
matching that filter will be included in the reply. See the section "FILTER
clause" for more details about how to specify the search and filter
conditions.

*LOOKUP* hosts *MATCHING* '<search_condition>' [*FILTER* '<filter_condition>']::
Retrieve detailed information about all host objects matching the specified
search condition. The return value is a list of detailed information for each
matching host providing the same details as returned by the *FETCH* command.
If no host matches the search criteria, it's not considered an error. Instead,
an empty list is returned. If a filter condition is specified, only objects
matching that filter will be included in the reply. See the sections "MATCHING
clause" and "FILTER clause" for more details about how to specify the search
and filter conditions.

*TIMESERIES* '<hostname>'.'<metric>' [START '<datetime>'] [END '<datetime>']::
Retrieve a time-series for the specified host's metric. The data is retrieved
from a backend data-store based on information provided by the respective
query plugin. The return value includes the actual start and end time of the
time-series and one or multiple sequences of time-stamp / value pairs. If the
metric does not exist or if the backend data-store is not supported, an error
is returned.

MATCHING clause
~~~~~~~~~~~~~~~
The *MATCHING* clause in a query specifies a boolean expression which is used
to match host objects based on their names, their attributes, or services
referenced by the host. Each *MATCHING* clause may be made up of one or
multiple subexpressions each matching on one criteria. The following
subexpressions are supported by SysDB:

'<field>' '<operator>' '<value>'::
	Match a named field against the specified value. See below for what fields
	and operators are supported.

*NOT* '<subexpression>'::
	Invert the boolean result of the specified subexpression.

'<subexpression>' *AND* '<subexpression>'::
'<subexpression>' *OR* '<subexpression>'::
	Combine multiple subexpressions using logical AND or logical OR.

The following fields may be queried:

*host*::
	The full name of the host.

*service*::
	The full service name as referenced by the host.

*attribute*::
	The full name of a host attribute.

*attribute.*'<name>'::
	The value of the named host attribute. If an attribute of the specified
	does not exist, each comparison is treated as if the value does not match.
	See the documentation for the *IS NULL* and *IS NOT NULL* operators for
	ways to check if an attribute exists.

'<value>' may either be a string (when matching by object names) or match the
type of the attribute's value (when matching attribute values). Attribute
values may either be a string, integer, decimal number, or a date-time value
(booleans and binary data are not supported by the frontend yet).

When comparing an attribute's value using a regular expression matcher, the
value will be cast to a string before doing so. No other casts are supported
at this time.

The following operators may be used to match field values:

*=*::
	Evaluates to true if the field value exactly matches the specified value.

*!=*::
	Evaluates to true if the field value does not match the exact specified
	value.

*=~*::
	Evaluates to true if the field value matches the specified regular
	expression. SysDB uses POSIX extended regular expressions.

*!~*::
	Evalues to true if the field value does not match the specified regular
	expression.

The following operators may be used, in addition, to match attribute values:

*<*, *\<=*, *>=*, *>*::
	Evaluates to true if the attribute value is less than, less than or equal
	to, greater than or equal to or greater than the specified value.

In addition, a named attribute may be checked for existence using the *IS
NULL* and *IS NOT NULL* operators. An attribute is considered to be *NULL* if
it is not set for a host.

FILTER clause
~~~~~~~~~~~~~
The *FILTER* clause in a query specifies a boolean expression which is used to
filter objects included in the query's response. The filter is applied to
hosts, services, and attributes alike and, thus, will usually be based on the
core properties of the stored objects. The basic syntax for filter clauses is
the same as for matching clauses (see the description about subexpressions
above).

The following fields (core properties) of stored objects may be queried:

*:last_update*::
	The timestamp of the last update of the object. This value is based on
	information provided by the queried backend if possible.

*:age*::
	The amount of time since the last update of the object.

*:interval*::
	The interval with which the object gets updated. This value is determined
	automatically based on a moving average determined from the update
	timestamps of an object. It depends on the update timestamps as provided
	by the backend (if available) and SysDB's query interval.

*:backend*::
	The name of the backend (plugin) providing the data.

The type of the *last_update*, *age*, and *interval* fields is date-time and
the type of the *backend* field is string. All conditional operators may be
used to compare the date-time fields (but not regular expression or *IS NULL*
operators). The backend field may only be matched by (in)equality. Each object
may be provided by multiple backends. The filter matches if the specified
value matches any of them.

RESPONSE FORMAT
---------------
The JavaScript Object Notation (JSON) format, as specified in RFC 4627, is
used in all replies from the server. http://www.ietf.org/rfc/rfc4627.txt

EXAMPLES
--------
The following examples illustrate the use of the commands and what their
replies look like. The replies are pretty-printed to more easily follow them.

  LIST hosts;
  {"hosts":[{
      "name": "host1.example.com",
      "last_update": "2001-02-03 04:05:06 +0700",
      "update_interval": "5m4s"
    },{
      "name": "host2.example.com",
      "last_update": "2001-02-03 04:05:06 +0700",
      "update_interval": "5m4s"
    }]}

  FETCH host 'host1.example.com';
  {
      "name": "host1.example.com",
      "last_update": "2001-02-03 04:05:06 +0700",
      "update_interval": "5m4s",
      "attributes": [{
          "name": "architecture",
          "value": "amd64",<commit_msg>sysdbql(7): Documented supported data types.<commit_after>sysdbql(7)
==========
:doctype: manpage

NAME
----
sysdbql - the SysDB query language

SYNOPSIS
--------

  LIST hosts;

  FETCH host 'some.host.name';

  LOOKUP hosts MATCHING attribute.architecture = 'amd64'
               FILTER :age < 5 * :interval;

DESCRIPTION
-----------
include::sysdb-description.txt[]

The SysDB query language is a human-readable format for describing a request
to retrieve data from a SysDB daemon. It is very remotely similar to the
Standard Query Language (SQL) supported by relational database management
systems (RDBMS) but specialized for SysDB's use-case.

QUERY COMMANDS
--------------
Each command is terminated by a semicolon. The following commands are
available to retrieve information from SysDB:

*LIST* hosts [*FILTER* '<filter_condition>']::
Retrieve a sorted (by name) list of all hosts currently stored in SysDB. The
return value is a list of hosts where each host specification includes its
name, the timestamp of the last update of the object known to SysDB and an
approximation of the interval with which the host was updated. If a filter
condition is specified, only objects matching that filter will be included in
the reply. See the section "FILTER clause" for more details about how to
specify the search and filter conditions.

*FETCH* host '<hostname>' [*FILTER* '<filter_condition>']::
Retrieve detailed information about the specified host object. The return
value includes the hostname, a list of services referenced by the host, and a
list of attributes for the host and each service. If the host does not exist,
an error is returned. If a filter condition is specified, only objects
matching that filter will be included in the reply. See the section "FILTER
clause" for more details about how to specify the search and filter
conditions.

*LOOKUP* hosts *MATCHING* '<search_condition>' [*FILTER* '<filter_condition>']::
Retrieve detailed information about all host objects matching the specified
search condition. The return value is a list of detailed information for each
matching host providing the same details as returned by the *FETCH* command.
If no host matches the search criteria, it's not considered an error. Instead,
an empty list is returned. If a filter condition is specified, only objects
matching that filter will be included in the reply. See the sections "MATCHING
clause" and "FILTER clause" for more details about how to specify the search
and filter conditions.

*TIMESERIES* '<hostname>'.'<metric>' [START '<datetime>'] [END '<datetime>']::
Retrieve a time-series for the specified host's metric. The data is retrieved
from a backend data-store based on information provided by the respective
query plugin. The return value includes the actual start and end time of the
time-series and one or multiple sequences of time-stamp / value pairs. If the
metric does not exist or if the backend data-store is not supported, an error
is returned.

MATCHING clause
~~~~~~~~~~~~~~~
The *MATCHING* clause in a query specifies a boolean expression which is used
to match host objects based on their names, their attributes, or services
referenced by the host. Each *MATCHING* clause may be made up of one or
multiple subexpressions each matching on one criteria. The following
subexpressions are supported by SysDB:

'<field>' '<operator>' '<value>'::
	Match a named field against the specified value. See below for what fields
	and operators are supported.

*NOT* '<subexpression>'::
	Invert the boolean result of the specified subexpression.

'<subexpression>' *AND* '<subexpression>'::
'<subexpression>' *OR* '<subexpression>'::
	Combine multiple subexpressions using logical AND or logical OR.

The following fields may be queried:

*host*::
	The full name of the host.

*service*::
	The full service name as referenced by the host.

*attribute*::
	The full name of a host attribute.

*attribute.*'<name>'::
	The value of the named host attribute. If an attribute of the specified
	does not exist, each comparison is treated as if the value does not match.
	See the documentation for the *IS NULL* and *IS NOT NULL* operators for
	ways to check if an attribute exists.

'<value>' may either be a string (when matching by object names) or match the
type of the attribute's value (when matching attribute values). Attribute
values may either be a string, integer, decimal number, or a date-time value
(booleans and binary data are not supported by the frontend yet). See the
section "DATA TYPES" for more details about how specify each data-type.

When comparing an attribute's value using a regular expression matcher, the
value will be cast to a string before doing so. No other casts are supported
at this time.

The following operators may be used to match field values:

*=*::
	Evaluates to true if the field value exactly matches the specified value.

*!=*::
	Evaluates to true if the field value does not match the exact specified
	value.

*=~*::
	Evaluates to true if the field value matches the specified regular
	expression. SysDB uses POSIX extended regular expressions.

*!~*::
	Evalues to true if the field value does not match the specified regular
	expression.

The following operators may be used, in addition, to match attribute values:

*<*, *\<=*, *>=*, *>*::
	Evaluates to true if the attribute value is less than, less than or equal
	to, greater than or equal to or greater than the specified value.

In addition, a named attribute may be checked for existence using the *IS
NULL* and *IS NOT NULL* operators. An attribute is considered to be *NULL* if
it is not set for a host.

FILTER clause
~~~~~~~~~~~~~
The *FILTER* clause in a query specifies a boolean expression which is used to
filter objects included in the query's response. The filter is applied to
hosts, services, and attributes alike and, thus, will usually be based on the
core properties of the stored objects. The basic syntax for filter clauses is
the same as for matching clauses (see the description about subexpressions
above).

The following fields (core properties) of stored objects may be queried:

*:last_update*::
	The timestamp of the last update of the object. This value is based on
	information provided by the queried backend if possible.

*:age*::
	The amount of time since the last update of the object.

*:interval*::
	The interval with which the object gets updated. This value is determined
	automatically based on a moving average determined from the update
	timestamps of an object. It depends on the update timestamps as provided
	by the backend (if available) and SysDB's query interval.

*:backend*::
	The name of the backend (plugin) providing the data.

The type of the *last_update*, *age*, and *interval* fields is date-time and
the type of the *backend* field is string. All conditional operators may be
used to compare the date-time fields (but not regular expression or *IS NULL*
operators). The backend field may only be matched by (in)equality. Each object
may be provided by multiple backends. The filter matches if the specified
value matches any of them.

DATA TYPES
----------
The SysDB query language natively supports various data-types. Constants of
all types may be used in any place where a value is expected.

*String constants*::
	A string constant is an arbitrary sequence of characters enclosed in
	single quotes ('''). Single quotes may be included in a string constant by
	specifying two adjacent single quotes.

*Integer constants*::
	An integer constant may be specified either as a sequence of digits or in
	scientific notation written in the form "'a' E 'b'" (without spaces) where
	'a' and 'b' are integers. A leading plus or minus sign specifies the sign
	of the constant.

*Floating-point constants*::
	A floating-point constant is a sequence of digits containing a decimal
	point. Digits before or after the decimal point (but not both) are
	optional. Floating-point constants may also be specified in scientific
	notation by appending the letter "E" and a positive or negative integer
	exponent. A leading plus or minus sign specifies the sign of the constant.

*Date and time constants*::
	A date constant may be specified as 'YYYY-MM-DD' and time constants may be
	specified as 'HH:MM:SS.nnnnnnnnn' where seconds and nanoseconds are
	optional.

*Interval constants*::
	An interval may be specified by one or multiple quantity and unit pairs.
	The quantity may be any integer constant and the unit may be any of the
	following: *Y* (years), *M* (months), *D* (days), *h* (hours), *m*
	(minutes), *s* (seconds), *ms* (milliseconds), *us* (microseconds), or
	*ns* (nanoseconds). Note that years and months are approximations.

RESPONSE FORMAT
---------------
The JavaScript Object Notation (JSON) format, as specified in RFC 4627, is
used in all replies from the server. http://www.ietf.org/rfc/rfc4627.txt

EXAMPLES
--------
The following examples illustrate the use of the commands and what their
replies look like. The replies are pretty-printed to more easily follow them.

  LIST hosts;
  {"hosts":[{
      "name": "host1.example.com",
      "last_update": "2001-02-03 04:05:06 +0700",
      "update_interval": "5m4s"
    },{
      "name": "host2.example.com",
      "last_update": "2001-02-03 04:05:06 +0700",
      "update_interval": "5m4s"
    }]}

  FETCH host 'host1.example.com';
  {
      "name": "host1.example.com",
      "last_update": "2001-02-03 04:05:06 +0700",
      "update_interval": "5m4s",
      "attributes": [{
          "name": "architecture",
          "value": "amd64",
{'fraction_non_alphanumeric': 0.0797134670487106, 'fraction_numerical': 0.008997134670487105, 'mean_word_length': 4.206145584725537, 'pattern_counts': {'":': 26, '<': 47, '<?xml version=': 0, '>': 45, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2593549', 'n_tokens_mistral': 4763, 'n_tokens_neox': 4507, 'n_words': 2532}