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 AND 'backend::collectd::unixsock' in backend
19 FILTER age < 5 * interval;
21 STORE host attribute 'some.host.name'.'key' 123.45
22 LAST UPDATE 2001-02-03 04:05:06;
24 DESCRIPTION
25 -----------
26 include::sysdb-description.txt[]
28 The SysDB query language (SysQL) is a human-readable format for describing a
29 request to retrieve data from a SysDB daemon. It is very remotely similar to
30 the Standard Query Language (SQL) supported by relational database management
31 systems (RDBMS) but specialized for SysDB's use-case.
33 Besides querying data, SysQL may also be used to store or update objects in
34 SysDB.
36 QUERY COMMANDS
37 --------------
38 Each command is terminated by a semicolon. The following commands are
39 available to retrieve information from SysDB:
41 *LIST* hosts|services|metrics [*FILTER* '<filter_condition>']::
42 Retrieve a sorted (by name) list of all objects of the specified type
43 currently stored in SysDB. The return value is a list of objects including
44 their names, the timestamp of the last update and an approximation of the
45 interval with which the host was updated. When listing services or metrics,
46 the respective objects will be grouped by host. If a filter condition is
47 specified, only objects matching that filter will be included in the reply.
48 See the section "FILTER clause" for more details about how to specify the
49 search and filter conditions.
51 *FETCH* host '<hostname>' [*FILTER* '<filter_condition>']::
52 *FETCH* service|metric '<hostname>'.'<name>' [*FILTER* '<filter_condition>']::
53 Retrieve detailed information about the specified object. The return value
54 includes the full object including all of its attributes and child objects.
55 If the named object does not exist, an error is returned. If a filter
56 condition is specified, only objects matching that filter will be included in
57 the reply. See the section "FILTER clause" for more details about how to
58 specify the search and filter conditions.
60 *LOOKUP* hosts|services|metrics [*MATCHING* '<search_condition>'] [*FILTER* '<filter_condition>']::
61 Retrieve detailed information about all objects matching the specified search
62 condition. The return value is a list of detailed information for each
63 matching object providing the same details as returned by the *FETCH* command.
64 If no object matches the search criteria, it's not considered an error.
65 Instead, an empty list is returned. If a filter condition is specified, only
66 objects matching that filter will be included in the reply. See the sections
67 "MATCHING clause" and "FILTER clause" for more details about how to specify
68 the search and filter conditions.
70 *TIMESERIES* '<hostname>'.'<metric>' [START '<datetime>'] [END '<datetime>']::
71 *TIMESERIES* '<hostname>'.'<metric>'\[<data-source, ...\] [START '<datetime>'] [END '<datetime>']::
72 Retrieve a time-series for the specified host's metric. The data is retrieved
73 from a backend data-store based on information provided by the respective
74 query plugin. The return value includes the actual start and end time of the
75 time-series and one or multiple sequences of time-stamp / value pairs. If any
76 data-source names have been specified, only those data-sources will be
77 returned. If the metric or a specified data-source does not exist or if the
78 backend data-store is not supported, an error is returned.
80 MATCHING clause
81 ~~~~~~~~~~~~~~~
82 The *MATCHING* clause in a query specifies a boolean expression which is used
83 to match objects based on their fields, attributes, or child objects. Any
84 object for which the expression evaluates to true will be included in the
85 result set according to the rules set by the actual query.
87 FILTER clause
88 ~~~~~~~~~~~~~
89 The *FILTER* clause in a query specifies a boolean expression which is used to
90 filter objects included in the query's response. The filter is applied to
91 hosts, services, and attributes alike and, thus, will usually be based on the
92 core properties of the stored objects. The basic syntax for filter clauses is
93 the same as for matching clauses.
95 Expressions
96 ~~~~~~~~~~~
97 Expressions form the basic building block for all queries. Boolean expressions
98 select objects based on the values of arithmetic expressions which operate on
99 object specific values (fields) or constant values.
101 Boolean expressions may use the following operators:
103 '<expression>' *AND* '<expression>'::
104 '<expression>' *OR* '<expression>'::
105 Evaluates to the logical AND or logical OR of the specified
106 subexpressions.
108 *NOT* '<expression>'::
109 Invert the boolean result of the specified subexpression.
111 '<expression>' '<cmp>' '<expression>'::
112 Compare the values of two expressions using any compare operator (see
113 below). Evaluates to either true or false. Comparing any value with a NULL
114 value always evaluates to false. The data types of the two values have to
115 match except for a few cases as noted in the documentation of the
116 respective operator.
118 *ANY* '<iterable>' '<cmp>' '<expression>'::
119 Compares each element of an iterable using any compare operator. Evaluates
120 to true if any of the elements matches or false if no such elements exist.
121 Otherwise, the same rules as for other comparison operations apply.
122 Attributes, a host's services and metrics, and arrays are iterables.
124 *ALL* '<iterable>' '<cmp>' '<expression>'::
125 *ALL* is similar to the *ANY* operator but matches if all elements match
126 or if no elements exist.
128 '<expression>' *IS NULL*::
129 '<expression>' *IS NOT NULL*::
130 Check whether an expression evaluates to a NULL value (or not). An
131 expression evaluates to NULL if the queried object does not exist (e.g.,
132 when accessing an attribute value).
134 '<expression>' *IS TRUE*::
135 '<expression>' *IS NOT TRUE*::
136 '<expression>' *IS FALSE*::
137 '<expression>' *IS NOT FALSE*::
138 Check whether an expression evaluates to a boolean *true* or *false* value
139 (or not).
141 '<expression>' *IN* '<expression>'::
142 '<expression>' *NOT IN* '<expression>'::
143 Checks whether the value of the first expression is included in the value
144 of the second expression (or not). The second value has to be an array
145 value (e.g., *backend* field) and the type of the first value has to match
146 the array's element type. The first value may also be an array. In this
147 case, the expression evaluates to true if all elements of that array are
148 included in the second array where order does not matter.
150 Parentheses ('()') may be used around subexpressions to group them and enforce
151 precedence.
153 The following fields may be queried:
155 *name*::
156 The canonicalized name of the object. The type of this field is string.
158 *last_update*::
159 The timestamp of the last update of the object. This value is based on
160 information provided by the queried backend if possible. The type of this
161 field is date-time.
163 *age*::
164 The amount of time since the last update of the object. The type of this
165 field is date-time.
167 *interval*::
168 The interval with which the object gets updated. This value is determined
169 automatically based on a moving average determined from the update
170 timestamps of an object. It depends on the update timestamps as provided
171 by the backend (if available) and SysDB's query interval. The type of this
172 field is date-time.
174 *backend*::
175 The name of the backend (plugin) providing the data. The type of this
176 field is array of strings.
178 *attribute[*'<name>'*]*::
179 The value of the object's named attribute. If an attribute of the
180 specified name does not exist, each comparison is treated as if the value
181 does not match. See the documentation for the *IS NULL* and *IS NOT NULL*
182 operators for ways to check if an attribute exists. The value of an
183 attribute may be of any of the supported data types. There is no schema
184 specifying each attribute's data type which means that the parser cannot
185 do any type checks. Thus, in case the data types mismatch when comparing
186 an attribute value with some other value, the two values will be cast to
187 strings before comparing them.
189 *value*::
190 *(Attributes only)* The value of an attribute. Attributes may be accessed
191 by iterating the values of the parent object and this field provides
192 access to its value in that case. See *attribute[*'<name>'*]* above for
193 details about how to handle attribute values.
195 *timeseries*::
196 *(Metrics only)* A boolean value indicating whether a backend data-store
197 for fetching time-series information is known to SysDB. See the section
198 "Metrics and Time-Series" in manpage:sysdb[7] for details.
200 Field expressions may be applied to parent or child nodes. For example, a
201 host's services are child objects and the host is the parent of the service
202 objects. This is done using typed expressions:
204 host|service|metric.'<field>'::
205 Evaluate the field in the context of the respective parent or child.
206 Currently, this is limited to services or metrics referencing their parent
207 host.
209 The following logical operators are supported by SysDB. Unless otherwise
210 noted, the data types of the left hand and right hand side have to match.
212 *=*::
213 Checks two values for equality.
215 *!=*::
216 Checks two values for inequality.
218 *=~*::
219 Checks if a value matches a regular expression. The regex pattern has to
220 be specified as a string but the value may be of any type and will be cast
221 to a string before evaluating the regular expression. SysDB uses POSIX
222 extended regular expressions.
224 *!~*::
225 Checks if a value does not match a regular expression. The same rules
226 apply as for the *=~* operator.
228 *<*, *\<=*, *>=*, *>*::
229 Checks whether a value compares less than, less than or equal to, greater
230 than or equal, or greater than some other value.
232 The following arithmetic operators are supported by SysDB. Unless otherwise
233 noted, the data types of the left hand and right hand side have to match.
235 *+*::
236 *-*::
237 Add or subtract two numeric or date-time values.
239 ***::
240 */*::
241 Multiple or divide two numeric or data-time values. A date-time value may
242 be multiplied with a numeric value or divided by a numeric value. The
243 result will then be a date-time value.
245 *%*::
246 Modulo operator (remainder of division). Same rules for data types apply
247 as for division.
249 *||*::
250 Concatenate string or array values.
252 STORING DATA
253 ------------
254 The *STORE* command may be used to store or update an object in SysDB. Each
255 command is terminated by a semicolon. The following variants are available for
256 storing the different data types:
258 *STORE* host '<name>' [*LAST UPDATE* '<datetime>']::
259 *STORE* service|metric '<hostname>'.'<name>' [*LAST UPDATE* '<datetime>']::
260 *STORE* host attribute '<hostname>'.'<key>' '<value>' [*LAST UPDATE* '<datetime>']::
261 *STORE* service|metric attribute '<hostname>'.'<name>'.'<key>' '<value>' [*LAST UPDATE* '<datetime>']::
262 Store an object of the specified type and name. For services, metrics, and
263 attributes, the name is prepended with the parent object name separated by
264 a dot ('.'). Optionally, the time-stamp of the object's last update may be
265 provided as well. If omitted, the current time on the server will be used
266 instead.
268 *STORE* metric '<hostname>'.'<name>' STORE '<type>' '<id>' ['<datetime>'] [*LAST UPDATE* '<datetime>']::
269 Store a metric and provide information about the metric store associated
270 with it. A metric store describes how to access a metric's data and can be
271 used to retrieve time-series information associated with the metric. See
272 the manpage:sysdb[7] manpage for details.
273 .
274 Note that the metric store information will be forwarded to the server
275 unmodified. That is, they need to be specified in a way such that the
276 server can make sense out of them. Else, retrieval of time-series data
277 will fail.
279 DATA TYPES
280 ----------
281 The SysDB query language natively supports various data-types. Constants of
282 all types may be used in any place where a value is expected.
284 *String constants*::
285 A string constant is an arbitrary sequence of characters enclosed in
286 single quotes ('''). Single quotes may be included in a string constant by
287 specifying two adjacent single quotes.
289 *Integer constants*::
290 An integer constant may be specified either as a sequence of digits or in
291 scientific notation written in the form "'a' E 'b'" (without spaces) where
292 'a' and 'b' are integers. A leading plus or minus sign specifies the sign
293 of the constant.
295 *Floating-point constants*::
296 A floating-point constant is a sequence of digits containing a decimal
297 point. Digits before or after the decimal point (but not both) are
298 optional. Floating-point constants may also be specified in scientific
299 notation by appending the letter "E" and a positive or negative integer
300 exponent. A leading plus or minus sign specifies the sign of the constant.
302 *Date and time constants*::
303 A date constant may be specified as 'YYYY-MM-DD' and time constants may be
304 specified as 'HH:MM:SS.nnnnnnnnn' where seconds and nanoseconds are
305 optional.
307 *Interval constants*::
308 An interval may be specified by one or multiple quantity and unit pairs.
309 The quantity may be any integer constant and the unit may be any of the
310 following: *Y* (years), *M* (months), *D* (days), *h* (hours), *m*
311 (minutes), *s* (seconds), *ms* (milliseconds), *us* (microseconds), or
312 *ns* (nanoseconds). Note that years and months are approximations.
314 *Array constants*::
315 An array stores of one or more values of the same type. It may be
316 specified as a comma-separated list of constant values enclosed in square
317 brackets ('[<elem1>,<elem2>,...]'). For each value, the same rules apply
318 as for a regular constant value of that type.
320 RESPONSE FORMAT
321 ---------------
322 The JavaScript Object Notation (JSON) format, as specified in RFC 4627, is
323 used in all query replies from the server. http://www.ietf.org/rfc/rfc4627.txt
325 For all other commands, the reply will be a message string.
327 EXAMPLES
328 --------
329 The following examples illustrate the use of the commands and what their
330 replies look like. The replies are pretty-printed to more easily follow them.
332 LIST hosts;
333 [{
334 "name": "host1.example.com",
335 "last_update": "2001-02-03 04:05:06 +0700",
336 "update_interval": "5m4s",
337 "backend": ['backend::mk-livestatus']
338 },{
339 "name": "host2.example.com",
340 "last_update": "2001-02-03 04:05:06 +0700",
341 "update_interval": "10s",
342 "backend": ['backend::mk-livestatus','backend::collectd::unixsock']
343 }]
345 FETCH host 'host1.example.com';
346 {
347 "name": "host1.example.com",
348 "last_update": "2001-02-03 04:05:06 +0700",
349 "update_interval": "5m4s",
350 "backend": ['backend::mk-livestatus'],
351 "attributes": [{
352 "name": "architecture",
353 "value": "amd64",
354 "last_update": "2001-02-03 04:05:06 +0700",
355 "update_interval": "5m4s",
356 "backend": ['backend::mk-livestatus']
357 },{
358 ...
359 }],
360 "services": [{
361 "name": "some service",
362 "last_update": "2001-02-03 04:05:06 +0700",
363 "update_interval": "5m4s",
364 "backend": ['backend::mk-livestatus']
365 },{
366 ...
367 }]}
369 LOOKUP hosts MATCHING attribute['architecture'] = 'amd64';
370 [{
371 "name": "host1.example.com",
372 "last_update": "2001-02-03 04:05:06 +0700",
373 "update_interval": "5m4s",
374 "backend": ['backend::mk-livestatus'],
375 "attributes": [{
376 "name": "architecture",
377 "value": "amd64",
378 "last_update": "2001-02-03 04:05:06 +0700",
379 "update_interval": "5m4s",
380 "backend": ['backend::mk-livestatus']
381 },{
382 ...
383 }],
384 "services": [{
385 "name": "some service",
386 "last_update": "2001-02-03 04:05:06 +0700",
387 "update_interval": "5m4s",
388 "backend": ['backend::mk-livestatus']
389 },{
390 ...
391 }]},{
392 ...
393 }]
395 SEE ALSO
396 --------
397 manpage:sysdb[1], manpage:sysdb[7]
399 RFC 4627 (JSON): http://www.ietf.org/rfc/rfc4627.txt
401 The SysDB homepage: https://sysdb.io/
403 AUTHOR
404 ------
405 SysDB was written by Sebastian "tokkee" Harl <sh@tokkee.org>.
407 COPYRIGHT
408 ---------
409 Copyright (C) 2012-2014 Sebastian "tokkee" Harl <sh@tokkee.org>
411 This is free software under the terms of the BSD license, see the source for
412 copying conditions. There is NO WARRANTY; not even for MERCHANTABILITY or
413 FITNESS FOR A PARTICULAR PURPOSE.
415 // vim: set tw=78 sw=4 ts=4 noexpandtab spell spelllang=en_us :