Help us improve your experience.

Let us know what you think.

Do you have time for a two-minute survey?

 
 

Querying the Metrics Database

Introduction

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 docs.timescale.com.

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

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.

Queries on Predefined Views

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

For example, if the HTTP plugin is enabled in Control Center, the following query

returns the value of the response_time_avg metric for a 5-minute interval detected between 2021-06-21 09:55:30+00 and 2021-06-21 09:57:00+00 by the stream named Stream 1.

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.

For example, if the HTTP plugin is enabled in Control Center, the query

returns the median value of the response_time_avg metric within 30-second intervals between 2021-06-21 09:55:30+00 and 2021-06-21 09:57:00+00 by the stream named Stream 1.

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

Further information on TimescaleDB features for data analysis can be found in the TimescaleDB documentation: docs.timescale.com/timescaledb/latest/getting-started/query-data/