How to specify variables in a query statement - Simple Log Service

Each chart displays the query and analysis results of one or more query statements. Variables in a query statement are placeholders that can be used to dynamically modify the parameters of the query statement. You can specify variables in a query statement to filter data on a chart or a dashboard without the need to modify the query statement. You can also use variables together with interaction occurrences. For example, you can add variables when you configure an Open Logstore interaction occurrence. Then, you can click a value on the related dashboard to open the Logstore that you specify. In the Logstore, the system executes the query statement that is obtained after variable replacement to query logs.

Scenarios

Variables are in the ${{Variable name|Default value}} format. For example, if a field named method has multiple values such as PUT, POST, GET, and DELETE and the default value is PUT, you can specify the field as the ${{method|PUT}} variable. You can use only the variable or use the variable together with an interaction occurrence.

占位符变量

Filter data on a chart or a dashboard

After you specify fields in a query statement as variables, you can filter data on the chart that uses the query statement or the related dashboard without the need to modify the query statement.

Use variables together with interaction occurrences

For example, you can add a variable when you configure an Open Logstore interaction occurrence for a chart that displays request status. If you click the value 200 in the chart, you are navigated to the Logstore that you specify. You can view the logs of requests for which HTTP status 200 is returned.

Configuration examples

Filter data on a dashboard

If you add a filter of the Variable Replacement type to a dashboard and the dashboard contains a chart whose query statement includes the variable specified for the filter, the system automatically replaces the variable in the query statement for the chart with the value that you select for the filter. The variable configuration applies to all charts whose query statements include the variable on the dashboard.

Add a filter of the Variable Replacement type: After NGINX access logs are collected to Simple Log Service, you can execute a query statement in Simple Log Service to query the numbers of page views (PVs) per minute. For more information, see Add a filter of the Variable Replacement type. If you want to query the numbers of PVs every 10 seconds, 60 seconds, or 600 seconds, you must modify the query statement multiple times. To simplify operations, you can add a filter of the Variable Replacement type.

Filter data on a chart

After you configure the settings for variable replacement on the General Configurations tab, Simple Log Service adds a filter in the upper-left corner of the related chart. You can select a value from the filter drop-down list. After you select a value, Simple Log Service automatically replaces the variable in the query statement of the chart with the variable value indicated by the value that you select, and performs a query and analysis operation.

In the following query statement, change the value 60 to the ${{date | 60}} variable:

* | select __time__ - __time__ % 60 as time, COUNT(*) as pv, avg(request_time) as duration, request_method GROUP BY time, request_method order by time limit 1000

Configure the settings for variable replacement on the General Configurations tab.
Set Variable Key to date and Display Name below Variable Key to time. Set Display Name below Variable Values to min and hour, and set Replacement Value to 60 for min and 3600 for hour.
After you configure the settings, a filter appears in the upper-left corner of the chart. If you select a value from the filter drop-down list, Simple Log Service performs a query and analysis operation based on the value that you select. For example, if you select hour, Simple Log Service executes the following query statement:
```
* | SELECT __time__ - __time__ %3600 AS time, COUNT(*) AS pv, approx_distinct(remote_addr) AS uv GROUP BY time ORDER BY time LIMIT 10000
```

Use variables together with interaction occurrences

After you click a value on a dashboard to trigger an interaction occurrence, the system replaces the specified variable with the value and refreshes the charts on the dashboard by executing the query statement that is obtained after variable replacement.

You can configure interaction occurrences for a dashboard to perform drill-down analysis. For more information, see Interaction occurrences. In the following examples, NGINX access logs are used.

Example 1: After you click the part that corresponds to HTTP status code 200 in a pie chart on a dashboard, you are navigated to the related Logstore, and you can view the logs of requests for which HTTP status code 200 is returned. The query time range of the Logstore is the same as the query time range of the dashboard.
Example 2: After you configure an interaction occurrence by using a Logstore named website_log that stores NGINX access logs and two dashboards named RequestMethod and destination_drilldown, you can click a request method on the RequestMethod dashboard to access the destination_drilldown dashboard. Then, you can view the trend of PVs on the destination_drilldown dashboard.