A collection of recipes for dealing with rtcstats

rtcstats-cookbook.md

How to access Redshift?

Amazon Redshift is a data warehouse product based on PostgreSQL. You can access the rtcstats databases HERE. We have a prod and a pilot database, use accordingly. Use username "admin" and "temporary credentials" to authenticate.

How to query/find all the sessions/endpoints in a call?

In this context a session equals an endpoint. The column name is statssessionid. If you know the meeting unique id and you want to find all the endpoints/sessions in that call this SQL query will do:

SELECT statssessionid FROM "rtcstats"."public"."rtcstats"
WHERE meetinguniqueid = 'MEETING_UNIQUE_ID'

if you have the unique meeting id, or if you have the conference id from callstats (which is more likely)

SELECT statssessionid FROM "rtcstats"."public"."rtcstats"
WHERE meetingurl = 'CALLSTATS_MEETING_ID'

What's an easier way?

Just go to rtc visualizer and search for your call.

How to download a bunch of sessions from S3?

pbpaste | cut -d' ' -f1 | xargs -n 1 -I {} aws s3 cp s3://jitsi-micros-rtcstats-server/{}.gz .

NOTE: Be quick to download the raw data before they expire! A typical lifecycle rule is to expire the data after 30 days.

How to process sessions from S3?

A simple one-liner can be this;

aws s3 cp s3://rtcstats-server-bucket/023e3641-6e68-46c0-8c79-5b436b0e41bc.gz - | gunzip | ./bin/extract.js /dev/stdin

Mass processing can be done with:

for session in *.gz; do bash -c "zcat $session | ./bin/extract.js /dev/stdin > $session-result.json" ; done

How to extract stats from the results (a few examples)

cat ~/rtcstats-sessions/*result.json|jq '.aggregates | to_entries[] | .value | select(.isP2P == true) | .usesRelay' -C|sort | uniq -c
cat ~/rtcstats-sessions/*result.json|jq '.aggregates | to_entries[] | .value | select(.isP2P == false) | .usesRelay' -C|sort | uniq -c

How to access Grafana?

Aaron has created a new helm release called new-grafana and deployed the redshift plugin and configured it to talk to the various rtcstats redshifts in both pilot and prod k8s clusters

1. Get your 'admin' user password by running:

   kubectl get secret --namespace default new-grafana -o jsonpath="{.data.admin-password}" | base64 --decode ; echo

2. The Grafana server can be accessed via port 80 on the following DNS name from within your cluster:

   new-grafana.default.svc.cluster.local

   Get the Grafana URL to visit by running these commands in the same shell:

   export POD_NAME=$(kubectl get pods --namespace default -l "app.kubernetes.io/name=grafana,app.kubernetes.io/instance=new-grafana" -o jsonpath="{.items[0].metadata.name}") kubectl --namespace default port-forward $POD_NAME 3000

3. Login with the password from step 1 and the username: admin

http://localhost:3000/d/WP03lQ8nk/rtcstats-exploration?orgId=1

How to access the Redshift DB with PSQL

1. Create a new user https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_USER.html CREATE USER foo PASSWORD 'md5e8a97b60b15745a7a0812eeb5ca942c1';
2. Grant the new user permissions GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO foo;