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"

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"