[sakai2-tcc] Counting Tools

Fri May 3 12:48:09 PDT 2013

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
> 