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 hosts currently stored in SysDB along
36 with their services or metrics if requested. The return value is a list of
37 hosts where each host specification includes its name, the timestamp of the
38 last update of the object known to SysDB and an approximation of the interval
39 with which the host was updated. When listing services or metrics, the
40 respective objects will be included for each host as well. If a filter
41 condition is specified, only objects matching that filter will be included in
42 the reply. See the section "FILTER clause" for more details about how to
43 specify the search and filter conditions.
45 *FETCH* host '<hostname>' [*FILTER* '<filter_condition>']::
46 Retrieve detailed information about the specified host object. The return
47 value includes the hostname, a list of services referenced by the host, and a
48 list of attributes for the host and each service. If the host does not exist,
49 an error is returned. If a filter condition is specified, only objects
50 matching that filter will be included in the reply. See the section "FILTER
51 clause" for more details about how to specify the search and filter
52 conditions.
54 *LOOKUP* hosts *MATCHING* '<search_condition>' [*FILTER* '<filter_condition>']::
55 Retrieve detailed information about all host objects matching the specified
56 search condition. The return value is a list of detailed information for each
57 matching host providing the same details as returned by the *FETCH* command.
58 If no host matches the search criteria, it's not considered an error. Instead,
59 an empty list is returned. If a filter condition is specified, only objects
60 matching that filter will be included in the reply. See the sections "MATCHING
61 clause" and "FILTER clause" for more details about how to specify the search
62 and filter conditions.
64 *TIMESERIES* '<hostname>'.'<metric>' [START '<datetime>'] [END '<datetime>']::
65 Retrieve a time-series for the specified host's metric. The data is retrieved
66 from a backend data-store based on information provided by the respective
67 query plugin. The return value includes the actual start and end time of the
68 time-series and one or multiple sequences of time-stamp / value pairs. If the
69 metric does not exist or if the backend data-store is not supported, an error
70 is returned.
72 MATCHING clause
73 ~~~~~~~~~~~~~~~
74 The *MATCHING* clause in a query specifies a boolean expression which is used
75 to match host objects based on their names, their attributes, or services
76 referenced by the host. Each *MATCHING* clause may be made up of one or
77 multiple subexpressions each matching on one criteria. The following
78 subexpressions are supported by SysDB:
80 '<field>' '<operator>' '<value>'::
81 Match a named field against the specified value. See below for what fields
82 and operators are supported.
84 *NOT* '<subexpression>'::
85 Invert the boolean result of the specified subexpression.
87 '<subexpression>' *AND* '<subexpression>'::
88 '<subexpression>' *OR* '<subexpression>'::
89 Combine multiple subexpressions using logical AND or logical OR.
91 The following fields may be queried:
93 *host*::
94 The full name of the host.
96 *service*::
97 The full service name as referenced by the host.
99 *attribute*::
100 The full name of a host attribute.
102 *attribute[*'<name>'*]*::
103 The value of the named host attribute. If an attribute of the specified
104 name does not exist, each comparison is treated as if the value does not
105 match. See the documentation for the *IS NULL* and *IS NOT NULL* operators
106 for ways to check if an attribute exists.
108 '<value>' may either be a string (when matching by object names) or match the
109 type of the attribute's value (when matching attribute values). Attribute
110 values may either be a string, integer, decimal number, or a date-time value
111 (booleans and binary data are not supported by the frontend yet). See the
112 section "DATA TYPES" for more details about how specify each data-type.
114 When comparing an attribute's value using a regular expression matcher, the
115 value will be cast to a string before doing so. No other casts are supported
116 at this time.
118 The following operators may be used to match field values:
120 *=*::
121 Evaluates to true if the field value exactly matches the specified value.
123 *!=*::
124 Evaluates to true if the field value does not match the exact specified
125 value.
127 *=~*::
128 Evaluates to true if the field value matches the specified regular
129 expression. SysDB uses POSIX extended regular expressions.
131 *!~*::
132 Evalues to true if the field value does not match the specified regular
133 expression.
135 The following operators may be used, in addition, to match attribute values:
137 *<*, *\<=*, *>=*, *>*::
138 Evaluates to true if the attribute value is less than, less than or equal
139 to, greater than or equal to or greater than the specified value.
141 In addition, a named attribute may be checked for existence using the *IS
142 NULL* and *IS NOT NULL* operators. An attribute is considered to be *NULL* if
143 it is not set for a host.
145 FILTER clause
146 ~~~~~~~~~~~~~
147 The *FILTER* clause in a query specifies a boolean expression which is used to
148 filter objects included in the query's response. The filter is applied to
149 hosts, services, and attributes alike and, thus, will usually be based on the
150 core properties of the stored objects. The basic syntax for filter clauses is
151 the same as for matching clauses (see the description about subexpressions
152 above).
154 The following fields (core properties) of stored objects may be queried:
156 *.last_update*::
157 The timestamp of the last update of the object. This value is based on
158 information provided by the queried backend if possible.
160 *.age*::
161 The amount of time since the last update of the object.
163 *.interval*::
164 The interval with which the object gets updated. This value is determined
165 automatically based on a moving average determined from the update
166 timestamps of an object. It depends on the update timestamps as provided
167 by the backend (if available) and SysDB's query interval.
169 *.backend*::
170 The name of the backend (plugin) providing the data.
172 The type of the *last_update*, *age*, and *interval* fields is date-time and
173 the type of the *backend* field is string. All conditional operators may be
174 used to compare the date-time fields (but not regular expression or *IS NULL*
175 operators). The backend field may only be matched by (in)equality. Each object
176 may be provided by multiple backends. The filter matches if the specified
177 value matches any of them.
179 DATA TYPES
180 ----------
181 The SysDB query language natively supports various data-types. Constants of
182 all types may be used in any place where a value is expected.
184 *String constants*::
185 A string constant is an arbitrary sequence of characters enclosed in
186 single quotes ('''). Single quotes may be included in a string constant by
187 specifying two adjacent single quotes.
189 *Integer constants*::
190 An integer constant may be specified either as a sequence of digits or in
191 scientific notation written in the form "'a' E 'b'" (without spaces) where
192 'a' and 'b' are integers. A leading plus or minus sign specifies the sign
193 of the constant.
195 *Floating-point constants*::
196 A floating-point constant is a sequence of digits containing a decimal
197 point. Digits before or after the decimal point (but not both) are
198 optional. Floating-point constants may also be specified in scientific
199 notation by appending the letter "E" and a positive or negative integer
200 exponent. A leading plus or minus sign specifies the sign of the constant.
202 *Date and time constants*::
203 A date constant may be specified as 'YYYY-MM-DD' and time constants may be
204 specified as 'HH:MM:SS.nnnnnnnnn' where seconds and nanoseconds are
205 optional.
207 *Interval constants*::
208 An interval may be specified by one or multiple quantity and unit pairs.
209 The quantity may be any integer constant and the unit may be any of the
210 following: *Y* (years), *M* (months), *D* (days), *h* (hours), *m*
211 (minutes), *s* (seconds), *ms* (milliseconds), *us* (microseconds), or
212 *ns* (nanoseconds). Note that years and months are approximations.
214 RESPONSE FORMAT
215 ---------------
216 The JavaScript Object Notation (JSON) format, as specified in RFC 4627, is
217 used in all replies from the server. http://www.ietf.org/rfc/rfc4627.txt
219 EXAMPLES
220 --------
221 The following examples illustrate the use of the commands and what their
222 replies look like. The replies are pretty-printed to more easily follow them.
224 LIST hosts;
225 [{
226 "name": "host1.example.com",
227 "last_update": "2001-02-03 04:05:06 +0700",
228 "update_interval": "5m4s"
229 },{
230 "name": "host2.example.com",
231 "last_update": "2001-02-03 04:05:06 +0700",
232 "update_interval": "5m4s"
233 }]
235 FETCH host 'host1.example.com';
236 {
237 "name": "host1.example.com",
238 "last_update": "2001-02-03 04:05:06 +0700",
239 "update_interval": "5m4s",
240 "attributes": [{
241 "name": "architecture",
242 "value": "amd64",
243 "last_update": "2001-02-03 04:05:06 +0700",
244 "update_interval": "5m4s"
245 },{
246 ...
247 }],
248 "services": [{
249 "name": "some service",
250 "last_update": "2001-02-03 04:05:06 +0700",
251 "update_interval": "5m4s"
252 },{
253 ...
254 }]}
256 LOOKUP hosts MATCHING attribute[architecture] = 'amd64';
257 [{
258 "name": "host1.example.com",
259 "last_update": "2001-02-03 04:05:06 +0700",
260 "update_interval": "5m4s",
261 "attributes": [{
262 "name": "architecture",
263 "value": "amd64",
264 "last_update": "2001-02-03 04:05:06 +0700",
265 "update_interval": "5m4s"
266 },{
267 ...
268 }],
269 "services": [{
270 "name": "some service",
271 "last_update": "2001-02-03 04:05:06 +0700",
272 "update_interval": "5m4s"
273 },{
274 ...
275 }]},{
276 ...
277 }]
279 SEE ALSO
280 --------
281 manpage:sysdb[1], manpage:sysdb[7]
283 The SysDB homepage: https://sysdb.io/
285 AUTHOR
286 ------
287 SysDB was written by Sebastian "tokkee" Harl <sh@tokkee.org>.
289 COPYRIGHT
290 ---------
291 Copyright (C) 2012-2014 Sebastian "tokkee" Harl <sh@tokkee.org>
293 This is free software under the terms of the BSD license, see the source for
294 copying conditions. There is NO WARRANTY; not even for MERCHANTABILITY or
295 FITNESS FOR A PARTICULAR PURPOSE.
297 // vim: set tw=78 sw=4 ts=4 noexpandtab spell spelllang=en_us :