Sakai Stats

[root@sakai backup]$ cat sakaistat.sh
mysql -pSakaiMySQL sakai -t < /var/backup/sakaistat.sql > /var/backup/sakaistat.txt
mail -s "Sakai usage stats" a.kharuk@auckland.ac.nz < /var/backup/sakaistat.txt

[root@sakai backup]$ cat sakaistat.sql
select "Total Users" as Property, count(user_id) as Amount from SAKAI_USER
union
select "System Users" as Property, count(user_id) as Amount from SAKAI_USER where type='system'
union
select "Registered users with e-mails" as Property, count(user_id) as Amount from SAKAI_USER where email is not null and type='registered'
union
select "Registered users without e-mails" as Property, count(user_id) as Amount from SAKAI_USER where email is null and type='registered'
union
select "Guests with e-mails" as Property, count(user_id) as Amount from SAKAI_USER where email is not null and type='guest'
union
select "Guests without e-mails" as Property, count(user_id) as Amount from SAKAI_USER where email is null and type='guest'
union
select "--------------------------------" as Property, 0 as Amount
union
select "Total sites" as Property, count(site_id) as Amount from SAKAI_SITE
union
select "Total Projects" as Property, count(site_id) as Amount from SAKAI_SITE where type="project"
union
select "Total My Workspaces" as Property, count(site_id) as Amount from SAKAI_SITE where title="My Workspace";

select a.month, ifnull(a.registered_with," ") as reg_email, ifnull(b.registered_without," ") as reg_noemail, ifnull(c.guests," ") as guests, ifnull(d.projects," ") as projects, ifnull(e.workspaces," ") as workspaces from
(select concat(year(createdon), "-", lpad(month(createdon),2,"0")) as month, count(user_id) as registered_with
from SAKAI_USER where type="registered" and email is not null group by year(createdon),month(createdon)) as a
left join
(select concat(year(createdon), "-", lpad(month(createdon),2,"0")) as month, count(user_id) as registered_without
from SAKAI_USER where type="registered" and email is null group by year(createdon),month(createdon)) as b
on a.month=b.month left join
(select concat(year(createdon), "-", lpad(month(createdon),2,"0")) as month, count(user_id) as guests
from SAKAI_USER where type="guest" group by year(createdon),month(createdon)) as c
on a.month=c.month left join
(select concat(year(createdon), "-", lpad(month(createdon),2,"0")) as month, count(site_id) as projects
from SAKAI_SITE where type="project" group by year(createdon),month(createdon)) as d
on a.month=d.month left join
(select concat(year(createdon), "-", lpad(month(createdon),2,"0")) as month, count(site_id) as workspaces
from SAKAI_SITE where title="My workspace" group by year(createdon),month(createdon)) e
on a.month=e.month;

Script file is sakai.bestgrid.org:/var/backup/sakaistat.sql