[sakai2-tcc] Counting Tools

[Beth Kirschner]

Steve has a simple query for events, but nothing which will give us a straight count of tools. I think we don't need to parse out the Resources events, since it's a given that this is a core tool. The events counts would have to be parsed to provide counts by tools. I could probably write a quick perl script to do this if we think this would be useful.

- Beth

Begin forwarded message:

> From: Steve Lonn <slonn at umich.edu>
> Subject: Re: SQL Query for Popular Counts
> Date: May 1, 2013 1:37:06 PM EDT
> To: Beth Kirschner <bkirschn at umich.edu>
> 
> Unfortunately, I don't have a query that groups by tool (and remember there's some "noise" in resources - have to parse out Drop Box and attachments (if you want to count Announcements attachments, for example, as the Announcements tool and not Resources).
> 
> Here's the query for all events, aggregated by event type  (this example has a date modifier - you should be very clear what timeframe your group wants):
> 
> select count( sakai_event.event), sakai_event.event
> from sakai_event
> and event_date >= to_date('20130101 00:00','YYYYMMDD HH24:MI')
> and event_date < to_date('20130501 00:00','YYYYMMDD HH24:MI')
> group by sakai_event.event;
> 
> 
> To parse DROPBOX from RESOURCES, you need this kind of query:
> 
> select count (CASE WHEN EVENT in ('content.available', 'content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 14) = '/content/group' THEN 'RESOURCES COUNT' END) as "RESOURCES",
> count (CASE WHEN EVENT in ('content.available', 'content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 20) = '/content/attachment/' THEN 'ATTACHMENTS' END) as "ATTACHMENTS",
> count (CASE WHEN EVENT in ('content.available', 'content.delete', 'content.new', 'content.revise', 'content.read') and substr(REF, 1, 14) = '/content/user/' THEN 'DROPBOX' END) as "DROPBOX", event
> from sakai_event
> where event_date >= to_date('20130101 00:00','YYYYMMDD HH24:MI')
> and event_date < to_date('20130501 00:00','YYYYMMDD HH24:MI')
> and event in ('content.available', 'content.delete', 'content.new', 'content.revise', 'content.read')
> group by sakai_event.event;
> 
> FYI, here's the most recent list of sakai events by tool that I made -- this needs to be updated for 2.9 (volunteers?)
> https://confluence.sakaiproject.org/display/UDAT/Sakai+2.7.x+Event+Table+Descriptions
> 
> Steve
> 
> 
> 
> On Wed, May 1, 2013 at 10:56 AM, Beth Kirschner <bkirschn at umich.edu> wrote:
> Steve,
> 
> The Sakai TCC would like to ask the community to run a query to see which tools are the most popular tools - I thought you might have a query that figures out these counts? If you do, can you send it my way?
> 
> Thanks,
> - Beth
> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai2-tcc/attachments/20130502/d58e388d/attachment.html