At-risk students in Brightspace: who has not opened a course in 5 days

Choose where to run it

• In Rowseta: open Reports > New report and paste the query into the SQL editor on the Query tab. Sync the data sets listed below first.
• In your own setup: load your Data Hub extracts into your own SQL tool, one table per data set, named without spaces (User Enrollments becomes UserEnrollments), after applying differentials to the fulls. Another SQL tool may need small changes to these queries.

A free Rowseta Desktop app is coming soon.

This query needs User Enrollments, Role Details, Course Access, Organizational Units and Users. The reference links explain the columns and keys in each data set.

Run the inactive students query

Run the complete query. It includes people whose enrollment role passes the learner rule below, paired with their enrolled course. Someone can appear for one course while being absent from the list for another.

The 5-day cutoff is measured from the newest access day anywhere in the loaded Course Access data, rather than from today. That makes the result a view of the extract you loaded. An old extract does not tell you whether someone opened a course this morning. The query uses access dates, so it compares whole days rather than hours.

People searching for students who have not logged in should distinguish a site login from course access: this query measures course access only. A blank last-access value also qualifies. It means the loaded Course Access data has no matching access day for that learner in that course. It does not establish why a person has not visited, or prove that they have never visited outside the data you loaded.

-- Change the inactive day count below.
WITH Settings AS (
    SELECT 5 AS InactiveDays
), learner_roles AS (
    SELECT DISTINCT RoleId
    FROM RoleDetails
    WHERE RoleId IS NOT NULL
      AND NULLIF(trim(CAST(DeletedBy AS VARCHAR)), '') IS NULL
      AND COALESCE(lower(trim(CAST(ShowInGrades AS VARCHAR))) IN ('true', '1'), false)
), Learners AS (
    SELECT DISTINCT UserEnrollments.OrgUnitId, UserEnrollments.UserId
    FROM UserEnrollments
    JOIN learner_roles ON learner_roles.RoleId = UserEnrollments.RoleId
), AccessDays AS (
    SELECT OrgUnitId, UserId, MAX(CAST(DayAccessed AS DATE)) AS LastAccess
    FROM CourseAccess
    GROUP BY OrgUnitId, UserId
), ExtractDay AS (
    SELECT MAX(CAST(DayAccessed AS DATE)) AS AsOfDay FROM CourseAccess
)
SELECT Courses.OrgUnitId AS CourseId, Courses.Name AS CourseName, Learners.UserId,
       Users.FirstName, Users.LastName, AccessDays.LastAccess
FROM Learners
JOIN OrganizationalUnits AS Courses ON Courses.OrgUnitId = Learners.OrgUnitId
JOIN Users ON Users.UserId = Learners.UserId
LEFT JOIN AccessDays ON AccessDays.OrgUnitId = Learners.OrgUnitId
                   AND AccessDays.UserId = Learners.UserId
CROSS JOIN ExtractDay
CROSS JOIN Settings
WHERE Courses.Type = 'Course Offering'
  AND COALESCE(TRY_CAST(Courses.IsDeleted AS BOOLEAN), false) = false
  AND Users.DateDeleted IS NULL
  AND ExtractDay.AsOfDay IS NOT NULL
  AND (TRY_CAST(Courses.StartDate AS DATE) IS NULL OR TRY_CAST(Courses.StartDate AS DATE) <= ExtractDay.AsOfDay)
  AND (TRY_CAST(Courses.EndDate AS DATE) IS NULL OR TRY_CAST(Courses.EndDate AS DATE) >= ExtractDay.AsOfDay)
  AND (AccessDays.LastAccess IS NULL OR AccessDays.LastAccess < ExtractDay.AsOfDay - Settings.InactiveDays)
ORDER BY Courses.OrgUnitId, Learners.UserId;

A role counts as a learner role when Role Details has ShowInGrades true and DeletedBy empty. Each RoleId is judged on its own and role names are never read. Preview and testing roles that show in grades pass this rule too, so to use your own roles, replace the body of learner_roles with your role IDs: SELECT UNNEST([103, 121]) AS RoleId.

Only courses running on the newest loaded access day qualify; missing start or end dates are allowed. If Course Access is empty, there is no comparison day and the query returns no rows.

Change 5 on line 3, the labelled InactiveDays setting, to use a different window. The comparison is strictly earlier than that cutoff, so a last access exactly on the cutoff day is not included.

Read the inactivity list and follow up

Fictional sample data: the first eight rows. Your own data will give different names and values.

Read each row as one learner and course to review, with the last recorded access day when present. The null values shown here mean there is no matching access date in the loaded data; check the course context before deciding whom to contact. CourseId and UserId tell apart rows with the same names. Inactivity does not establish risk or explain a student's situation. This is a follow-up list, never a verdict.