[sakai2-tcc] Counting Tools

[Steve Swinsburg]

The query *does* work in MySQL. I have attached a screenshot. But its moot,
we both agree that title is redundant ;)

[image: Inline image 1]


On Sat, May 4, 2013 at 12:45 AM, David Adams <da1 at vt.edu> wrote:

> 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/20130504/f50e47ef/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 218792 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai2-tcc/attachments/20130504/f50e47ef/attachment-0001.png