3ef41c12bd0166fd8d2e0d131943bfacff80584a
1 sysdbql(7)
2 ==========
3 :doctype: manpage
5 NAME
6 ----
7 sysdbql - the SysDB query language (SysQL)
9 SYNOPSIS
10 --------
12 LIST hosts;
13 LIST services;
15 FETCH host 'some.host.name';
17 LOOKUP hosts MATCHING attribute['architecture'] = 'amd64'
18 FILTER age < 5 * interval;
20 DESCRIPTION
21 -----------
22 include::sysdb-description.txt[]
24 The SysDB query language (SysQL) is a human-readable format for describing a
25 request to retrieve data from a SysDB daemon. It is very remotely similar to
26 the Standard Query Language (SQL) supported by relational database management
27 systems (RDBMS) but specialized for SysDB's use-case.
29 QUERY COMMANDS
30 --------------
31 Each command is terminated by a semicolon. The following commands are
32 available to retrieve information from SysDB:
34 *LIST* hosts|services|metrics [*FILTER* '<filter_condition>']::
35 Retrieve a sorted (by name) list of all objects of the specified type
36 currently stored in SysDB. The return value is a list of objects including
37 their names, the timestamp of the last update and an approximation of the
38 interval with which the host was updated. When listing services or metrics,
39 the respective objects will be grouped by host. If a filter condition is
40 specified, only objects matching that filter will be included in the reply.
41 See the section "FILTER clause" for more details about how to specify the
42 search and filter conditions.
44 *FETCH* host '<hostname>' [*FILTER* '<filter_condition>']::
45 *FETCH* service|metric '<hostname>'.'<name>' [*FILTER* '<filter_condition>']::
46 Retrieve detailed information about the specified object. The return value
47 includes the full object including all of its attributes and child objects.
48 If the named object does not exist, an error is returned. If a filter
49 condition is specified, only objects matching that filter will be included in
50 the reply. See the section "FILTER clause" for more details about how to
51 specify the search and filter conditions.
53 *LOOKUP* hosts|services|metrics [*MATCHING* '<search_condition>'] [*FILTER* '<filter_condition>']::
54 Retrieve detailed information about all objects matching the specified search
55 condition. The return value is a list of detailed information for each
56 matching object providing the same details as returned by the *FETCH* command.
57 If no object matches the search criteria, it's not considered an error.
58 Instead, an empty list is returned. If a filter condition is specified, only
59 objects matching that filter will be included in the reply. See the sections
60 "MATCHING clause" and "FILTER clause" for more details about how to specify
61 the search and filter conditions.
63 *TIMESERIES* '<hostname>'.'<metric>' [START '<datetime>'] [END '<datetime>']::
64 Retrieve a time-series for the specified host's metric. The data is retrieved
65 from a backend data-store based on information provided by the respective
66 query plugin. The return value includes the actual start and end time of the
67 time-series and one or multiple sequences of time-stamp / value pairs. If the
68 metric does not exist or if the backend data-store is not supported, an error
69 is returned.
71 MATCHING clause
72 ~~~~~~~~~~~~~~~
73 The *MATCHING* clause in a query specifies a boolean expression which is used
74 to match objects based on their fields, attributes, or child objects. Any
75 object for which the expression evaluates to true will be included in the
76 result set according to the rules set by the actual query.
78 FILTER clause
79 ~~~~~~~~~~~~~
80 The *FILTER* clause in a query specifies a boolean expression which is used to
81 filter objects included in the query's response. The filter is applied to
82 hosts, services, and attributes alike and, thus, will usually be based on the
83 core properties of the stored objects. The basic syntax for filter clauses is
84 the same as for matching clauses.
86 Expressions
87 ~~~~~~~~~~~
88 Expressions form the basic building block for all queries. Boolean expressions
89 select objects based on the values of arithmetic expressions which operate on
90 object specific values (fields) or constant values.
92 Boolean expressions may use the following operators:
94 '<expression>' *AND* '<expression>'::
95 '<expression>' *OR* '<expression>'::
96 Evaluates to the logical AND or logical OR of the specified
97 subexpressions.
99 *NOT* '<expression>'::
100 Invert the boolean result of the specified subexpression.
102 '<expression>' '<cmp>' '<expression>'::
103 Compare the values of two expressions using any compare operator (see
104 below). Evaluates to either true or false. Comparing any value with a NULL
105 value always evaluates to false. The data types of the two values have to
106 match except for a few cases as noted in the documentation of the
107 respective operator.
109 *ANY* '<object_type>' '<cmp>' '<expression>'::
110 Compares the objects of an iterable child using any compare operator.
111 Evaluates to true if any of those child objects matches or false if no
112 such children exist. Otherwise, the same rules as for other comparison
113 operations apply.
115 *ALL* '<object_type>' '<cmp>' '<expression>'::
116 *ALL* is similar to the *ANY* operator but matches if all child objects
117 match or if no children exist.
119 '<expression>' *IS NULL*::
120 '<expression>' *IS NOT NULL*::
121 Check whether an expression evaluates to a NULL value (or not). An
122 expression evaluates to NULL if the queried object does not exist (e.g.,
123 when accessing an attribute value).
125 '<expression>' *IN* '<expression>'::
126 Checks whether the value of the first expression is included in the value
127 of the second expression which has to be an array value (e.g., *backend*
128 field). If the second value is not an array or if the type of the first
129 value does not match the array's element type, the expression always
130 evaluates to false.
132 Parentheses ('()') may be used around subexpressions to group them and enforce
133 precedence.
135 The following fields may be queried:
137 *name*::
138 The canonicalized name of the object. The type of this field is string.
140 *last_update*::
141 The timestamp of the last update of the object. This value is based on
142 information provided by the queried backend if possible. The type of this
143 field is date-time.
145 *age*::
146 The amount of time since the last update of the object. The type of this
147 field is date-time.
149 *interval*::
150 The interval with which the object gets updated. This value is determined
151 automatically based on a moving average determined from the update
152 timestamps of an object. It depends on the update timestamps as provided
153 by the backend (if available) and SysDB's query interval. The type of this
154 field is date-time.
156 *backend*::
157 The name of the backend (plugin) providing the data. The type of this
158 field is array of strings.
160 *attribute[*'<name>'*]*::
161 The value of the object's named attribute. If an attribute of the
162 specified name does not exist, each comparison is treated as if the value
163 does not match. See the documentation for the *IS NULL* and *IS NOT NULL*
164 operators for ways to check if an attribute exists. The value of an
165 attribute may be of any of the supported data types. There is no schema
166 specifying each attribute's data type which means that the parser cannot
167 do any type checks. Thus, in case the data types mismatch when comparing
168 an attribute value with some other value, the two values will be cast to
169 strings before comparing them.
171 The following logical operators are supported by SysDB. Unless otherwise
172 noted, the data types of the left hand and right hand side have to match.
174 *=*::
175 Checks two values for equality.
177 *!=*::
178 Checks two values for inequality.
180 *=~*::
181 Checks if a value matches a regular expression. The regex pattern has to
182 be specified as a string but the value may be of any type and will be cast
183 to a string before evaluating the regular expression. SysDB uses POSIX
184 extended regular expressions.
186 *!~*::
187 Checks if a value does not match a regular expression. The same rules
188 apply as for the *=~* operator.
190 *<*, *\<=*, *>=*, *>*::
191 Checks whether a value compares less than, less than or equal to, greater
192 than or equal, or greater than some other value.
194 The following arithmetic operators are supported by SysDB. Unless otherwise
195 noted, the data types of the left hand and right hand side have to match.
197 *+*::
198 *-*::
199 Add or subtract two numeric or date-time values.
201 ***::
202 */*::
203 Multiple or divide two numeric or data-time values. A date-time value may
204 be multiplied with a numeric value or divided by a numeric value. The
205 result will then be a date-time value.
207 *%*::
208 Modulo operator (remainder of division). Same rules for data types apply
209 as for division.
211 *||*::
212 Concatenate string or array values.
214 DATA TYPES
215 ----------
216 The SysDB query language natively supports various data-types. Constants of
217 all types may be used in any place where a value is expected.
219 *String constants*::
220 A string constant is an arbitrary sequence of characters enclosed in
221 single quotes ('''). Single quotes may be included in a string constant by
222 specifying two adjacent single quotes.
224 *Integer constants*::
225 An integer constant may be specified either as a sequence of digits or in
226 scientific notation written in the form "'a' E 'b'" (without spaces) where
227 'a' and 'b' are integers. A leading plus or minus sign specifies the sign
228 of the constant.
230 *Floating-point constants*::
231 A floating-point constant is a sequence of digits containing a decimal
232 point. Digits before or after the decimal point (but not both) are
233 optional. Floating-point constants may also be specified in scientific
234 notation by appending the letter "E" and a positive or negative integer
235 exponent. A leading plus or minus sign specifies the sign of the constant.
237 *Date and time constants*::
238 A date constant may be specified as 'YYYY-MM-DD' and time constants may be
239 specified as 'HH:MM:SS.nnnnnnnnn' where seconds and nanoseconds are
240 optional.
242 *Interval constants*::
243 An interval may be specified by one or multiple quantity and unit pairs.
244 The quantity may be any integer constant and the unit may be any of the
245 following: *Y* (years), *M* (months), *D* (days), *h* (hours), *m*
246 (minutes), *s* (seconds), *ms* (milliseconds), *us* (microseconds), or
247 *ns* (nanoseconds). Note that years and months are approximations.
249 RESPONSE FORMAT
250 ---------------
251 The JavaScript Object Notation (JSON) format, as specified in RFC 4627, is
252 used in all replies from the server. http://www.ietf.org/rfc/rfc4627.txt
254 EXAMPLES
255 --------
256 The following examples illustrate the use of the commands and what their
257 replies look like. The replies are pretty-printed to more easily follow them.
259 LIST hosts;
260 [{
261 "name": "host1.example.com",
262 "last_update": "2001-02-03 04:05:06 +0700",
263 "update_interval": "5m4s"
264 },{
265 "name": "host2.example.com",
266 "last_update": "2001-02-03 04:05:06 +0700",
267 "update_interval": "5m4s"
268 }]
270 FETCH host 'host1.example.com';
271 {
272 "name": "host1.example.com",
273 "last_update": "2001-02-03 04:05:06 +0700",
274 "update_interval": "5m4s",
275 "attributes": [{
276 "name": "architecture",
277 "value": "amd64",
278 "last_update": "2001-02-03 04:05:06 +0700",
279 "update_interval": "5m4s"
280 },{
281 ...
282 }],
283 "services": [{
284 "name": "some service",
285 "last_update": "2001-02-03 04:05:06 +0700",
286 "update_interval": "5m4s"
287 },{
288 ...
289 }]}
291 LOOKUP hosts MATCHING attribute['architecture'] = 'amd64';
292 [{
293 "name": "host1.example.com",
294 "last_update": "2001-02-03 04:05:06 +0700",
295 "update_interval": "5m4s",
296 "attributes": [{
297 "name": "architecture",
298 "value": "amd64",
299 "last_update": "2001-02-03 04:05:06 +0700",
300 "update_interval": "5m4s"
301 },{
302 ...
303 }],
304 "services": [{
305 "name": "some service",
306 "last_update": "2001-02-03 04:05:06 +0700",
307 "update_interval": "5m4s"
308 },{
309 ...
310 }]},{
311 ...
312 }]
314 SEE ALSO
315 --------
316 manpage:sysdb[1], manpage:sysdb[7]
318 RFC 4627 (JSON): http://www.ietf.org/rfc/rfc4627.txt
320 The SysDB homepage: https://sysdb.io/
322 AUTHOR
323 ------
324 SysDB was written by Sebastian "tokkee" Harl <sh@tokkee.org>.
326 COPYRIGHT
327 ---------
328 Copyright (C) 2012-2014 Sebastian "tokkee" Harl <sh@tokkee.org>
330 This is free software under the terms of the BSD license, see the source for
331 copying conditions. There is NO WARRANTY; not even for MERCHANTABILITY or
332 FITNESS FOR A PARTICULAR PURPOSE.
334 // vim: set tw=78 sw=4 ts=4 noexpandtab spell spelllang=en_us :