Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?


Querying the Metrics Database


The metrics database uses the TimescaleDB extension to efficiently manage time series data from Paragon Active Assurance, providing additional functions for data aggregation and rollup.

In this chapter we will describe the database objects and fields of the metrics database and their usage within an SQL query.

For full coverage of TimescaleDB features, go to

Database Objects to Query

Each plugin in Paragon Active Assurance has a number of database views which the user can query:

  • one view for tests
  • five views for monitors with metrics aggregated at different granularities: 10 seconds (raw), 1 minute, 5 minutes, 30 minutes, and 60 minutes.

The naming convention for the view objects is the following:

  • vw_(monitor|test)_metrics_<plugin_name> for raw metrics data
  • vw_monitor_metrics_minute<minutes>_<plugin_name> for metrics aggregated over time intervals of size <minutes> for the purpose of monitoring.

For example, the HTTP plugin has the following database objects that can be queried:

Each view contains two categories of information:

  • measurement: This is metadata such as the name of the test or monitor and the Test Agent(s) used. It is needed to retrieve the information related to a task in a test or monitor, and it should be included in the WHERE clause of an SQL statement to narrow down the search. Examples of fields in this category are:

    • Common to monitoring and testing: account_short_name, task_name, stream_name, plugin_name
    • Specific to testing: test_name, test_step_name, periodic_test_name
    • Specific to monitoring: monitor_name, task_started, monitor_tags
  • metric: This is data collected in the network at a given point in time, such as throughput, delay, and jitter.

The _time column is common to all views and must be included in each query. However, the set of metrics returned depends on the task type: for example, an HTTP measurement will have a different set of metrics from a Ping measurement.

The columns holding specific measurements differ from one task to another, and they are usually part of the metrics table in the view definition.

For example, the vw_monitor_metrics_http view contains:

Running Queries in TimescaleDB

To run queries in TimescaleDB it is important to:

  • identify the measurement task that collects the metrics
  • identify and set the time interval of interest
  • set the data granularity for the metrics.

Identifying the Task of Interest

Metrics collected during execution of a test or monitor are produced by all the measurement tasks defined in the test or monitor. To uniquely identify such a task in the metrics database, it is essential to retrieve the right information from Control Center.

Identifying a Monitor Task

  • In Control Center, click the Monitoring button on the left-side bar.
  • Click List.
  • Click the monitor name in the list and note the name of the task of interest. An example is shown below:

  • Click the client name and note the name of the stream, which is displayed as shown in the following image:

  • Run this query, where monitor_name, task_name, and stream_name are taken from the Control Center GUI as just shown:

    Take note of monitor_id, task_id and stream_id in the output. For example:

Identifying a Test Task

This is similar to monitor task identification (see above) except that in the Control Center GUI, you click the Tests button on the left-side bar. The query is also modified accordingly:

Setting the Time Interval of Interest

To ensure that we are querying data in the right time interval, it is important to know the time zone of reference in the metrics database and whether it differs from the time representation in Control Center.

To get the current time zone of the database, run this query:

Example of output:

In this case, the current time zone of the database is UTC, so all time intervals are represented in UTC.

To convert a local time zone to UTC, perform the following steps:

  1. Define the local time in the format YYYY-MM-dd HH:mm (<local_time>).
  2. Select the appropriate local time zone:

    • Run this query (<continent> is the name of the continent):

      Select the time zone name corresponding to the city closest to you and assign it to local_time_zone (for example, America/New_York)

  3. Run the query below to get the time in UTC:


Here is how to convert the time 2021-12-16 15:00 from Pacific Time to UTC:

  1. <local_time> is 2021-12-16 15:00.
  2. Run this query to retrieve time zones in the Americas:

  3. Select America/Los_Angeles from the list.
  4. Run this query to have the time zone converted:

Setting Data Granularity for Metrics

Depending on the time interval of interest, it is possible to run queries on metrics using either

  • the predefined views available in TimescaleDB directly, or
  • the predefined views as a data source for metrics aggregation on custom time intervals.

Below is an example. Suppose we are analyzing the stream below:

Here we have:

  • monitor_id = 6 for the M1 monitor
  • task_id = 8 for the HTTP task in M1
  • stream_id = 10 for the stream named - localta1:eth0 (IPv4)
  • Time interval delimited by <time_start> and <time_end>:
    • <time_start> = 2022-03-23 10:04:55+00
    • <time_end> = 2022-03-23 11:04:55+00

Queries on Predefined Views

These queries are recommended if the data granularity of interest is already present in TimescaleDB.

The format of the query is:

The value of <data_source> is one of the objects listed in the Database Objects to Query section.


Given the context described in the previous paragraphs, the query

returns the value of the response_time_avg metric for a 5-minute interval detected between 2022-03-23 10:04:55+00 and 2022-03-23 11:04:55+00 for the stream - localta1:eth0 (IPv4) related to the task HTTP defined in the monitor M1.

Below is an example of output that may be returned in this case:

Queries with Custom Time Interval

To run aggregate functions on metrics based on time intervals, TimescaleDB introduced the concept of time buckets to customize the granularity of the measurements given an underlying data set.

The function time_bucket accepts arbitrary time intervals as well as optional offsets and returns the bucket start time.

A user can apply aggregate functions on metrics grouped by the output from time_bucket to define metrics granularity dynamically, if the time interval of interest is not available in the TimescaleDB database.

The format of the query is:

where <data_source> is one of the objects listed in the Database Objects to Query section and <metrics> is a comma-separated list of metrics or an expression involving metrics.

Metrics for HTTP tasks are likewise listed in the Database Objects to Query section.

<bucket_interval> denotes the interval width in use for rolling up the metric values. A value for <bucket_interval> consists of a number followed by a time unit such as:

  • s (or second) for seconds
  • m (or minute) for minutes
  • h (or hour) for hours
  • d (or day) for days


Given the context described in the previous paragraphs and the change

  • <bucket_interval> = 10m (10-minute data granularity)

the query

returns the continuous 50% percentile value of the response_time_avg metric within 10-minute intervals detected between 2022-03-23 10:04:55+00 and 2022-03-23 11:04:55+00 for the stream - localta1:eth0 (IPv4) related to the task HTTP defined in the monitor M1.

The output returned in this case looks like this:


For better query performance, it is recommended that you use as data source the view with the coarsest granularity that guarantees acceptable data accuracy.

Further information on TimescaleDB features for data analysis can be found in the TimescaleDB documentation: