MXQL Syntax Guide

Format

MXQL consists of command and operands on each line, separated by spaces.

<command> <operand>

Command is the reserved word that is a single word. Command is input in uppercase characters and Operand in lowercase characters. The format of Operand that can be input for each Command is fixed. Operand can have 4 types of values.

1. In case there is no operand

   TAGLOAD

2. String (number or word)

   CATEGORY app_counter

3. String array

   SELECT [ time, pcode ]

4. JSON string type

   FILTER { key : tx_count, value : 5}

Sample MXQL query

CATEGORY app_counter
TAGLOAD
SELECT [ time, pcode ]
FILTER { key : tx_count, value : 5}

Test environment

In Home > Select Project > Sitemap > Laboratory > MXQL Data, you can test the MXQL query.

Note

In metrics, tags and fields are separated, but the MXQL Data menu displays tags and fields without distinction.

Step-by-step configuration

MXQL has can be configured step by step. The types of commands that can be used for each step have been fixed, and the names and characteristics of each step are as follows:

1. Metric selection: Select one of metrics collected by each agent.

2. Metrics loading: The metrics are loaded with the values set in the previous step. In most cases, use TAGLOAD. Only in special cases, use FLEXLOAD. To use FLEXLOAD, see the following.

3. Metrics processing: It is performed step by step for the metrics loaded in the previous step.

Example

# Metrics selection step
CATEGORY app_counter -- Category selection

# Metrics loading step
TAGLOAD -- Viewing the 1000 data

# Metrics processing step
SELECT [time, oid, active_tx_count, tx_count, tx_error] -- Pass only 5 fields of 1000 data to the next step
FILTER {expr : "tx_count > 40"}        -- Pass only 100 items of 1000 data
FILTER {expr : "active_tx_count > 10"} -- Pass only 10 items of 100 data
FILTER {expr : "tx_error < 3"}        -- Pass only 3 items of 10 data

The following is an example of the metrics returned after the metrics processing step.

Comment

Sentences beginning with "#" or "--" are ignored.

Example

# Data lookup setting
CATEGORY app_counter

# Data lookup
TAGLOAD

# Data processing
SELECT [ time, pcode ]
FILTER { key : tx_count, value : 5}

MetricValue (composite value)

MetricValue (composite value) is an MXQL data structure that conveniently supports frequently used operations in the metrics processing step. The GROUP and UPDATE commands in the metrics processing step can be used only when the metrics are saved in the MetricValue format.

For example, let's assume that there are following data:

time	tx_count
2021/06/24 13:40:00	1
2021/06/24 13:40:10	2
2021/06/24 13:40:20	3
2021/06/24 13:40:30	4
2021/06/24 13:40:40	5
2021/06/24 13:40:50	6

If you perform the merge option of GROUP with the above data at an interval of 30 seconds, you can convert the data into the following format.

time	tx_count
2021/06/24 13:40:00 ~ 2021/06/24 13:40:20	MetricValue for 1, 2, 3
2021/06/24 13:40:30 ~ 2021/06/24 13:40:50	MetricValue for 4, 5, 6

When converting data to MetricValue, 6 options become available.

Option	Function
sum	Adds the values in MetricValue.
min	Returns the minimum of the values in MetricValue.
max	Returns the maximum of the values in MetricValue.
last	Returns the last added one of the values in MetricValue.
avg	Returns the average for the values in MetricValue.
cnt	Returns the number of the values in MetricValue.

The MetricValue options are available using the UPDATE command.

UPDATE

CATEGORY app_counter
TAGLOAD
SELECT [ time, okindName, okind, apdex_satisfied, apdex_tolerated, apdex_total]
-- Using the merge option in the GROUP command, set the fields to be converted to MetricValue
GROUP { timeunit:5000, pk:okind, last:okindName, merge:[apdex_satisfied, apdex_tolerated, apdex_total] }
-- Apply the sum option via the UPDATE command
UPDATE { key:[apdex_satisfied, apdex_tolerated, apdex_total], value:sum }

How to use the MetricValue type data

• Set fields in the merge option of the GROUP command.

  CATEGORY app_counter
  TAGLOAD
  SELECT [ time, okindName, okind, apdex_satisfied, apdex_tolerated, apdex_total]
  -- Using the merge option in the GROUP command, set the fields to be converted to MetricValue
  GROUP { timeunit:5000, pk:okind, last:okindName, merge:[apdex_satisfied, apdex_tolerated, apdex_total] }
  -- Apply the sum option via the UPDATE command
  UPDATE { key:[apdex_satisfied, apdex_tolerated, apdex_total], value:sum }

• Since the data has been saved to the collection server, all fields have the categories stored in MetricValue format. In Sitemap > Analysis > Metrics Search > Category option, you can see the categories that can be set in Basic, 5 Minutes, and 1 Hour units. Here, the category where you can select 5 Minutes or 1 Hour is the one saved in MetricValue format.

  

  If the name of the category in which 5 Minutes or 1 Hour can be selected, is combined with {m5} or {h1}, instead of applying the merge option of the GROUP command, you can apply the sum option of the UPDATE command.

  CATEGORY app_counter{m5}
  TAGLOAD
  SELECT [time, pname, host_ip, pid, httpc_count]
  -- Even if you have not applied the GROUP command, you can apply the UPDATE command because the data is already of the MetricValue type.
  UPDATE { key : httpc_count, value : avg }

Basic operation of MetricValue type avg

The default output for MetricValue type fields is avg. avg is applied to MetricValue type fields unless a separate option has been set. The following two queries have the same result.

• In case avg has not been set

  CATEGORY app_counter
  TAGLOAD
  SELECT [time, pcode,pname, tps]
  GROUP  {timeunit:5000, pk:pcode, last: pname, merge:tps}

• In case avg has been set

  CATEGORY app_counter
  TAGLOAD
  SELECT [time, pcode,pname, tps]
  GROUP  {timeunit:5000, pk:pcode, last: pname, merge:tps}
  UPDATE {key:tps, value:avg}

Predefined MXQL query statement

You can perform MXQL by setting the path of a predefined MXQL query file without directly writing the MXQL query. For example, the MXQL queries to obtain "Number of active transactions by agent," "Number of cases by <section>," and "Last 15 seconds" are as follows:

HEADER {  act0$:I, act3$:I, act8$:I, act$:I}
TIME-RANGE {duration:15s, etime:$etime}
OIDSET {oid:$oid, okind:$okind, onode:$onode}
CATEGORY app_counter

TAGLOAD {backward:true}

SELECT [oid, oname, active_tx_0, active_tx_3, active_tx_8, active_tx_count, pcode]
FIRST-ONLY {key:oid}
RENAME {src:active_tx_0,  dst:act0}
RENAME {src:active_tx_3,  dst:act3}
RENAME {src:active_tx_8,  dst:act8}
RENAME {src:active_tx_count,  dst:act}
CREATE {key:_id_, from:oid}
CREATE {key:_name_, from:oname}

INJECT default

If the above query has been registered in the collection server, you can search the same data just by entering the following. This method is used to read and call the content of the server file stored in the set path. Enter the pre-defined file path.

/app/act_tx/act_tx_oid

In the following, you can see available queries.

References

Bind variables (parameters)

In MXQL, you can use the bind variables. Bind variables start with $. Also, only the part corresponding to the value can be used.

SKIP $skip_value

SKIP [$skip_value]

SKIP {value:$skip_value}

The bind variables cannot be passed with the key.

SKIP {$option:10}

Once you have used the bind variables in your query, the values to enter must be passed when running MXQL.

Note

Bind variable names can only contain uppercase and lowercase characters. Numbers and special characters cannot be included in the bind variable names.

Data loading method

Data that can be searched in MXQL is divided into two types depending on the format of Metrics.

• Data in which the metric is divided into tags and fields to be loaded using the stored data TAGLOAD.
• All metrics data stored in fields (data that can be loaded using FLEXLOAD)

Most categories are using TAGLOAD. FLEXLOAD is used only when using data of categories included in the following.

List of predefined MXQL queries provided by the SaaS service

The main purpose of the function to set an MXQL query as a path without directly writing it is not to simply call a complex query, but to allow the administrator to write and use the query as intended. Therefore, rather than checking which queries are included in Yard, use them by registering queries by yourself. Because the case to use the JOIN command is a special case among the cases where MXQL queries are used, the administrator must register the query directly and use the path of the file.

Note

You can save the query to register as a file in the path set in mxql_root of the yard.conf file. (default ./mxql)

Number of active transactions by agent, number of cases, last 15 seconds

• Path: /app/act_tx/act_tx_oid

• Query:

  HEADER {  act0$:I, act3$:I, act8$:I, act$:I}
  
  TIME-RANGE {duration:15s, etime:$etime}
  
  OIDSET {oid:$oid, okind:$okind, onode:$onode}
  
  CATEGORY app_counter
  
  TAGLOAD {backward:true}
  
  SELECT [oid, oname, active_tx_0, active_tx_3, active_tx_8, active_tx_count]
  FIRST-ONLY {key:oid}
  RENAME {src:active_tx_0,  dst:act0}
  RENAME {src:active_tx_3,  dst:act3}
  RENAME {src:active_tx_8,  dst:act8}
  RENAME {src:active_tx_count,  dst:act}
  
  CREATE {key:_id_, from:oid}
  CREATE {key:_name_, from:oname}

Detailed information by agent, number of active transactions by agent, number of cases, last 15 seconds

• Path: /app/act_tx/agent_with_tx

• Query:

  CATEGORY agent_list
  
  OIDSET {oid:$oid, okind:$okind, onode:$onode}
  
  FLEXLOAD
  
  JOIN {query:'/app/act_tx/act_tx_oid', pk:oid, field:[act0,act3,act8, act] }
  
  UPDATE {key:act0, notnull:0}
  UPDATE {key:act3, notnull:0}
  UPDATE {key:act8, notnull:0}
  UPDATE {key:act, notnull:0}
  
  RENAME {src:[act0, act3, act8, act],  dst:[normal, slow, verySlow, total]}
  
  INJECT default