This script will output the AWS account ID, AWS region, and other user metrics for single node installations of RStudio Workbench running in AWS.

rstudio-user-report.sh

#!/bin/bash

METADATA=$(curl --silent http://169.254.169.254/latest/dynamic/instance-identity/document)
REGION=$(echo "$METADATA" | grep 'region' | cut -d : -f2 | awk -F\" '{print $2}')
ACCOUNTID=$(echo "$METADATA" | grep 'accountId' | cut -d : -f2 | awk -F\" '{print $2}')

METRICS=$(sqlite3 /var/lib/rstudio-server/rstudio.sqlite <<EOF
select \
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-1   days') then 1 else 0 end) as DAU0,
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-7   days') then 1 else 0 end) as WAU0,
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-30  days') then 1 else 0 end) as MAU0,
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-365 days') then 1 else 0 end) as YAU0,
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-1   days') then 1 else 0 end) as DAU1,
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-7   days') then 1 else 0 end) as WAU1,
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-30  days') then 1 else 0 end) as MAU1,
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-365 days') then 1 else 0 end) as YAU1 
from licensed_users;
EOF
)

printf "ACCOUNTID|REGION|METRICS|DAU0|WAU0|MAU0|YAU0|DAU1|WAU1|MAU1|YAU1\n$ACCOUNTID|$REGION|$METRICS\n"

Running this script

Save this script to your EC2 instance running RStudio Workbench. Make it an executable: chmod 755 rstudio-user-report.sh. Then execute it from the command line. The AWS account ID, AWS region, and other user metrics will print to standard output.

Example

$ curl https://gist.githubusercontent.com/nwstephens/c8a7199118e97d12727ee5f6d5ddb572/raw -o rstudio-user-report.sh
$ chmod 755 rstudio-user-report.sh
$ ./rstudio-user-report.sh
ACCOUNTID|REGION|METRICS|DAU0|WAU0|MAU0|YAU0|DAU1|WAU1|MAU1|YAU1
263245908434|us-west-2|3.0|4.0|5.0|5.0|0.0|0.0|0.0|0.0

Output metrics

METRIC	DESCRIPTION
ACCOUNTID	AWS account ID
REGION	AWS region
DAU0	Daily active users unlocked
WAU0	Weekly active users unlocked
MAU0	Monthly active users unlocked
YAU0	Yearly active users unlocked
DAU1	Daily active users locked
WAU1	Weekly active users locked
MAU1	Monthly active users locked
YAU1	Yearly active users locked

About

This script queries the SQLite database that RStudio Workbench uses for single-node installations. By default, this database is stored at /var/lib/rstudio-server/rstudio.sqlite and should only be readable by administrators. See the database section in the RStudio Workbench Admin Guide for more information.

Alternatively, you can simplify the bash script by calling the SQLite query from a file (e.g. query.sql).

For example:

query.sql

select 
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-1   days') then 1 else 0 end) as DAU0,
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-7   days') then 1 else 0 end) as WAU0, 
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-30  days') then 1 else 0 end) as MAU0, 
total(CASE WHEN locked=0 and last_sign_in >= datetime('now', '-365 days') then 1 else 0 end) as YAU0, 
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-1   days') then 1 else 0 end) as DAU1,
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-7   days') then 1 else 0 end) as WAU1, 
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-30  days') then 1 else 0 end) as MAU1, 
total(CASE WHEN locked=1 and last_sign_in >= datetime('now', '-365 days') then 1 else 0 end) as YAU1 
from licensed_users;

rstudio-user-report.sh

METADATA=$(curl --silent http://169.254.169.254/latest/dynamic/instance-identity/document)
REGION=$(echo "$METADATA" | grep 'region' | cut -d : -f2 | awk -F\" '{print $2}')
ACCOUNTID=$(echo "$METADATA" | grep 'accountId' | cut -d : -f2 | awk -F\" '{print $2}')
METRICS=$(sqlite3 /var/lib/rstudio-server/rstudio.sqlite '.read query.sql')
printf "$ACCOUNTID|$REGION|$METRICS"