Filtering data on a single widget

KUMA allows you to filter data in widgets of the Events type by selecting filtering parameters in the dashboard.

For filtering data on a single widget, you need to specify one or more local variables in the SQL query in widget properties. Variables allow you to modify this SQL query by substituting the data from the widget on the dashboard into the query. The widget filters the data based on the new values of the variables and refreshes the chart. You do not need to manually change the SQL query in the properties of the widget.

We do not recommend setting more than five variables within a dashboard. This can lead to unstable operation of the dashboard.

Configuring data filtering on a single widget

To configure local variables for data filtering within a widget:

1. In the Dashboards section, select a dashboard and click the Edit button to enter the dashboard editing mode.
2. Select an existing Events widget or click the Add widget button and then select the Events widget type.
3. In the widget properties window, select the General tab.
4. Under Available variables, click Add.
5. Enter an arbitrary name for the variable, then specify the value of the variable. Local and global variable names must be unique within a dashboard.

   As the value of the variable, specify "value" or "category", that is, the field aliases used in the query. If you want to enter the value of this variable manually when filtering data in the widget, leave this field empty.

   For charts of the table type, you can specify the name of the event field as the value of the variable. This lets you filter the data in the table by the selected value of this field. The name of the event field is displayed in the header of the table column.

6. In the SQL query field, in the WHERE clause, add a function to handle the variables.

   The syntax for variables with a specific value is as follows:

   filter(<event_field_name> = $variable_name)

   The syntax for variables with an arbitrary value to be entered manually is as follows:

   filter(<event_field_name> like $variable_name)

   In this case, when typing manually, you can use the % metacharacter to search for partial text matches. For detailed information, see the ClickHouse documentation.

   You cannot use the SQL builder to add variables to an SQL query.

   View example

   Example SQL query with a local variable:

   SELECT count(ID) AS 'metric', Name AS 'value', TenantID AS 'category' FROM 'events' WHERE filter(Name = $Name) GROUP BY Name, TenantID ORDER BY metric DESC LIMIT 250

   This query uses the local variable $Name. Value of the Name event field are substituted into this variable. You can apply the variable by clicking the funnel icon () on the widget. This opens a window; in that window, you can specify a value for the variable. The widget filters the selection by the selected value of the Name field.

   Note that if in the SQL query you specify a variable for a field that takes a numeric value, such a value must be converted to a string. You can do this using the toString function. Otherwise, the SQL query returns an error. Example:

   SELECT count(ID) AS `metric`, SourcePort AS `value` FROM `events` WHERE (filter(toString(SourcePort) like $SourcePort)) GROUP BY SourcePort ORDER BY value ASC LIMIT 250

   In this query, the SourcePort event field has a numeric value, therefore it is converted to a string: toString(SourcePort).

7. Save your changes.

Applying data filtering to a single widget

To apply a local variable for data filtering within a widget:

1. In the Dashboards section, select the dashboard that contains the relevant widget. At least one local variable must be set in the properties of this widget.
2. On the widget, click the funnel icon ().
3. This opens a window; in that window, select local variables that you want to apply.
4. Save your changes.

   The widget rebuilds the chart taking into account the new value of the variable.

Page top