OMERO server stats for Images, frames and logins

omero_stats.sh

#!/bin/bash

set -e
set -u
set -x

HOSTNAME="localhost"
DATABASE="omero"
USER="omero-x"
THIRTY_DAYS_AGO="$(date -d '30 days ago' '+%Y-%m-%d %H:%M:00')"
SIXTY_DAYS_AGO="$(date -d '60 days ago' '+%Y-%m-%d %H:%M:00')"
NINETY_DAYS_AGO="$(date -d '90 days ago' '+%Y-%m-%d %H:%M:00')"

echo "# of Images (in an OME sense)"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select count(id) from image;"

echo "# of frames"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select SUM(sizez*sizec*sizet) from pixels where image in (select child from datasetimagelink);"

echo "# of unique users in the last 30 days"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select distinct session.owner, experimenter.omeName from session, experimenter where session.owner = 
experimenter.id and started > '$THIRTY_DAYS_AGO';"

echo "# of unique users in the last 60 days"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select distinct session.owner, experimenter.omeName from session, experimenter where session.owner = experimenter.id and started > '$SIXTY_DAYS_AGO';"

echo "# of unique users in the last 90 days"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select distinct session.owner, experimenter.omeName from session, experimenter where session.owner = experimenter.id and started > '$NINETY_DAYS_AGO';"