[sakai2-tcc] Counting Tools

Fri May 3 07:45:29 PDT 2013

I did check the query and it breaks. I'm not sure why you would need or
want Title, though, as it can vary and if it does you'll get confusing
data. Dropping "title," from the select clause works fine.

As for the format... I'll be the one running this query for VT, and the
first question that popped into my mind was, what format do you want it in?
I would probably have chosen CSV, but if it's unclear you'll get a mess of
different outputs. Maybe you'll get more response if you don't put any
restrictions on it. But if you say "in CSV if possible" that'll save you a
LOT of time. You'll still have to munge around with the files by hand (no
script is going to fix human-generated data errors), but I think if you at
least state a preference you'll have a lot less work to do, and for most
folks running the query, you'll save them time as well, if they don't have
to make a guess at what you want.

David Adams
Director, Systems Integration and Support
Virginia Tech Learning Technologies

On Fri, May 3, 2013 at 9:22 AM, Neal Caidin
<nealcaidin at sakaifoundation.org>wrote:

> I think to keep it simple, the TCC should provide the query with whatever
> formatting they want it to be in, so that the only instructions are:
>
> 1) Run the query
> 2) Send the data
>
> Keep it simple. If we have a consistent format of any kind it *should* be
> pretty dang easy to get the data into CSV or whatever, no? Excel has nice
> tools built-in for importing. And there are always quick Python or Perl
> scripts. Seem reasonable?  I think we should minimize the amount of work
> for the institutions to provide this data.
>
> I see what you mean about the query. It seems like it should either have
> the title in group by or it could have something like max(title) in the
> select. Did you try it and confirm it breaks? Maybe John can clarify ?
>
> Thanks,
> Neal
>
> On May 3, 2013, at 9:37 AM, David Adams <da1 at vt.edu> wrote:
>
> That query is broken. Either you need to add title to the group by
> statement or remove it from the select. Also, I'd suggest giving some
> indication of what format you'd like the data in. CSV? Copy and paste with
> terrible spacing and stray characters out of a terminal window?
>
> David Adams
> Director, Systems Integration and Support
> Virginia Tech Learning Technologies
>
>
> On Fri, May 3, 2013 at 7:11 AM, Neal Caidin <
> nealcaidin at sakaifoundation.org> wrote:
>
>>
>> This okay? -
>> https://confluence.sakaiproject.org/display/TCC/CLE+Tool+Use+Query
>>
>> Will link this from the survey.
>>
>> -- Neal
>>
>> P.S. Maybe there should be a TCC subpage of Surveys, underneath which we
>> can gather results, etc.
>>
>> On 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
>>
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai2-tcc/attachments/20130503/ecae2fe2/attachment-0001.html 