![[tokkee]](http://tokkee.org/images/avatar.png){kind=avatar}
diff --git a/doc/sysdbql.7.txt b/doc/sysdbql.7.txt
index 335918ab59d3473f93f685379b600fe0d51843e6..587cd6cc72bee0fe71d2f046480bea47aa9f59de 100644 (file)
--- a/doc/sysdbql.7.txt
+++ b/doc/sysdbql.7.txt
sysdbql(7)
==========
-Sebastian "tokkee" Harl <sh@tokkee.org>
-version {package_version}, {build_date}
:doctype: manpage
NAME
----
-sysdbql - the SysDB query language
+sysdbql - the SysDB query language (SysQL)
SYNOPSIS
--------
- LIST;
+ LIST hosts;
+ LIST services;
- QUERY hosts WHERE attribute.architecture = 'amd64';
+ FETCH host 'some.host.name';
+
+ LOOKUP hosts MATCHING attribute['architecture'] = 'amd64'
+ AND 'backend::collectd::unixsock' in backend
+ FILTER age < 5 * interval;
+
+ STORE host attribute 'some.host.name'.'key' 123.45
+ LAST UPDATE 2001-02-03 04:05:06;
DESCRIPTION
-----------
-SysDB stores system and inventory information about hardware and software
-systems. This information is stored in a graph-like hierarchy of generic
-objects. The central object type is a host, which generally represents a
-physical or virtual machine or any other type of physical resource. Hosts, in
-turn, may reference a list of services which represent any kind of logical
-resource like a software system. Both, hosts and services, may reference a
-list of attributes which represent further information about the respective
-host or service object. For example, attributes may specify static information
-like a host's architecture or the software version or snapshots of performance
-data like the current memory utilization or much more.
-
-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
+include::sysdb-description.txt[]
+
+The SysDB query language (SysQL) 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.
+Besides querying data, SysQL may also be used to store or update objects in
+SysDB.
+
QUERY COMMANDS
--------------
Each command is terminated by a semicolon. The following commands are
available to retrieve information from SysDB:
-*LIST*::
-Retrieve a sorted (by name) list of all hosts currently stored in SysDB. The
-return value is a list of hosts where each host description includes its name,
-the timestamp of the last update of the object in SysDB and an approximation
-of the interval with which the host was updated.
-
-*FETCH* '<hostname>'::
-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.
-
-*LOOKUP* hosts *WHERE* '<search_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.
-See the section "WHERE clause" for more details about how to specify the
-search condition.
-
-WHERE clause
-~~~~~~~~~~~~
-The *WHERE* 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 *WHERE* 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>'::
+*LIST* hosts|services|metrics [*FILTER* '<filter_condition>']::
+Retrieve a sorted (by name) list of all objects of the specified type
+currently stored in SysDB. The return value is a list of objects including
+their names, the timestamp of the last update and an approximation of the
+interval with which the host was updated. When listing services or metrics,
+the respective objects will be grouped by host. 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>']::
+*FETCH* service|metric '<hostname>'.'<name>' [*FILTER* '<filter_condition>']::
+Retrieve detailed information about the specified object. The return value
+includes the full object including all of its attributes and child objects.
+If the named object 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|services|metrics [*MATCHING* '<search_condition>'] [*FILTER* '<filter_condition>']::
+Retrieve detailed information about all objects matching the specified search
+condition. The return value is a list of detailed information for each
+matching object providing the same details as returned by the *FETCH* command.
+If no object 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 objects based on their fields, attributes, or child objects. Any
+object for which the expression evaluates to true will be included in the
+result set according to the rules set by the actual query.
+
+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.
+
+Expressions
+~~~~~~~~~~~
+Expressions form the basic building block for all queries. Boolean expressions
+select objects based on the values of arithmetic expressions which operate on
+object specific values (fields) or constant values.
+
+Boolean expressions may use the following operators:
+
+'<expression>' *AND* '<expression>'::
+'<expression>' *OR* '<expression>'::
+ Evaluates to the logical AND or logical OR of the specified
+ subexpressions.
+
+*NOT* '<expression>'::
Invert the boolean result of the specified subexpression.
-'<subexpression>' *AND* '<subexpression>'::
-'<subexpression>' *OR* '<subexpression>'::
- Combine multiple subexpressions using logical AND or logical OR.
+'<expression>' '<cmp>' '<expression>'::
+ Compare the values of two expressions using any compare operator (see
+ below). Evaluates to either true or false. Comparing any value with a NULL
+ value always evaluates to false. The data types of the two values have to
+ match except for a few cases as noted in the documentation of the
+ respective operator.
+
+*ANY* '<iterable>' '<cmp>' '<expression>'::
+ Compares each element of an iterable using any compare operator. Evaluates
+ to true if any of the elements matches or false if no such elements exist.
+ Otherwise, the same rules as for other comparison operations apply.
+ Attributes, a host's services and metrics, and arrays are iterables.
+
+*ALL* '<iterable>' '<cmp>' '<expression>'::
+ *ALL* is similar to the *ANY* operator but matches if all elements match
+ or if no elements exist.
+
+'<expression>' *IS NULL*::
+'<expression>' *IS NOT NULL*::
+ Check whether an expression evaluates to a NULL value (or not). An
+ expression evaluates to NULL if the queried object does not exist (e.g.,
+ when accessing an attribute value).
+
+'<expression>' *IS TRUE*::
+'<expression>' *IS NOT TRUE*::
+'<expression>' *IS FALSE*::
+'<expression>' *IS NOT FALSE*::
+ Check whether an expression evaluates to a boolean *true* or *false* value
+ (or not).
+
+'<expression>' *IN* '<expression>'::
+'<expression>' *NOT IN* '<expression>'::
+ Checks whether the value of the first expression is included in the value
+ of the second expression (or not). The second value has to be an array
+ value (e.g., *backend* field) and the type of the first value has to match
+ the array's element type. The first value may also be an array. In this
+ case, the expression evaluates to true if all elements of that array are
+ included in the second array where order does not matter.
+
+Parentheses ('()') may be used around subexpressions to group them and enforce
+precedence.
The following fields may be queried:
-*host.name*::
- The full name of the host.
-
-*service.name*::
- The full service name as referenced by the host.
-
-*attribute.name*::
- 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.
-
-The following operators may be used to match field values:
+*name*::
+ The canonicalized name of the object. The type of this field is string.
+
+*last_update*::
+ The timestamp of the last update of the object. This value is based on
+ information provided by the queried backend if possible. The type of this
+ field is date-time.
+
+*age*::
+ The amount of time since the last update of the object. The type of this
+ field is date-time.
+
+*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. The type of this
+ field is date-time.
+
+*backend*::
+ The name of the backend (plugin) providing the data. The type of this
+ field is array of strings.
+
+*attribute[*'<name>'*]*::
+ The value of the object's named attribute. If an attribute of the
+ specified name 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. The value of an
+ attribute may be of any of the supported data types. There is no schema
+ specifying each attribute's data type which means that the parser cannot
+ do any type checks. Thus, in case the data types mismatch when comparing
+ an attribute value with some other value, the two values will be cast to
+ strings before comparing them.
+
+*value*::
+ *(Attributes only)* The value of an attribute. Attributes may be accessed
+ by iterating the values of the parent object and this field provides
+ access to its value in that case. See *attribute[*'<name>'*]* above for
+ details about how to handle attribute values.
+
+*timeseries*::
+ *(Metrics only)* A boolean value indicating whether a backend data-store
+ for fetching time-series information is known to SysDB. See the section
+ "Metrics and Time-Series" in manpage:sysdb[7] for details.
+
+Field expressions may be applied to parent or child nodes. For example, a
+host's services are child objects and the host is the parent of the service
+objects. This is done using typed expressions:
+
+host|service|metric.'<field>'::
+ Evaluate the field in the context of the respective parent or child.
+ Currently, this is limited to services or metrics referencing their parent
+ host.
+
+The following logical operators are supported by SysDB. Unless otherwise
+noted, the data types of the left hand and right hand side have to match.
*=*::
- Evaluates to true if the field value exactly matches the specified value.
+ Checks two values for equality.
*!=*::
- Evaluates to true if the field value does not match the exact specified
- value.
+ Checks two values for inequality.
*=~*::
- Evaluates to true if the field value matches the specified regular
- expression. SysDB uses POSIX extended regular expressions.
+ Checks if a value matches a regular expression. The regex pattern has to
+ be specified as a string but the value may be of any type and will be cast
+ to a string before evaluating the regular expression. SysDB uses POSIX
+ extended regular expressions.
*!~*::
- Evalues to true if the field value does not match the specified regular
- expression.
+ Checks if a value does not match a regular expression. The same rules
+ apply as for the *=~* operator.
+
+*<*, *\<=*, *>=*, *>*::
+ Checks whether a value compares less than, less than or equal to, greater
+ than or equal, or greater than some other value.
+
+The following arithmetic operators are supported by SysDB. Unless otherwise
+noted, the data types of the left hand and right hand side have to match.
+
+*+*::
+*-*::
+ Add or subtract two numeric or date-time values.
+
+***::
+*/*::
+ Multiple or divide two numeric or data-time values. A date-time value may
+ be multiplied with a numeric value or divided by a numeric value. The
+ result will then be a date-time value.
+
+*%*::
+ Modulo operator (remainder of division). Same rules for data types apply
+ as for division.
+
+*||*::
+ Concatenate string or array values.
+
+STORING DATA
+------------
+The *STORE* command may be used to store or update an object in SysDB. Each
+command is terminated by a semicolon. The following variants are available for
+storing the different data types:
+
+*STORE* host '<name>' [*LAST UPDATE* '<datetime>']::
+*STORE* service|metric '<hostname>'.'<name>' [*LAST UPDATE* '<datetime>']::
+*STORE* host attribute '<hostname>'.'<key>' '<value>' [*LAST UPDATE* '<datetime>']::
+*STORE* service|metric attribute '<hostname>'.'<name>'.'<key>' '<value>' [*LAST UPDATE* '<datetime>']::
+ Store an object of the specified type and name. For services, metrics, and
+ attributes, the name is prepended with the parent object name separated by
+ a dot ('.'). Optionally, the time-stamp of the object's last update may be
+ provided as well. If omitted, the current time on the server will be used
+ instead.
+
+*STORE* metric '<hostname>'.'<name>' STORE '<type>' '<id>' ['<datetime>'] [*LAST UPDATE* '<datetime>']::
+ Store a metric and provide information about the metric store associated
+ with it. A metric store describes how to access a metric's data and can be
+ used to retrieve time-series information associated with the metric. See
+ the manpage:sysdb[7] manpage for details.
+.
+ Note that the metric store information will be forwarded to the server
+ unmodified. That is, they need to be specified in a way such that the
+ server can make sense out of them. Else, retrieval of time-series data
+ will fail.
+
+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.
+
+*Array constants*::
+ An array stores of one or more values of the same type. It may be
+ specified as a comma-separated list of constant values enclosed in square
+ brackets ('[<elem1>,<elem2>,...]'). For each value, the same rules apply
+ as for a regular constant value of that type.
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
+used in all query replies from the server. http://www.ietf.org/rfc/rfc4627.txt
+
+For all other commands, the reply will be a message string.
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":[{
+ LIST hosts;
+ [{
"name": "host1.example.com",
"last_update": "2001-02-03 04:05:06 +0700",
- "update_interval": "5m4s"
+ "update_interval": "5m4s",
+ "backend": ['backend::mk-livestatus']
},{
"name": "host2.example.com",
"last_update": "2001-02-03 04:05:06 +0700",
- "update_interval": "5m4s"
- }]}
+ "update_interval": "10s",
+ "backend": ['backend::mk-livestatus','backend::collectd::unixsock']
+ }]
- FETCH 'host1.example.com';
+ FETCH host 'host1.example.com';
{
"name": "host1.example.com",
"last_update": "2001-02-03 04:05:06 +0700",
"update_interval": "5m4s",
+ "backend": ['backend::mk-livestatus'],
"attributes": [{
"name": "architecture",
"value": "amd64",
"last_update": "2001-02-03 04:05:06 +0700",
- "update_interval": "5m4s"
+ "update_interval": "5m4s",
+ "backend": ['backend::mk-livestatus']
},{
...
}],
"services": [{
"name": "some service",
"last_update": "2001-02-03 04:05:06 +0700",
- "update_interval": "5m4s"
+ "update_interval": "5m4s",
+ "backend": ['backend::mk-livestatus']
},{
...
}]}
- LOOKUP hosts WHERE attribute.architecture = 'amd64';
+ LOOKUP hosts MATCHING attribute['architecture'] = 'amd64';
[{
"name": "host1.example.com",
"last_update": "2001-02-03 04:05:06 +0700",
"update_interval": "5m4s",
+ "backend": ['backend::mk-livestatus'],
"attributes": [{
"name": "architecture",
"value": "amd64",
"last_update": "2001-02-03 04:05:06 +0700",
- "update_interval": "5m4s"
+ "update_interval": "5m4s",
+ "backend": ['backend::mk-livestatus']
},{
...
}],
"services": [{
"name": "some service",
"last_update": "2001-02-03 04:05:06 +0700",
- "update_interval": "5m4s"
+ "update_interval": "5m4s",
+ "backend": ['backend::mk-livestatus']
},{
...
}]},{
SEE ALSO
--------
-*sysdb*(1)
+manpage:sysdb[1], manpage:sysdb[7]
+
+RFC 4627 (JSON): http://www.ietf.org/rfc/rfc4627.txt
+
+The SysDB homepage: https://sysdb.io/
AUTHOR
------