Queries

This section outlines the structure and configuration parameters used for defining queries in our dashboarding system. These queries are used to fetch data for widgets in dashboards and in reports.

We try prioritize a flat structure for our queries, avoiding nested or tree-like structures as long as possible. This approach promotes simplicity, easier implementation. Maybe over time the structure will have to evolve to more nested structure or even AST

Full struct of queries can be found in types.go. Here i a breakdown of the query configuration parameters.

Alias

Type: string

optional, sometimes required

The Alias field serves a dual purpose within our query structure. Primarily, it provides a user-friendly name for the query, making it easier to understand and manage within dashboards. This field can be mandatory for certain widget types, especially specialized components. For example, a segmented-circle widget requires a query named segments.

Beyond user-friendly names or mandatory named queries the alias field can also be used to mark queries as variable names. All variable names must start with a dollar sign $. For instance, a variable named $current could be used to retrieve the current hash or time. These variables can then be referenced in other queries for filtering purposes, using expressions like $current.hash or $current.time.

This dual functionality of the Alias field enhances both the readability and flexibility of our query system.

MetricName

Type: string

required

Specifies the source to be queried. In most cases this will refer to table name of analyticsdb.

Fields

Type: []QueryField

required with at least one field

Defines the specific fields to be retrieved from the table. This field is mandatory and requires at least one field.

Query field

Name

required

Name of the column from the table to be retrieved.

FieldAlias

Type: string

optional

The alias of the field that will be used when returning the data. Field alias can begin with $ character making this field a variable which can be used in other parts of the query.

AggFunc

Type: string

optional

Aggregation function to be used. If no function is specified by defualt AggNone is used which means thre is no aggregation.

The list of supported aggregation functions:

• sum
• mean
• max
• min
• count
• count_distinct
• count_new
• trend
• latestBy

Func

Type: string

optional

Not always we want to aggregate data. Sometimes we just want to apply simple operations on fields. Currently there are not many functions but the list can grow over time.

• daysBetween accepts one func arg. This function calculates the number of days between two dates. First date comes from the Name (base column) and second comes from FuncArgs.

Filter (post-filter)

Type: []Filter

optional

This is used for post-filtering. This means when you run a query which returns some results you may want to include all of them in aggregation but you may want to use only part of this. For the later you need post-filtering.

An example where you may need it is that you want to find all the vulnerabilities but in one column you want to see couunt of critical vulnerabilities and in another colum you want to see number of low severity vulnerabilities. For this purpose you create two fields and apply two different post-filters, one for each field.

Read more details about filters in section.

Filter (regular filter)

Type: []Filter

optional

Query filters are composed of several fields that work together to define a filter. Notably, both regular filters and post-filters share the same configuration, despite being applied at different stages of the query process. The key difference lies in their application points: regular filters are applied at the top level, while post-filters are applied at the field level.

Field

Type: string

required

The name of a field/column you apply filter on. For example commit_hash or vulnerability_severity.

Values

Type: []string

optional

Values that will be used for filtering. If no operator is specified all the values will be used the same whay if they are used with IN operator in SQL.

ValuesVariables

Type: []string

optional

When configured, Values Variables take precedence over Values. They function similarly to Values, but with one key distinction: they utilize dynamic values derived from variables.

As an exmaple you may want to use this to filter data only for the "latest" commit hash where you extracted latest commit hash in some of the variable queries before.

Not

Type: boolean

optional, default false

If the IN operator is used and not is set to true then it will just negate IN operator.

Operator

Type: string

optional, default in

If operator is specified it will be used in filter condition. There are a couple operators supported: in, >, >=, <, <=, between

GroupBy

Type: []string

optional

Fields that will be used to group the data. It doesn't have to contain only physical fields. You can specify even field alias.

TopBy

Type: string

optional

Used to extract the top element from each group, where the group is built based on the name of the field passed as the topBy argument.

OrderBy

Type: []OrderBy

optional

Defines the sorting order for the retrieved data. Each orderBy element has to specify field (strubg) and descending flag (boolean).

TimeInterval

Type: string

optional

Time interval used in time series queries. Possible values: 1m, 1h, 1d, 1w, 1M, 1Y.

FillMissing

Type: bool

optional, default false

Determines whether missing data points should be filled with previous value

TimeField

Type: string

required

Specifies the field representing the timestamp for the data.

From

Type: time.Time

Defines the start time for the query.

Until

Type: time.Time

Defines the end time for the query.

Format

Type: sting

optional, default timeSeries

Specifies the desired format for the query results. Supported formats include timeSeries and table.

Note: The specific types used for MetricName, QueryField, Filter, OrderBy, TimeInterval, TimeField, and ResultFormat are defined within the types.go file.