You can use the Events widget to get analytics based on SQL queries.
When creating this type of widget, you must set values for the following settings:
The tab:
Graph is the type of the graph. The following graph types are available:
Pie chart.
Bar chart.
Counter.
Line chart.
Table.
Date Histogram.
Tenant is 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.
Period is the period for which data is displayed in the widget. The following periods are available:
As layout means data is displayed for the period selected for the layout. The default value.
1 hour—data is displayed for the previous hour.
1 day—data is displayed for the previous day.
7 days—data is displayed for the previous 7 days.
30 days—data is displayed for the previous 30 days.
In period—data is displayed for a custom time period. If you select this option, use the opened calendar to select the start and end dates of the period and click Apply Filter. The date and time format depends on your operating system's settings. You can manually change the date values if necessary.
The upper boundary of the period is not included in the time slice defined by it. In other words, to receive analytics for a whole day, you must configure the period as <Day 1>, 00:00:00 – <Day 2>, 00:00:00 instead of <Day 1>, 00:00:00 – <Day 1>, 23:59:59.
Show data for previous period—enable the display of data for two periods at the same time: for the current period and for the previous period.
Storage is the storage that is searched for events.
SQL query field () is the query for manually filtering and searching for events. You can create a query in Builder by clicking .
SELECT—event fields that should be returned. The number of available fields depends on the selected graph type.
In the drop-down list on the left, select the event fields for which you want to display data in the widget.
The middle field displays what the selected field is used for in the widget: metric or value.
If you selected the Table graph type, in the middle fields, you must specify column names using ANSII-ASCII characters.
In the drop-down list on the right, you can select an operation to be performed on the data:
count—event count. This operation is available only for the ID event field. Used by default for line charts, pie charts, bar charts, and counters. This is the only option for date histogram.
max is the maximum value of the event field from the event selection.
min is the minimum value of the event field from the event selection.
avg is the average value of the event field from the event selection.
sum is the sum of event field values from the event selection.
SOURCE is the type of the data source. Only the events value is available for selection.
WHERE—conditions for filtering events.
In the drop-down list on the left, select the event field that you want to use for filtering.
Select the necessary operator from the middle drop-down list. The available operators depend on the type of value of the selected event field.
In the drop-down list on the right, enter the value of the condition. Depending on the selected type of field, you may have to manually enter the value, select it from the drop-down list, or select it on the calendar.
You can add search conditions by clicking Add condition or remove search conditions by clicking .
You can also add groups of conditions by clicking Add group. By default, groups of conditions are added with the AND operator, but you can change the it if necessary. Available values: AND, OR, NOT. Group conditions are deleted using the Delete group button.
GROUP BY—event fields or aliases to be used for grouping the returned data. This parameter is not available for Counter graph type.
ORDER BY—columns used as the basis for sorting the returned data. This parameter is not available for the Date Histogram and Counter graph types.
In the drop-down list to the left, select the value that will be used for sorting.
Select the sort order from the drop-down list on the right: ASC for ascending, DESC for descending.
For Table type graphs, you can add sorting conditions by clicking Add column.
LIMIT is the maximum number of data points for the widget. This parameter is not available for the Date Histogram and Counter graph types.
Search condition parameters for the widget showing average bytes received per host
The following limitations apply:
The metric and value aliases in SQL queries cannot be edited for any type of event analytics widget, except tables.
Aliases in widgets of the Table type can contain Latin and Cyrillic characters, as well as spaces. When using spaces or Cyrillic, the alias must be enclosed in quotation marks: "An alias with a space", `Another alias`.
ARRAY JOIN SQL queries are not supported.
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.
In widgets of the Counter type, you must specify the method of data processing for the values of the SELECT function: count, max, min, avg, sum.
The tab:
The tab is displayed if on the tab in the Graph field you have selected one of the following values: Bar chart, Line chart, Date Histogram.
The Y-min and Y-max values set the scale of the Y axis.
The X-min and X-max values set the 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.
Line-width is the width of the line on the graph. This field is displayed for the "Line chart" graph type.
Point size is the size of the pointer on the graph. This field is displayed for the "Line chart" graph type.
The tab:
Name is the name of the widget.
Description is the description of the widget.
Color is 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 option is enabled, when a widget displays a large amount of data, horizontal scrolling is not available and all available information is fit into the fixed size of the widget. If there is a lot of data to display, it is recommended to increase the widget size.
Show total shows sums total of the values.
Legend displays a legend for 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.
Decimals is the number of decimals to which the displayed value must be rounded off.
Period segments length (available for graphs of the Date Histogram type) sets the length of segments into which you want to divide the period.