I want to delete all courses that have not been accessed in 6 months.
How can I view a batch of courses based on last access date?
Thanks - Alice
Hi there, Alice Garrard!
I'm going to share this with both Canvas Admins and Canvas Developers to see if there's a creative way to accomplish this. Hopefully the extra sets of eyes will help provide you an answer!
I would use Canvas Data. It contains an enrollment_dim table that has a field called last_activity_at.
You're probably not using MySQL so this statement may need modified a little, but here's a little snippet to return those courses that have not been accessed in more than 6 months.
SELECT c.canvas_id, c.name, MAX(e.last_activity_at) AS last_activityFROM course_dim c JOIN enrollment_dim e ON (e.course_id = c.id)GROUP BY c.canvas_id , c.nameHAVING last_activity < NOW() - INTERVAL 6 MONTH;
You can pull in other values that you might want. You can also filter by the type of enrollment if you need to check access by just instructors or just students. For example, you could add a WHERE type='StudentEnrollment' before the GROUP BY statement to look at just students. You would also probably want to double check the courses against a list of "Never Delete" courses as a safety net.
If you don't use Canvas Data, you can use the API to query every enrollment in every course, which takes a lot of time. It would be faster to just check for instructor access, but it's a few seconds in Canvas Data (once it's set up) vs minutes to hours through the API depending on how many courses you have and who you want to check.
It would be nice if the enrollments portion of the Provisioning report from the Admin > Settings > Reports menu gave the information, but it doesn't contain the last_activity, just a bunch of other information needed to re-create an enrollment. There's also the promising "Last enrollment activity", but only gives the last access by the user, not the last activity by the enrollment, so it doesn't contain the course information that you would need.
I thought I would follow up. James has provided some great insights and a sound solution. Has this answered your original question and got you up and running?
Retrieving data ...