X-Git-Url: https://git.tokkee.org/?p=sysdb.git;a=blobdiff_plain;f=doc%2Fsysdbql.7.txt;h=9c8b3057cf062eea4ca7fe9720bd9483cdbe5e39;hp=c4f52a9a8c1110ac3880b7c03b849eeb6b75d991;hb=HEAD;hpb=de02a150937998ce92f38c66043514aea5c8dacb diff --git a/doc/sysdbql.7.txt b/doc/sysdbql.7.txt index c4f52a9..9c8b305 100644 --- a/doc/sysdbql.7.txt +++ b/doc/sysdbql.7.txt @@ -4,177 +4,388 @@ sysdbql(7) 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 ----------- 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 +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* '':: -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* '':: -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: - -'' '' '':: - Match a named field against the specified value. See below for what fields - and operators are supported. - -*NOT* '':: +*LIST* hosts|services|metrics [*FILTER* '']:: +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 '' [*FILTER* '']:: +*FETCH* service|metric ''.'' [*FILTER* '']:: +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* ''] [*FILTER* '']:: +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* ''.'' [START ''] [END '']:: +*TIMESERIES* ''.''\['] [END '']:: +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 any +data-source names have been specified, only those data-sources will be +returned. If the metric or a specified data-source 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: + +'' *AND* '':: +'' *OR* '':: + Evaluates to the logical AND or logical OR of the specified + subexpressions. + +*NOT* '':: Invert the boolean result of the specified subexpression. -'' *AND* '':: -'' *OR* '':: - Combine multiple subexpressions using logical AND or logical OR. +'' '' '':: + 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* '' '' '':: + 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* '' '' '':: + *ALL* is similar to the *ANY* operator but matches if all elements match + or if no elements exist. + +'' *IS NULL*:: +'' *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). + +'' *IS TRUE*:: +'' *IS NOT TRUE*:: +'' *IS FALSE*:: +'' *IS NOT FALSE*:: + Check whether an expression evaluates to a boolean *true* or *false* value + (or not). + +'' *IN* '':: +'' *NOT IN* '':: + 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*:: - 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.*'':: - 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. - -'' 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, or decimal number (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: +*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[*''*]*:: + 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[*''*]* 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.'':: + 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. - -The following operators may be used, in addition, to match attribute values: + Checks if a value does not match a regular expression. The same rules + apply as for the *=~* operator. *<*, *\<=*, *>=*, *>*:: - 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 check for existence using the *IS NULL* -and *IS NOT NULL* expressions. An attribute is considered to be *NULL* if it -is not set for a host. + 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 '' [*LAST UPDATE* '']:: +*STORE* service|metric ''.'' [*LAST UPDATE* '']:: +*STORE* host attribute ''.'' '' [*LAST UPDATE* '']:: +*STORE* service|metric attribute ''.''.'' '' [*LAST UPDATE* '']:: + 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 ''.'' STORE '' '' [''] [*LAST UPDATE* '']:: + 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 ('[,,...]'). 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'] },{ ... }]},{ @@ -185,7 +396,9 @@ SEE ALSO -------- manpage:sysdb[1], manpage:sysdb[7] -The SysDB homepage: http://sysdb.io/ +RFC 4627 (JSON): http://www.ietf.org/rfc/rfc4627.txt + +The SysDB homepage: https://sysdb.io/ AUTHOR ------