[sakai2-tcc] Counting Tools
Steve Swinsburg
steve.swinsburg at gmail.com
Thu May 2 07:38:13 PDT 2013
Heres a query for both MySQL and Oracle to get the count of tools across the whole install:
SELECT
registration
, count(*) site_count
FROM sakai_site_tool
WHERE site_id NOT LIKE '~%'
AND site_id NOT LIKE '!%'
GROUP BY registration
ORDER BY site_count DESC
Gives results like:
sakai.resources 10
sakai.siteinfo 9
sakai.assignment.grades 8
sakai.forums 8
sakai.gradebook.tool 8
sakai.announcements 8
sakai.messages 8
sakai.schedule 8
sakai.syllabus 8
sakai.iframe.site 7
sakai.summary.calendar 6
sakai.synoptic.announcement 6
sakai.synoptic.messagecenter 6
sakai.metaobj 1
sakai.repository 1
sakai.sitestats 1
osp.presLayout 1
sakai.news 1
osp.presentation 1
nyu.libraries 1
osp.presTemplate 1
osp.glossary 1
osp.style 1
sakai.mailbox 1
cheers,
S
On 02/05/2013, at 11:51 PM, Neal Caidin <nealcaidin at sakaifoundation.org> wrote:
> TCC,
>
> FYI - this is related to TCC - CLECC meeting yesterday. I'm drafting up a Tools survey for TCC to review. We thought it would be a good idea to give institutions an option to run a query instead, and send us the data for review. Each participating institution has the option to fill out the survey, or run a query we provide and send us the data, or both.
>
> More notes on yesterdays TCC - CLECC coming soon.
>
> Cheers,
> Neal
>
>
> On May 2, 2013, at 9:37 AM, Beth Kirschner <bkirschn at umich.edu> wrote:
>
>> 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
>>>
>>>
>>
>> _______________________________________________
>> sakai2-tcc mailing list
>> sakai2-tcc at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai2-tcc
>
> _______________________________________________
> sakai2-tcc mailing list
> sakai2-tcc at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai2-tcc
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai2-tcc/attachments/20130503/ae941a7d/attachment-0001.html
More information about the sakai2-tcc
mailing list