Part 2 – Tracking Blackboard Activity

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:

  1. event_type – High level classification of an access. Common event_types are:
    • LOGIN_ATTEMPT
    • LOGOUT
    • SESSION_INT
    • TAB_ACCESS
    • PAGE_ACCESS
    • MODULE_ACCESS
  2. 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’)
  3. 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’)
  4. 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’)
  5. 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’)
  6. 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
  7. 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’)
  8. 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”.)
  9. 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'

About blackboardcodemonkey
My name is Tony West. I am currently a Blackboard server administrator and software developer for a consortium of universities in the mid-west. I was employed by Blackboard from 2009 – 2010 out of Rochester, New York. I am passionate about application development and administration.

4 Responses to Part 2 – Tracking Blackboard Activity

  1. Anonymous says:

    Hi Tony,

    Have you heard of anyone who uses the Activity Accumulator to capture faculty “dwell time” i.e. how long a faculty memeber spent in a course room?

    • There is no way to do this with much accuracy. A user could in theory sit on a page for 30 minutes and nothing would be logged if they didn’t keep clicking around the course. You could get rough estimates though by taking the difference in timestamps of accesses to a course.

  2. Prashant says:

    Hi Tony,
    I am trying to create a report of the ACTIVITY_ACCUMULATOR table to identify measures like dwell time, number of posting for a discussion post. My concern is that CONTENT_PK1 column does not always have values that point to the COURSE_CONTENTS table. Do you know how CONTENT_PK1 column is populated? Do you have any queries for such reporting for reference?

    Thanks
    Prashant

    • My apologies for not responding for such a long time. I’ve haven’t posted in a year or two. I am just getting back into things. As to your question about tracking access to activity it seems that you’ve noticed that the content_pk1 is often NULL. Content_pk1 should populate when submitting an assignment, a test, or plain-jane content (e.g. when a user clicks on a document link.) Now assignments are pretty straightforward, the course_contents table should have everything you need. But in the case of tests there is another linkage. Test data is stored in the qti_asi_data table. Therefore to determine which test a user accesses you must join Activity_Accumulator->Course_Contents->Gradebook_Main->Qti_Asi_Data. Here is a query that grabs the top 1000 test accesses:

      select top 1000 u.user_id, cm.course_id, qad.title, aa.timestamp from activity_accumulator aa
      join gradebook_main gm on gm.course_contents_pk1=aa.content_pk1
      join qti_asi_data qad on qad.pk1=gm.qti_asi_data_pk1
      join users u on u.pk1 = aa.user_pk1
      join course_main cm on cm.pk1 = aa.course_pk1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: