omero_stats.py

#!/usr/bin/env python

import psycopg2
import datetime
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import matplotlib.cbook as cbook

conn = psycopg2.connect("dbname=omerodb user=omerouser host=localhost")
cur = conn.cursor()

def showAllExperimenters():
    cur.execute("SELECT * from experimenter;")
    experimenters = cur.fetchall()

    for experimenter in experimenters:
        print 'experimenter', experimenter

def countSignupsPerMonth():
    cur.execute("WITH month_year AS (SELECT eventlog.id AS id, EXTRACT(MONTH FROM event.time) AS month, EXTRACT(YEAR FROM event.time) AS year FROM eventlog,event WHERE eventlog.entitytype='ome.model.meta.Experimenter' AND eventlog.action = 'INSERT' AND eventlog.event=event.id) SELECT month_year.year, month_year.month, count(month_year.id) FROM month_year GROUP BY month_year.month, month_year.year ORDER BY month_year.year, month_year.month;")
    monthlyCounts = cur.fetchall()
    print monthlyCounts
    dates = []
    counts = []
    runningCount = 0
    for monthlyCount in monthlyCounts:
        year, month, count = monthlyCount
        dates.append(datetime.date(int(year),int(month),1))
        runningCount += count
        counts.append(runningCount)

    years    = mdates.YearLocator()   # every year
    months   = mdates.MonthLocator()  # every month
    yearsFmt = mdates.DateFormatter('%Y')

    print 'dates: ', dates
    print 'counts: ', counts

    fig, ax = plt.subplots()
    ax.plot(dates, counts)

    # format the ticks
    ax.xaxis.set_major_locator(years)
    ax.xaxis.set_major_formatter(yearsFmt)
    ax.xaxis.set_minor_locator(months)

    datemin = dates[0]              #datetime.date(dates.min().year, 1, 1)
    datemax = dates[len(dates)-1]   #datetime.date(dates.max().year+1, 1, 1)
    ax.set_xlim(datemin, datemax)

    # format the coords message box
    def price(x): return '$%1.2f'%x
    ax.format_xdata = mdates.DateFormatter('%Y-%m')
    ax.format_ydata = price
    ax.grid(True)

    # rotates and right aligns the x labels, and moves the bottom of the
    # axes up to make room for them
    fig.autofmt_xdate()

    plt.show()

def countSessionsPerUserPerMonth():
    cur.execute("SELECT omename, EXTRACT(MONTH FROM session.started) AS month, EXTRACT(YEAR FROM session.started) AS year, count(session.id) FROM session, experimenter WHERE session.owner = experimenter.id GROUP BY owner, omename, year, month ORDER BY owner, omename, year, month;")
    usage = cur.fetchall()
    users = {}

    datemin = datetime.date.today()    #datetime.date(dates.min().year, 1, 1)
    datemax = datetime.date.today()    #datetime.date(dates.max().year+1, 1, 1)

    for use in usage:
        print use
        newDate = datetime.date(int(use[2]), int(use[1]), 1)
        users.setdefault(use[0], []).append( (newDate, use[3] ))
        users[use[0]]
        if newDate < datemin:
            datemin = newDate

    print users

    years    = mdates.YearLocator()   # every year
    months   = mdates.MonthLocator()  # every month
    yearsFmt = mdates.DateFormatter('%Y')
    fig, ax = plt.subplots()
    ax.set_title('User sessions per Month')

    for user in users:
        dates, counts = zip(*users[user])
        print 'dates: ', dates
        print 'counts: ', counts
        ax.plot(dates, counts)

    # format the ticks
    ax.xaxis.set_major_locator(years)
    ax.xaxis.set_major_formatter(yearsFmt)
    ax.xaxis.set_minor_locator(months)

    ax.set_xlim(datemin, datemax)

    # format the coords message box
    def price(x): return x
    ax.format_xdata = mdates.DateFormatter('%Y-%m')
    ax.format_ydata = price
    ax.grid(True)

    # rotates and right aligns the x labels, and moves the bottom of the
    # axes up to make room for them
    fig.autofmt_xdate()

    plt.show()

def allEmailAddresses():
    cur.execute("SELECT email FROM experimenter WHERE email LIKE '%@%';");
    emails = cur.fetchall()
    # Format as comma separated list for copy-paste
    commaSep = ''
    for email in emails:
        commaSep += email[0] + ', '

    print 'emails:', commaSep

#showAllExperimenters()
#countSignupsPerMonth()
#countSessionsPerUserPerMonth()
allEmailAddresses()

omero_stats.sh

#!/bin/bash

set -e
#set -u
#set -x

HOSTNAME="localhost"
DATABASE="omerodb"
USER="omerouser"

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 7 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 (now() - started) < '7 days'::interval;"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select count(distinct session.owner) from session, experimenter where session.owner = experimenter.id and (now() - started) < '7 days'::interval;"

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 (now() - started) < '30 days'::interval;"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select count(distinct session.owner) from session, experimenter where session.owner = experimenter.id and (now() - started) < '30 days'::interval;"

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 (now() - started) < '60 days'::interval;"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select count(distinct session.owner) from session, experimenter where session.owner = experimenter.id and (now() - started) < '60 days'::interval;"

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 (now() - started) < '90 days'::interval;"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select count(distinct session.owner) from session, experimenter where session.owner = experimenter.id and (now() - started) < '90 days'::interval;"

echo "# of logins per user over all time"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select owner, omename, count(session.id) from session, experimenter where session.owner = experimenter.id group by owner, omename order by owner;"

echo "# of logins per user over the past week"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select owner, omename, count(session.id) from session, experimenter where session.owner = experimenter.id and (now() - started) < '1 week'::interval group by owner, omename order by owner;"

echo "List of users with signup dates"
psql --pset pager=off -h $HOSTNAME -U $USER $DATABASE -c "select event.time,experimenter.omename from eventlog,event,experimenter where eventlog.entitytype='ome.model.meta.Experimenter' and eventlog.action='INSERT' and eventlog.event=event.id and eventlog.entityid=experimenter.id order by event.time;"