Can I query aggregated data across sessions?

Yes. This is one of the most powerful capabilities of the rtcstats-server, and it's fully open source.

How it works

When rtcstats-server processes incoming data from rtcstats.js, it doesn't just store the raw files - it also extracts call-related features and stores them in an OLAP SQL database. This database is designed for exactly this kind of analytical querying.

This means you can run SQL queries across all of your collected sessions to answer questions like:

• How many minutes did users spend in calls last week?
• How many sessions used TURN relay vs. direct connections?
• What percentage of sessions experienced CPU limiting?
• How has the call setup time changed after our last deployment?

Getting started with queries

The database is organized into five tables:

• rtcstats-server which contains pointers to the dump, user/session/conference identifiers as well as an id field which all other tables refer to.
• features_metadata whose dump_id field refers to the rtcstats-server table's id field. It contains information such as the session start time, number of peerconnections used, or geolocation.
• features_client whose dump_id field refers to the features_metadata table's id field. It contains client-level features such as the number of CPU cores, device memory, how often enumerateDevices was called, etc. These are the same for all peerconnections in the session.
• features_connection whose dump_id field refers to the features_metadata table's id field. It contains peerconnection-level features such as the time it took to establish the ICE/DTLS connection, what candidate pair was used, and the geolocation of the relay server.
• features_track whose connection_id field refers to the features_connection table's id field. It contains per-track information such as the track's media type, whether the track is outgoing or incoming, how long the track lasted, or what encoder was used.

To list the features associated with a particular dump id, use the following SQL query:

SELECT
  *
FROM "rtcstats-server" AS server
JOIN features_metadata ON features_metadata.dump_id = server.id
JOIN features_client ON features_client.dump_id = features_metadata.id
JOIN features_connection ON features_connection.dump_id = features_metadata.id
JOIN features_track ON features_track.connection_id = features_connection.id
WHERE
  server.id = 12345;

The list of features and fields available is constantly expanding, so it is best to use your favorite database explorer to see what is available. Typically, you will have a cookbook with a few queries that you care about. In our experience, quite a few of these involve tracking a certain metric over time (such as daily or weekly calls and minutes) or across browser versions.

How many records get inserted into the database per day

To see how many records get inserted into the database per day, use:

SELECT
  COUNT(*),
  DATE_TRUNC('day', created_at) AS day
FROM "rtcstats-server"
GROUP BY
  day
ORDER BY
  day ASC;

This should roughly match your daily number of users or calls.

How many minutes do users spend in a call per day

Likewise, querying call duration is possible:

SELECT
  DATE_TRUNC('day', server.created_at) AS day,
  SUM(duration) / (60 * 1000) AS minutes
FROM "rtcstats-server" AS server
JOIN features_metadata ON features_metadata.dump_id = server.id
JOIN features_connection ON features_connection.dump_id = features_metadata.id
GROUP BY
  day
ORDER BY
  day ASC;

How many sessions were relayed in a particular country

Queries can get pretty complex. Here is one example of querying the total number of calls and the number of calls using a TURN server, splitting the results by the country of the user:

SELECT
  COUNT(*) FILTER (
    WHERE connection.first_candidate_pair_local_type = 'relay'
    OR connection.first_candidate_pair_remote_type = 'relay'
  ) AS relayed,
  COUNT(*) AS total,
  metadata.location_country
FROM "rtcstats-server" AS server
JOIN features_metadata AS metadata ON metadata.dump_id = server.id
JOIN features_connection AS connection ON connection.dump_id = metadata.id
WHERE
  connection.connection_time IS NOT NULL
GROUP BY
  metadata.location_country;

See also

• How to use sessionId, conferenceId and userId - correlating data across sessions
• Active observability - the full observability approach
• rtcstats-server setup guide