[sakai2-tcc] Counting Tools

Neal Caidin nealcaidin at sakaifoundation.org
Fri May 3 10:00:17 PDT 2013 

I think either query is better than no query. Nothing is going to be perfect. But now is not a bad time to capture data, at least for U.S. schools, since we would probably get a fair snapshot of tool use for Spring semester.

I lean towards the events query option because it will give us a better (not perfect) view of what is actually used, vs just what happens to be deployed in the sites. In my experience, most instructors do not change whatever tools are deployed by default in their sites, but may use only a few of them.  I don't think we would ask DBA's to restore archived (truncated) data, just run with whatever is in production at the time.  

Are there any performance risks with this query? Should we ask DBA's to run against a refreshed test instance instead, or off-hours?

Thanks,
Neal


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