[sakai2-tcc] Counting Tools

Neal Caidin nealcaidin at sakaifoundation.org
Fri May 3 05:11:50 PDT 2013 

This okay? - https://confluence.sakaiproject.org/display/TCC/CLE+Tool+Use+Query

Will link this from the survey.

-- Neal

P.S. Maybe there should be a TCC subpage of Surveys, underneath which we can gather results, etc. 

On May 2, 2013, at 5:43 PM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:

> Correct, since every tool stores content differently. Unless an event was fired on access of every tool that you could crossmatch and search for, but is accessing a tool 'using' a tool? Or is creating content in the tool using the tool?
> 
> You could use the capabilities in sitestats to create a report perhaps, since many people truncate their event tables, though that requires more effort on the institution's part. The SQL that John posted is an expanded version of the SQL I posted and should give a decent indication of tool availability.
> 
> cheers,
> Steve
> 
> 
> On 03/05/2013, at 1:20 AM, Neal Caidin <nealcaidin at sakaifoundation.org> wrote:
> 
>> Steve,
>> 
>> That would give you the count of tools in sites, but not necessarily if they have been used, correct?
>> 
>> Cheers,
>> Neal
>> 
>> On May 2, 2013, at 10:38 AM, Steve Swinsburg <steve.swinsburg at gmail.com> wrote:
>> 
>>> 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/b74e3c3e/attachment.html

More information about the sakai2-tcc mailing list