[sakai2-tcc] Counting Tools

Fri May 3 08:14:41 PDT 2013

The query I originally posted is an events query (I've updated the dates to give us 1.5 years worth of data):

select count( sakai_event.event), sakai_event.event
from sakai_event
and event_date >= to_date('20120101 00:00','YYYYMMDD HH24:MI')
and event_date < to_date('20140101 00:00','YYYYMMDD HH24:MI')
group by sakai_event.event;

We would need to parse the data once returned to aggregate tool events for each tool, but it wouldn't take much.

- Beth

On May 3, 2013, at 11:02 AM, Neal Caidin <nealcaidin at sakaifoundation.org> wrote:

> Does someone have an events query that the group can look at?
> 
> -- Neal
> 
> On May 3, 2013, at 10:57 AM, Aaron Zeckoski <azeckoski at unicon.net> wrote:
> 
>>> You cant look at events because almost everyone truncates their event log
>> The data being truncated is not a guarantee that it will not be
>> representative. Also, you seem to assume no one deletes sites AND the
>> query assumes deleted sites should be counted.
>> 
>> As for the accuracy of counting tools in sites. There is my previous
>> point about ignoring tools that are mostly used in workspaces. Also,
>> what about import processes where sites are created for instructors
>> but not used (and there are many of institutions doing this).
>> Institutions like that will simply skew the results to being completed
>> unreliable.
>> In the case of events, aside from the likely case that is it only a
>> partial snapshot, it is unlikely to be skewed or unrepresentative of
>> use in reality.
>> I think there are more negatives against counting tools in sites than
>> there are against counting events.
>> 
>> -AZ
>> 
>> 
>> On Fri, May 3, 2013 at 10:42 AM, Steve Swinsburg
>> <steve.swinsburg at gmail.com> wrote:
>>> I fixed the query. The query does run in MySQL but not Oracle, should be
>>> fine in both now. Title wasnt really required since it can be inferred, but
>>> also because it can be localised. Also fixed formatting.
>>> 
>>> You cant look at events because almost everyone truncates their event log.
>>> Though this would be the best way to do it. However we need this to be easy
>>> and not want to force people to restore old archives of the event log just
>>> to do it, because they won't.
>>> 
>>> I think you can apply some common sense to the results and skip Site Info ;)
>>> 
>>> cheers,
>>> Steve
>>> 
>>> 
>>> On Sat, May 4, 2013 at 12:29 AM, Aaron Zeckoski <azeckoski at unicon.net>
>>> wrote:
>>>> 
>>>> But having a tool in a site is not equivalent to it being used. Also,
>>>> this will ignore tools that are mostly used in workspaces
>>>> institutions.
>>>> I still think looking at events is a better measure.
>>>> No database query will be perfect of course, but it seems like we can
>>>> to better than just counting the tools in sites. Something event or
>>>> activity based is going to be more representative.
>>>> Are we really going to believe Site Info is the most popular tool
>>>> simply because it is in every site? (maybe it is)
>>>> 
>>>> -AZ
>>>> 
>>>> 
>>>> On Thu, 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
>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>> _______________________________________________
>>>>> sakai2-tcc mailing list
>>>>> sakai2-tcc at collab.sakaiproject.org
>>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai2-tcc
>>>>> 
>>>> 
>>>> 
>>>> 
>>>> --
>>>> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
>>>> _______________________________________________
>>>> sakai2-tcc mailing list
>>>> sakai2-tcc at collab.sakaiproject.org
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai2-tcc
>>> 
>>> 
>> 
>> 
>> 
>> -- 
>> Aaron Zeckoski - Software Architect - http://tinyurl.com/azprofile
>> _______________________________________________
>> 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