Online learning teams and department chairs want to see, across every course, whether each instructor visited, posted in discussions and left assignment feedback during a period, without opening each course. These counts show recorded activity, not teaching quality. Zero means no recorded activity of that kind in the loaded data.
Where the data comes from: Brightspace Data Sets, the CSV extracts Brightspace publishes in Data Hub. This is not a built-in Brightspace report or screen. Rowseta syncs these data sets for you, or your administrator can download the extracts for your own SQL tool.
Run this query
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.
Load User Enrollments, Role Details, Course Access, Discussion Posts, Assignment Submissions, Users and Organizational Units.
Run the instructor activity query
Change the first day on line 2 and last day on line 3. Both dates are included. Edit the role names on line 7 to add a teaching assistant, for example ('Instructor', 'Teaching Assistant'), using the exact names in Role Details. Two RoleIds with the same name both count.
WITH Settings AS (
SELECT DATE '2025-09-01' AS FirstDay,
DATE '2025-12-31' AS LastDay
), instructor_roles AS (
SELECT DISTINCT RoleId
FROM RoleDetails
WHERE RoleName IN ('Instructor')
), People AS (
SELECT DISTINCT UserId, FirstName, LastName FROM Users WHERE DateDeleted IS NULL
), Courses AS (
SELECT DISTINCT OrgUnitId, Name FROM OrganizationalUnits WHERE Type = 'Course Offering'
), Enrollments AS (
SELECT DISTINCT UserId, OrgUnitId, RoleId FROM UserEnrollments
), Instructors AS (
SELECT DISTINCT E.UserId, E.OrgUnitId FROM Enrollments E
JOIN instructor_roles ON instructor_roles.RoleId = E.RoleId
JOIN People ON People.UserId = E.UserId
JOIN Courses ON Courses.OrgUnitId = E.OrgUnitId
), Visits AS (
SELECT DISTINCT UserId, OrgUnitId, CAST(DayAccessed AS DATE) AS VisitDay FROM CourseAccess
), CourseVisits AS (
SELECT UserId, OrgUnitId, COUNT(DISTINCT VisitDay) AS DaysVisited, MAX(VisitDay) AS LastVisit
FROM Visits CROSS JOIN Settings
WHERE VisitDay BETWEEN Settings.FirstDay AND Settings.LastDay
GROUP BY UserId, OrgUnitId
), Posts AS (
SELECT DISTINCT PostId, UserId, OrgUnitId, DatePosted FROM DiscussionPosts
WHERE COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
), PostCounts AS (
SELECT UserId, OrgUnitId, COUNT(*) AS DiscussionPosts FROM Posts CROSS JOIN Settings
WHERE CAST(DatePosted AS DATE) BETWEEN Settings.FirstDay AND Settings.LastDay
GROUP BY UserId, OrgUnitId
), Submissions AS (
SELECT DISTINCT DropboxId, SubmitterId, SubmitterType, OrgUnitId, FeedbackUserId, FeedbackLastModified
FROM AssignmentSubmissions
WHERE COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
), FeedbackCounts AS (
SELECT FeedbackUserId AS UserId, OrgUnitId, COUNT(*) AS FeedbackGiven
FROM Submissions CROSS JOIN Settings
WHERE CAST(FeedbackLastModified AS DATE) BETWEEN Settings.FirstDay AND Settings.LastDay
GROUP BY FeedbackUserId, OrgUnitId
)
SELECT I.UserId, People.FirstName, People.LastName, I.OrgUnitId, Courses.Name AS CourseName,
COALESCE(CourseVisits.DaysVisited, 0) AS DaysVisited, CourseVisits.LastVisit,
COALESCE(PostCounts.DiscussionPosts, 0) AS DiscussionPosts,
COALESCE(FeedbackCounts.FeedbackGiven, 0) AS FeedbackGiven
FROM Instructors I
JOIN People ON People.UserId = I.UserId
JOIN Courses ON Courses.OrgUnitId = I.OrgUnitId
LEFT JOIN CourseVisits ON CourseVisits.UserId = I.UserId AND CourseVisits.OrgUnitId = I.OrgUnitId
LEFT JOIN PostCounts ON PostCounts.UserId = I.UserId AND PostCounts.OrgUnitId = I.OrgUnitId
LEFT JOIN FeedbackCounts ON FeedbackCounts.UserId = I.UserId AND FeedbackCounts.OrgUnitId = I.OrgUnitId
ORDER BY I.UserId, I.OrgUnitId;Course Access records browser visits as local days, not times. It counts course visits, not logins; use the login history guide for logins. Other dates are UTC. Quiz grading and grade book edits are not counted.
Read the activity per instructor and course
Fictional sample data: all 6 rows.
| UserId | FirstName | LastName | OrgUnitId | CourseName | DaysVisited | LastVisit | DiscussionPosts | FeedbackGiven |
|---|---|---|---|---|---|---|---|---|
| 1 | Fictional | Same person | 100 | Same course | 3 | 2025-12-31 | 3 | 3 |
| 1 | Fictional | Same person | 200 | Same course | 1 | 2025-10-02 | 2 | 2 |
| 2 | Fictional | Same person | 100 | Same course | 1 | 2025-10-03 | 3 | 3 |
| 2 | Fictional | Same person | 200 | Same course | 1 | 2025-10-04 | 4 | 4 |
| 3 | Fictional | Person 3 | 100 | Same course | 0 | null | 0 | 0 |
| 3 | Fictional | Person 3 | 200 | Same course | 0 | null | 0 | 0 |
DaysVisited counts distinct days and LastVisit is the latest in the range. DiscussionPosts excludes deleted posts. FeedbackGiven counts loaded Assignment Submissions rows whose stored FeedbackUserId is the instructor and whose FeedbackLastModified falls in the range, excluding deleted rows. One row is one submitter's folder, not a count of comments.
The query removes duplicate source rows before counting. Matching on both UserId and OrgUnitId keeps activity in its course even when instructors or courses share names. Instructors without activity remain in the list with zero counts and a null last visit. Check the loaded extracts before interpreting a zero as a reason to follow up.
When it goes wrong
| What you see | What to do |
|---|---|
| No instructors appear | Check the exact role names in Role Details and edit the instructor role names list. A Rowseta session limited to one course cannot read Role Details, so sign in directly. |
| An activity count is zero | Check the date range and whether the relevant data set is loaded. |
| A course is missing | Only Course Offering enrollments appear, and users with DateDeleted set are left out. |