[sakai2-tcc] Counting Tools

Fri May 3 08:02:32 PDT 2013

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