Context tables widget
You can use the Context tables widget to get analytics based on SQL queries.
When creating this widget, you must specify the settings described in the tables below.
The 
tab:
The following table lists the settings that must be specified on the tab.
Description of parameters
Setting
|
Description
|
Graph
|
Graph type. The following graph types are available:
- Bar chart.
- Pie chart.
- Counter.
- Table.
|
Tenant
|
The tenant for which data is displayed in the widget. You can select multiple tenants. By default, data is displayed for tenants selected in layout settings.
|
Correlator
|
Name of the correlator that contains the context table for which you want to receive information.
|
Context table
|
Name of the context table for which you want to receive information.
The same context table can be used in multiple correlators. However, a separate entity of the context table is created for each correlator. Therefore, the contents of the context tables used by different correlators are different even if the context tables have the same name and ID.
|
SQL query field
|
This field lets you manually enter a query for filtering and searching context table data. By default, for each widget type, the field contains a query that obtains the context table schema and the key by key fields.
The query structure is similar to that used in event search.
When creating a query based on context tables, you must consider the following:
- For the
FROM function, you must specify the `records` value. - You can get data only for the fields specified in the context table schema.
- You can use supported features of ClickHouse.
- If you want to receive data for fields whose names contain spaces and Cyrillic characters, you must also enclose such names in quotes in the query:
- In the
SELECT function, enclose aliases in double quotes or backticks: "<alias>", `<another alias>`; - In the
ORDER BY function, enclose aliases in backticks: `<another alias>` - Event field values are enclosed in straight quotes:
WHERE DeviceProduct = 'Microsoft'
- Names of event fields do not need to be enclosed in quotes.
- If the name of an active list field begins or ends with spaces, these spaces are not displayed by the widget. The field name must not contain spaces only.
- If the values of the active list fields contain trailing or leading spaces, it is recommended to use the
LIKE '%<field value>%' function to search by them. - You can use the
_count service field (how many times this record has been added to the context table), as well as custom fields. - The
metric and value aliases in SQL queries cannot be edited for any type of active lists analytics widget, except tables. - If a date and time conversion function is used in an SQL query (for example,
fromUnixTimestamp64Milli) and the field being processed does not contain a date and time, an error will be displayed in the widget. To avoid this, use functions that can handle a null value. Example: SELECT _key, fromUnixTimestamp64Milli(toInt64OrNull(DateTime)) as Date FROM `records` LIMIT 250. - Large values for the
LIMIT function may lead to browser errors. - If you select Counter as the chart type, you must specify the method of data processing for the values of the
SELECT function: count, max, min, avg, sum.
Special considerations when using aliases in SQL functions and SELECT statements: you may use double quotes and backticks: ", `.
When using spaces or non-Latin characters, the alias must be enclosed in double quotes: "<Alias with a space>", values must be enclosed in straight single quotes: '<Value with a space>'.
When displaying data for the previous period, sorting by the count(ID) parameter may not work correctly. We recommend sorting by the metric parameter. For example, SELECT count(ID) AS "metric", Name AS "value" FROM `events` GROUP BY Name ORDER BY metric ASC LIMIT 250.
|
You can get the names of the tenants in the widget instead of their IDs.
If you want the names of tenants to be displayed in active list widgets instead of tenant IDs, in correlation rules of the correlator, configure the function for populating the active list with information about the corresponding tenant. The configuration process involves the following steps:
- Export the list of tenants.
- Create a dictionary of the Table type and import the previously obtained list of tenants into the dictionary.
- Add a local variable with the dict function for mapping the tenant name to tenant ID to the correlation rule.
Example:
- Variable:
TenantName - Value:
dict ('<Name of the previously created dictionary with tenants>', TenantID)
- Add an action with active lists to the correlation rule. This action will write the value of the previously created variable in the key-value format to the active list using the Set function. As the key, specify the field of the active list (for example,
Tenant), and in the value field, reference the previously created variable (for example, $TenantName).
When this rule triggers, the name of the tenant mapped by the dict function to the ID from the tenant dictionary is placed in the active list. When creating widgets for active lists, you can get the name of the tenant by referring to the name of the field of the active list (in the example above, Tenant).
The method described above can be applied to other event fields with IDs.
Examples of SQL queries for receiving analytics based on active lists:
SELECT * FROM `records` WHERE "Event source" = 'Johannesburg' LIMIT 250This query returns the key of the active list where the field name is "Event source" and the value of this field is "Johannesburg".
SELECT count(_key) AS metric, Status AS value FROM `records` GROUP BY value ORDER BY metric DESC LIMIT 250Query for a pie chart, which returns the number of keys in the active list (count aggregation over the _key field) and all variants of the Status custom field. The widget displays a pie chart with the total number of records in the active list, divided proportionally by the number of possible values for the Status field.
SELECT Name, Status, _count AS Number FROM `records` WHERE Description ILIKE '%ftp%' ORDER BY Name DESC LIMIT 250Query for a table, which returns the values of the Name and Status custom fields, as well as the service field _count for those records of the active list in which the value of the Description custom field matches ILIKE '%ftp%'. The widget displays a table with the Status, Name, and Number columns.
|
The 
tab:
The following table lists the settings that must be specified on the tab.
This tab is displayed if on the tab, in the Graph field, you have selected Bar chart.
Description of parameters
Setting
|
Description
|
Y-min and Y-max
|
Scale of the Y axis.
Negative values can be displayed on chart axes. This is due to the scaling of charts on the widget and can be fixed by setting zero as the minimum chart values instead of Auto.
|
X-min and X-max
|
Scale of the X axis.
Negative values can be displayed on chart axes. This is due to the scaling of charts on the widget and can be fixed by setting zero as the minimum chart values instead of Auto.
|
The 
tab:
The following table lists the settings that must be specified on the tab.
Description of parameters
Settings
|
Description
|
Name
|
Name of the widget.
|
Description
|
Description of the widget.
|
Color
|
The color used for displaying the information:
- default for your browser's default font color
- green
- red
- blue
- yellow
|
Horizontal
|
Makes the histogram horizontal instead of vertical.
When this setting is enabled, all available information is fitted into the configured widget size. If the amount of data is great, you can increase the size of the widget to display it optimally.
|
Show total
|
Shows sums total of the values.
|
Show legend
|
Displays a legend for the analytics. The toggle switch is turned on by default.
|
Show nulls in legend
|
Displays parameters with a null value in the legend for analytics. The toggle switch is turned off by default.
|
Article ID: 265363, Last review: May 30, 2025