[SAK-33432] Lessons page statistics not being displayed in siteStats Created: 06-Oct-2017  Updated: 22-Jun-2018  Resolved: 03-Apr-2018

Status: Verified
Project: Sakai
Component/s: Sitestats
Affects Version/s: 11.4, 12.0, 19.0
Fix Version/s: 11.5 [Tentative], 12.1, 19.0

Type: Bug Priority: Blocker
Reporter: Kenneth Aragon Assignee: Jesus Maria Mendez
Resolution: Fixed Votes: 4
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File VerifiedSAK-33432.png     JPEG File a.jpg     JPEG File a_4.jpg     JPEG File after_fix.jpg     JPEG File before_fix.jpg     Text File image-2018-03-29-11-53-18-042.png    
Issue Links:
Depend
depends on SAK-38056 Many Lessons events are defined as ma... Verified
depends on SAK-37446 Add distinct events for CRUD on Lesso... Verified
Relate
relates to SAK-29709 Add SiteStats ribbon for Lessons tool... RESOLVED
is related to SAK-38093 Lessons event ref for event type 'les... CLOSED
is related to SAK-32104 Sitestats - Lessons: number of pages ... Verified
12 status: Resolved
11 status: Resolved
Conversion Script Required:
Yes
Test Plan:

Please add a Test Plan here.


 Description   

The lesson page statistics is not being displayed in the Site Statistic tool.  Steps to replicate :

 

  1. Login as Instructor
  2. Access a site and add the Lessons and Statistics tools
  3. Access the Lessons tool and add some content (Create a couple of pages add some text)
  4. Login to the site a student
  5. Access the Lessons tool and click through the pages to create a read event
  6. As the Instructor access the site and click on the Statistics pages

Expected result : 

The Lessons statistics should reflect the correct number of pages and pages read

 

Actual Result:

The Lessons statistics does not.  I still see 0 pages and 0 pages read.

 

Tested on 11.x, 12.x, and Trunk nightly servers.



 Comments   
Comment by Jonas Eriksson (inactive) [ 15-Nov-2017 ]

We have a similar problem in our production environment, 11.4

Comment by Matthew Jones [ 27-Mar-2018 ]

I believe this is a duplicate of SAK-32104, closing as duplicate

Comment by Miguel Pellicer [ 28-Mar-2018 ]

Is not duplicated Matthew Jones

There is a big problem with this widget, the read events are never populated to the SST tables.

Example:

select count ( * ) from sakai.sakai_event where event = "lessonbuilder.read"; -> 30K rows

SELECT count ( * ) FROM sakai.sst_lessonbuilder where page_action = "read"; -> 0 rows

I've cleaned up the SST tables and ran the job again from eventId = -1 and the SST table is not populated.

The problem is here, it expects something like "/lessonbuilder/page/54" but receives "/lessonbuilder/item/54".

https://github.com/sakaiproject/sakai/blob/master/sitestats/sitestats-impl/src/java/org/sakaiproject/sitestats/impl/StatsUpdateManagerImpl.java#L709

 

Comment by Miguel Pellicer [ 28-Mar-2018 ]

Looks like the problem was introduced by this JIRA:

https://jira.sakaiproject.org/browse/LSNBLDR-736

And this PR:

https://github.com/sakaiproject/sakai/pull/3425

I'm investigating the problem a bit more but seems related.

 

Comment by Miguel Pellicer [ 28-Mar-2018 ]

Promoted to blocker

Comment by Miguel Pellicer [ 28-Mar-2018 ]

At this points, there is no way to differentiate between Items and Pages, so the stats can't be recalculated correctly (The events of SAKAI_EVENT are wrong). This is the best I got, at least it shows something rather than 0  - 0%, but the values of the "Pages Read" are not correct, the others are:

If someone else would like to take a look, the changes are in this branch, 0% reliable:

https://github.com/mpellicer/sakai-apereo/commit/16d62138f500b57a673d8932844bea7f17cc16ba

 

I suggest disabling the Lessons Widget until displays correct data.

 

Comment by Matthew Jones [ 28-Mar-2018 ]

Yeah, I agree, I wouldn't block the release for this. But I would either disable the widget or at least this part of the widget.

Based on what you found on LSNBLDR-736 it seems like there should be /item and /page and there is no support in Sitestats for the /item feature.

From what I can see there still are events for "/lessonbuilder/page" that are generated but perhaps /page/ and /item/ are tracking different things. (It looks like /page/ actually tracks student pages and /item/ tracks everything else)

So maybe we should just switch to /item/ and perhaps have a feature request for a column to track student page reads? (Which would be /page/) ?

Any thoughts Brian Jones since you worked on LSNBLDR-736.

Comment by Brian Jones [ 28-Mar-2018 ]

My opinion is that LSNBLDR-736 fixed a legitimate bug, where lesson's events were sometimes ambiguous because you can have a page and an item with the same ID. Previous to that fix, the code would always build the event ref with the "/page/" identifier for both SimplePage and SimplePageItem objects, so when resolving the event to an object you would never really know if it's referring to an Item or a Page.

I think the solution to this ticket is to fix the fix the Lessons Widget in Statistics so that it's querying for the proper data.

Comment by Miguel Pellicer [ 29-Mar-2018 ]

There's more, this JIRA broke Sitestats too, there will be different fixes for Master and 12.x:

https://jira.sakaiproject.org/browse/LSNBLDR-786 changed the events but there is no conversion script from 11.x to 12.x, sounds bad for me

I've changed some things and some elements are working now, but some not, this probably may require more and more work:

I suggest disabling this panel because the results are totally wrong.

Comment by Miguel Pellicer [ 29-Mar-2018 ]

I've added a PR that resolves the issues with the items and pages, differentiates between page and item. It also adds a new feature that is really useful, the page visited by the user, now it looks much better:

This will require a conversion script to fix the old events and recover the statistics, I've tested this and worked for me:

-- SAK-33432 - Restore all the past events of LB items that are LB pages
CREATE TABLE EVENTS_TEMP (EVENT_ID INTEGER);

INSERT INTO EVENTS_TEMP (EVENT_ID)
SELECT SE.EVENT_ID FROM SAKAI_EVENT SE, LESSON_BUILDER_ITEMS LBI WHERE SE.REF = CONCAT('/lessonbuilder/item/', LBI.ID) AND LBI.SAKAIID <> '';

UPDATE SAKAI_EVENT SET EVENT = REPLACE(EVENT, 'item', 'page'), REF = REPLACE(REF, 'item', 'page') WHERE EVENT_ID IN (SELECT EVENT_ID FROM EVENTS_TEMP);

DROP TABLE EVENTS_TEMP;
Comment by Brian Jones [ 29-Mar-2018 ]

I'm wondering about that conversion script. You're changing all 'item' refs to 'page' refs, but I don't think you can make this assumption. Given the problem described in LSNBLDR-736, these historical events are ambiguous; they could have been referring to Page or Item objects. I think there's no possible way to determine retroactively if these events actually refer to Page or Item object.

Comment by Miguel Pellicer [ 29-Mar-2018 ]

I think I can distinguish between items and pages in the query with this condition "LBI.SAKAIID <> '' AND LBI.PAGEID = 0", seems working for me.

Currently, I'm executing the entire process again using an 11.x local instance with production data, I'll report the results after my testing.

Comment by Miguel Pellicer [ 29-Mar-2018 ]

Here are my results, the default behavior of the current 11.x will show this useless stats:

I've applied the 11.x FIX with all the changes, executed the migration scripts and regenerated the Sitestats tables, this is what I got:

After I've migrated my instance to Master, executing the migration scripts, and got exactly the same results.

Comment by Diego del Blanco Orobitg [ 30-Mar-2018 ]

That's amazing Miguel, this is absolutely a blocker IMO and needs to be urgently solved. Sakai lack of this kind of statistics and this is really important for a lot of institutions. Thanks for the effort!

Comment by Jesus Maria Mendez [ 05-Apr-2018 ]

Verified on: http://trunk-mysql.nightly.sakaiproject.org

https://jira.sakaiproject.org/secure/attachment/49687/VerifiedSAK-33432.png

Comment by Neal Caidin [ 05-Apr-2018 ]

If this JIRA is fixed and verified why is there now a new open pull request?

 

Comment by Miguel Pellicer [ 05-Apr-2018 ]

Conversion scripts, they are optional but allow the institution to recover old statistics.

Comment by Johan Evelönn [ 12-Apr-2018 ]

Miguel Pellicer, we use 11.4, so we only need to run the scripts and then it should be working fine? Or is there other things needed to be done as well?

 

Comment by Miguel Pellicer [ 12-Apr-2018 ]

There are two things:

  1. Future events. Apply the software changes to make it work (Including this JIRA and https://jira.sakaiproject.org/browse/SAK-32104)
  2. Past events. Run the scripts (Only the part of 11.4 to 11.5)

The second point is not part of the community release yet, I think I'm the only one who tested them. Would you mind to test them in a dev environment and provide feedback?

Feedback about the entire JIRA will be appreciated too.

Thanks you Johan Evelönn

Comment by Matthew Jones [ 12-Apr-2018 ]

I pulled an import from LSNBLDR-790 and found the 2 conflict dependencies (which made sense to include) and merged this all to 11.x

Comment by Miguel Pellicer [ 13-Apr-2018 ]

Thanks Matthew Jones

 

Would you mind to merge this https://jira.sakaiproject.org/browse/SAK-32104 to 11.x as well?

Comment by Matthew Jones [ 13-Apr-2018 ]

Miguel Pellicer Yeah, that one needs to be marked as verified then I'll merge it.

Comment by Johan Evelönn [ 16-Apr-2018 ]

Thanks Miguel Pellicer for the information!

I have not been successful in running the script, since I get a timeout. This is probably because my sakai_event table contains to many rows. I changed the script a bit, but I still won't get it to work. It seems like we have a procedure where we move events from the table into another table from time to time, my guess is because it gets to big. So to solve this issue now for me I will probably have to move data from the table, but that kind of breaks the historic statistics, so I guess we will settle for the future statistics issue.

Generated at Sun Sep 15 05:43:41 CDT 2019 using Jira 8.0.3#800011-sha1:073e8b433c2c0e389c609c14a045ffa7abaca10d.