[sakai2-tcc] Counting Tools

[John Bush]

right, I don't think there is really an easy way to count if they've
been used.  We have a similar query we run that adds in the number of
users the tool is available to, that is some indication of the tool
exposure.

select title, registration, count(distinct(t.site_id)) as num_sites,
count(distinct user_id) as users_available_to from sakai_site_tool t,
sakai_site_user u where t.site_id NOT LIKE '~%' AND t.site_id NOT LIKE
'!%' and t.site_id = u.site_id group by registration order by
users_available_to desc

See image for result example.


On Thu, May 2, 2013 at 8: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
>



-- 
John Bush
602-490-0470
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Screen Shot 2013-05-02 at 8.56.14 AM.png
Type: image/png
Size: 62073 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai2-tcc/attachments/20130502/45a7c314/attachment-0001.png