Part 2 – Tracking Blackboard Activity
December 28, 2010 4 Comments
This is Part 1 of 2 in this series:
Part 1 – Analyzing the Blackboard Access Logs
Part 2 – Analyzing the Activity Accumulator
PREFACE
When statistics tracking is turned on all users’ actions within the Blackboard application are recorded in the activity_accumulator table of Blackboard’s “core” and “stats” databases. Newer access information is stored in the core database (bb_bb60 or BBLEARN) and access information older than a threshold (default 180 days) resides in the stats database (bb_bb60_stats or BBLEARN_stats.) Each row in these activity_accumulator tables contains information regarding the user, course, group, tab, module, content, discussion board, and so forth involved in a specific access.
ANALYZING THE ACTIVITY_ACCUMULATOR
The following are the fields of the activity_accumulator tables:
- event_type – High level classification of an access. Common event_types are:
- LOGIN_ATTEMPT
- LOGOUT
- SESSION_INT
- TAB_ACCESS
- PAGE_ACCESS
- MODULE_ACCESS
- user_pk1 – The user’s primary key, which can be used to look up more detailed information about the user that performed the access in the users table (i.e.
SELECT * FROM users WHERE pk1=’SomePrimaryKey’) - course_pk1 – The course’s primary key (if the access was of a course), which can be used to look up more detailed information about the course that was accessed in the course_main table (i.e.
SELECT * FROM course_main WHERE pk1=’SomePrimaryKey’) - group_pk1 – The group’s primary key (if the access was made by a group), which can be used to look up more detailed information about the group that accessed the course in the groups table (i.e.
SELECT * FROM groups WHERE pk1=’SomePrimaryKey’) - forum_pk1 – The forum’s primary key (if the access was made to a forum), which can be used to look up more detailed information about the forum that was accessed in the forum_main table (i.e.
SELECT * FROM forum_main WHERE pk1=’SomePrimaryKey’) - internal_handle – Detailed classification of an access. An example of a few internal_handles that could be listed alongside an event_type of “COURSE_ACCESS”:
- control_panel
- cp_gradebook
- cp_gradebook2_modify_item
- cp_discussion_board
- cp_staff_information
- cp_announcements
- cp_design
- cp_groups
- cp_collaboration
- content
- check_grade
- grade_individual_attempt
- discussion_board
- discussion_board_entry
- db_grade_list
- course_tools_area
- send_email
- admin_course_list_users
- content_pk1 – The content item’s primary key (if the access was made to a content item), which can be used to look up more detailed information about the content item that was accessed in the course_contents table (i.e.
SELECT * FROM course_contents WHERE pk1=’SomePrimaryKey’) - data – Contains event specific information to provide greater detail of the event_type and internal_handle. For example:
- A row with an event_type of “LOGIN_ATTEMPT” will have an entry in the data field that is either “Login Succeeded” or “Login Failed.”
- A row with an event_type of “TAB_ACCESS” or “MODULE_ACCESS” will have an entry in the data field that is the primary key of the tab or module. The primary key can be then be used to look up detailed information in the tab or module tables.
- A row with an event_type of “PAGE_ACCESS” will have an entry in the data field identifying which page was accessed (i.e. “Create Link Tab” or “Manage Brands”.)
- timestamp – The date and time that the access occurred.
From knowledge of these fields and the relationship of the activity_accumulator table to others (i.e. users, course_main, tab, module, forum_main, and so on) a user can construct queries to analyze access of a specific type from a specific time by a specific user or group. If, for example, an administrator wants to retrieve all access by a user with a user name of ‘jsmith’ in a course with an id of ‘History101’ after October 22, 2010 they could use the following:
SELECT * FROM activity_accumulator as aa
INNER JOIN users u
ON aa.user_pk1 = u.pk1
INNER JOIN course_main cm
ON aa.course_pk1 = cm.pk1
WHERE u.user_id='jsmith' AND cm.course_id='History101' AND timestamp > '2010-10-22'
blackboardcodemonkey 