sysdbql(7) ========== :doctype: manpage NAME ---- sysdbql - the SysDB query language (SysQL) SYNOPSIS -------- LIST hosts; LIST services; 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 (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* 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. '' '' '':: 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: *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. *=*:: Checks two values for equality. *!=*:: Checks two values for inequality. *=~*:: 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. *!~*:: 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 '' [*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 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; [{ "name": "host1.example.com", "last_update": "2001-02-03 04:05:06 +0700", "update_interval": "5m4s", "backend": ['backend::mk-livestatus'] },{ "name": "host2.example.com", "last_update": "2001-02-03 04:05:06 +0700", "update_interval": "10s", "backend": ['backend::mk-livestatus','backend::collectd::unixsock'] }] 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", "backend": ['backend::mk-livestatus'] },{ ... }], "services": [{ "name": "some service", "last_update": "2001-02-03 04:05:06 +0700", "update_interval": "5m4s", "backend": ['backend::mk-livestatus'] },{ ... }]} 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", "backend": ['backend::mk-livestatus'] },{ ... }], "services": [{ "name": "some service", "last_update": "2001-02-03 04:05:06 +0700", "update_interval": "5m4s", "backend": ['backend::mk-livestatus'] },{ ... }]},{ ... }] SEE ALSO -------- manpage:sysdb[1], manpage:sysdb[7] RFC 4627 (JSON): http://www.ietf.org/rfc/rfc4627.txt The SysDB homepage: https://sysdb.io/ AUTHOR ------ SysDB was written by Sebastian "tokkee" Harl . COPYRIGHT --------- Copyright (C) 2012-2014 Sebastian "tokkee" Harl This is free software under the terms of the BSD license, see the source for copying conditions. There is NO WARRANTY; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. // vim: set tw=78 sw=4 ts=4 noexpandtab spell spelllang=en_us :