Query for recent suspensions

Query for recent suspensions

by Kimber Warden -
Number of replies: 4

I'm trying to write a query to find students who have been suspended from their courses within the last hour.

I'm using something similar to 

SELECT ue.userid
FROM prefix_user_enrolments ue
WHERE ue.status="1"
AND UNIX_TIMESTAMP() - ue.timemodified < 3600

To test it, I enter a course, go to Course Administration->Users->Enrolled Users and change a student's enrollment status from Active to Suspended in the "Enrollment Methods" column. Then I immediately run the report. I expect the suspended student to be returned by the query, however no records are returned.

Puzzled, I looked at the user_enrolments table and discovered that the "timemodified" field was not updated although I would have expected it to update to the time at which I suspended the student.

Shouldn't the field update? Or do I misunderstand the purpose of the "timemodified" field? If I misunderstand it, how should I modify query to get the records I want?

Average of ratings: Useful (1)
In reply to Kimber Warden

Re: Query for recent suspensions

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I think it's probably a 'feature'.  Myself, I would raise a tracker report (link to it here) about this and see what shakes out. 

I'm inclined to agree with you but there may have been some reason for not updating this field. It depends how you define 'modified' although my guess would be that it depends on the opinion of the developer who is working on that code at the time.  Or it just wasn't considered when the suspension feature was added. 

To help your immediate problem, is suspension logged? You could search for recent suspensions in the log table possibly. 

Average of ratings: -
In reply to Kimber Warden

Re: Query for recent suspensions

by Darko Miletić -

Updating user enrollment DOES NOT update timemodified field as you already discovered. What you can try using is log table since event is issued. Query would look like this:

   SELECT ue.userid
     FROM mdl_user_enrolments       ue
	 JOIN mdl_user                   u ON u.id = ue.userid  AND u.deleted = 0
	 JOIN mdl_enrol                  e ON e.id = ue.enrolid
	 JOIN mdl_course                 c ON c.id = e.courseid
LEFT JOIN mdl_logstore_standard_log  l ON l.eventname = '\\core\\event\\user_enrolment_updated' 
                                          AND
                                          l.objectid = ue.id
                                          AND
                                          l.relateduserid = ue.userid
    WHERE ue.status = 1 AND l.timecreated >= UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL 1 HOUR)




Average of ratings: Useful (3)