Student user accesses

[gwwitte]

Hello,

My institution is trying to identify students who have not accessed their online courses through Canvas in the first two weeks of class.

Looking at the data tables on the DAP ERD it seems like canvas.asset_user_accesses likely has the data I need, however when I query the canvas namespace I'm not finding that particular table. Does anyone have any insight? Are there other ways folks are using to identify students who have not accessed their courses?

I'm new canvas api so I'd appreciate even simple advice 🙂

Thanks

[mclark19]

@gwwitte  I don't think that table is exposed in CD2. You could try "last_activity_at" from the canvas.enrollments table to see if that is what you need.

[ESheta]

The last_activity_at field in the enrollments table is only useful if the query is run on the 14th day after the class starts. If the query is run a month later, this field becomes less helpful because it only stores the last access date for a student in a course.

For example, if a class starts on September 1st and the query is run on October 1st:

• Student A logs in twice. first on September 2nd and then on September 17th. (should not be listed in the report as he visited within the first two weeks)
• Student B logged in for the first time on September 17th. (should be listed in the report)

Since last_activity_at only keeps the most recent access date, both students would have the same last_activity_at value of September 17th, making it difficult to determine when each student first accessed the course.

[ESheta]

I’ve created a query that retrieves course ID, user ID, and the number of sessions (logins) for students within a specific time range. This helps track student engagement at the course, term, or subaccount level.

Key Features:

1. Counts distinct sessions per student within the given date range.
2. Can be adjusted to fetch data for a specific course, term, or subaccount.
3. If accessed_sessions is NULL, it means the student did not log in during the selected period.

SQL Query:

This query helps identify students who actively logged in during a specific period and those who never accessed the course in that timeframe.

Let me know if you have any suggestions or if you need adjustments for specific use cases! 

select distinct
c.sis_source_id,
p.sis_user_id ,
( select count(distinct session_id)
from web_logs wl
where wl.user_id=e.user_id and wl.course_id=e.course_id
and wl.timestamp between '2024-09-01' and '2024-09-15' ) as Access_Times
FROM enrollments e
inner join courses c on c.id=e.course_id
inner join pseudonyms p on p.user_id=e.user_id
WHERE c.sis_source_id = 'AAAA_202425' -- This filter can be replaced by a specific account or a specific term
and e.type = 'StudentEnrollment'