DBWQL – dbWatch Query Language

Introduction to dbwql

Dbwql is the query language used in dbWatch to find information about objects.

This topic should be considered as the advanced part of dbWatch. It is also the central part from which many features in dbWatch are based and should be learned by anyone who wants to get the most out of their dbWatch installation.

Dbwql highlights

• Dbwql uses pathexpressions to navigate the dbWatch state.
• Dbwql uses filterexpressions to filter a set of data.
• Dbwql is modeled on the XPath language.
• Dbwql is designed to fetch data asynchronously to efficiently fetch data from multiple servers.

Introduction

The best way to get familiar with dbwql is by using the Dbwql console, accessible as a tab on the left side of the dbWatch Monitor.

The console consists of 4 main areas. On the top is the Context area, here you can predefine anchors to use in your queries. Next is the Query area, this is where you enter dbwql queries. The middle area is an information area, this will display suggestions if you hit the tab key while typing queries, or it will display error messages if the syntax of your query is incorret. On the bottom is the Result area, this is where the results of the queries are displayed..

By default the dbwql console operates in “contionus mode”, that means that queries are executed on the fly as you type. You can change this to “triggered mode” on the “Execution” menu. Then queries will only be executed when you press “Ctrl + E”.

dbWatch defines a graph of entities that we navigate with dbwql. Each entity can contain other entities or values (values do not contain other entities). We move from one entity to the other with the “/” sign.

The start entity is the entity representing (all) the dbWatch Server(s).

If you type instance in the debug console, you will see a list of all the instances that are registered on your dbWatch Servers.

If you add a “/” and hit the tab key, you will see all the entities and values that are available from the instances.

Expand the query again by typing “instance/task”. The result will then show a list of all installed tasks (or alerts) on all instances.

As you will observe, the default is that only the last “column” in the query is shown in the result set (in this case task is shown, but not instance). In order to output other “columns”, you can add curly brackets “{}” to the entity you want to show. F.ex:

As you can see, the last column (task in this case) is always shown.

Filters

You can apply filters to the query by adding an expression that evaluates to true/false in square brackets.

In the previous example we saw that the query “instance{}/task” lists the tasks of all the registerd instances. If we want the tasks of only some of the instances, we can filter on properties of the instances. For example if we want only PostgreSQL instances that have a name that contains “dev” we could write as follows:

You can add filters to all the columns. So if we want to limit the result further to only tasks that have a name starting with “Backup”, we could do the following

Inverting a filter

Sometimes it is usefull to invert a filter expression. This
can be done like this:

instance->i/$i[databasetype='postgres']?->ignore
/$i[. != $ignore]

Anchor

What if you want to list several properties from a entity? Then we use an anchor. An anchor is defined by “->” and accessed by “$”.

For example if we want to list the name and status for all alerts on a specific instance we could do the following

As you can see above, we place all the task (of type alert) belonging to the instance called “ora test 2” in an anchor called t, then we can use $t later in the query to reference the task name and status.

Operators

The following operators are available in the dbwql language.

Operator	Meaning	Example	Description
=	equals	instance[port = 3306]	Can be used in the filter part of a dbwql.
!=	not equals	instance[port != 3306]	Can be used in the filter part of a dbwql.
<	less than	instance[total_session_count < 50]	Can be used in the filter part of a dbwql.
<=	less than or equal	instance[total_session_count <= 50	Can be used in the filter part of a dbwql.
>	greater than	instance[memory_usage > 300	Can be used in the filter part of a dbwql.
>=	greater than or equal	instance[memory_usage >= 300]	Can be used in the filter part of a dbwql.
!	inverts a boolean value	instance[!(host like ’192.168.%’)]	Can be used in the filter part of a dbwql.
&	and	instance[total_session_count < 50 & memory_usage < 100]	Can be used in the filter part of a dbwql.
|	or	instance[total_session_count < 50 | memory_usage < 100]	Can be used in the filter part of a dbwql.
like	string matching with ‘%’ wildcard	instance[host like ’192.168.%’]	Can be used in the filter part of a dbwql.
matches	regular expression matching	instance[name matches ‘[O-T].*’]	Can be used in the filter part of a dbwql.
->	create anchor	instance->i/$i/name{}/$i/status{}	Creates an anchor of an entity that can be referenced later by prefixing the assigned name with $.
x	assign column number	instance->i/$i/name{ 0 }/$i/status{ 2 }/$i/host{ 1 }	Postfixing a value with a number between curly braces causes that value to be in the resultset at the column number specified. By default only one column will be produced, containing the last value in the query.

Examples of dbwql queries

Fetch the names of all instances.

instance/name

Fetch all tasks on the instance named “Instance one”.

instance[name="Instance one"]/task

Fetch all tasks with status Alarm and the corresponding instance name

instance->inst/task[status="ALARM"]{ 0 }/$inst/name

Fetch the name of all instances of with a total_session_count above 10 and that are of databasetype oracle or sqlserver.

instance[total_session_count > 10 & (databasetype='oracle' | databasetype = 'sqlserver')]/name

Result rendering

The Debug console can render the query results based on hashtags in the query. Theese hastags work as a hint to the GUI and are not part of the dbwql language. They will currently have no effect on queries executed in other contexts (f.ex through the CLI.)

The currently available render hashtags are:

Hashtag	Example	Description
#rendertext	instance/name{#rendertext}	This is the default rendering.
#renderdate	instance/task/lastruntimestamp{#renderdate}	Will attempt to render the value as a datetime (and it will be sorted as an datetime, not string).
#renderstatus	instance/task{#renderstatus}	Will attempt to render the value as a status icon.
#renderint	instance/db_count{#renderint}	Will attempt to render the value as an integer (and it will be sorted as an int, not string).
#renderdecimal	instance/db_count{#renderdecimal}	Will attempt to render the value as a decimal (and it will be sorted as an decimal, not string).
#renderpercent	instance/cpuload{#renderpercent(100)}	Will attempt to render the value as percent of the argument (Default 100). (So if your values are in the range 0..1, you could specify #renderpercent(1))
#renderbar	instance{}/buffer_miss_pct{#renderbar(100) #warning(70) #alarm(90)} instance{}/buffer_miss_pct{#renderpercent #renderbar(100) #labelwidth(100)}	Will attempt to render the value as a bar. The argument is max value (default 100), with no warning/alarm threshold set. Adding #warning and/or #alarm with arguments in percent will cause the bar to be colored red,orange,green based on theese thresholds. Can be combined with renderint/decimal/percent and an optional labelwidth
#renderboolean	instance{}/hasnote{#renderboolean #iconfalse #icontrue(document.png)}	Excpects a true/false value and will render the values as icons. The default icons can be overridden by specifying #iconfalse/#icontrue. Specifying no argument (like #iconfalse in the example), will result in no icon being displayed. Specifying an icon file (like #icontrue(document.png), will result in that icon being used. (Feature available from version 12.1.5)
#headericon	instance/hasnote{#headericon(editscript.png)}	Will display the specified icon as the column header
#renderraw	instance{#renderraw}/db_count	Will show the instance column as rawdata and not a “nice” string.
#sort	instance{#sort(asc)}	Will sort the result on this column. Default is descending. Legal arguments are ‘asc’ and ‘desc’.
#width	instance{#width(120)}/name	Will cause the instance column to be restricted to a width of 120px.
#hide	instance{#hide}/name	Will hide the column from the result.
#owner	instance{#owner}/name	This value will be used to create the correct menu when right clicking on a row.
#id	instance{#id}/name	This value will be used as an index when optimizing the updating of the result table.

Available properties

The through power of dbwql comes from the property system. The property system defines what topics/values are accessible through dbwql.

Read more about the property system.

Dbwql terminology

Entity

A dbwatch entity identifies a resource. Dbwatch entities consist of an entity type, id and a context. The context is in itself an entity.

Represented as a string an entity can look like this;

• “instance:My database@server:My server” or
• “user:Jack@securitycontext:default@server:My server”
  

dbWatch uses entities as nodes in a semantic graph. Entity nodes can be attatched to other nodes by a directed edge. These edges are triples, consisting of an entity node, a topic and some data.
If the data is an entity it can be described further, if not it is a leef node.

Topic

A topic is a semantic description of an edge in the semantic graph. Dbwatch comes with a set of predefined topics. It is also possible to define new topics to fit your enterprise needs.

← CLI Examples / Kerberos configuration →

Copyright © 2024 dbWatch AS