[sakai2-tcc] Counting Tools

Beth Kirschner bkirschn at umich.edu
Fri May 3 14:10:46 PDT 2013 

Good catch - the 'where' must have been lost somewhere along the line. I've just verified the syntax on our local database and it works fine.

- Beth

On May 3, 2013, at 3:48 PM, Neal Caidin <nealcaidin at sakaifoundation.org> wrote:

> Whoops. This can't be right. There is no WHERE clause. 
> 
> Should it be :
> select count( sakai_event.event), sakai_event.event
> from sakai_event
> where 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;
> 
> 
> ?
> 
> On May 3, 2013, at 11:14 AM, Beth Kirschner <bkirschn at umich.edu> wrote:
> 
>> 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
>> 
>

More information about the sakai2-tcc mailing list