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'

Advertisements

Part 1 – 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

All users’ actions within the Blackboard application are recorded in Blackboard’s access logs. Each log entry contains information about the user, their environment, the nature of the activity, and the time of the activity. Prior to the release of Blackboard version 9.0 ( versions 6 through 8 ) the httpd logs located under blackboard\logs\httpd contained this information. With the release of Blackboard 9.0 the bb-access-log was introduced located under blackboard\logs\tomcat. In version 9.0 and later the httpd log still exists, but the bb-access-log is more useful to the administrator because it automatically cross-references the session id in the httpd logs and the modperl logs to identify the primary key of the user that performed a given access. In versions 6 through 8 this cross-referencing must be done manually.

 

ANALYZING THE BB-ACCESS-LOG

192.168.90.128 – _5_1 [03/Dec/2010:10:40:21 -0600] “GET /webapps/portal/frameset.jsp?tab_tab_group_id=_2_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3DCourse%26id%3D_2_1%26url%3D HTTP/1.1” 200 7620 “Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12” 132E22BA1D45421822B981B8326AC471 0.017 17 7620

– The IP address of the user that accessed the page. If in a load balanced environment, this will be the IP of a server and not the user’s PC.

 

192.168.90.128 – _5_1 [03/Dec/2010:10:40:21 -0600] “GET /webapps/portal/frameset.jsp?tab_tab_group_id=_2_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3DCourse%26id%3D_2_1%26url%3D HTTP/1.1” 200 7620 “Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12” 132E22BA1D45421822B981B8326AC471 0.017 17 7620

– The primary key (pk1) of the user that accessed the page. The primary key of the user can be utilized to determine the username and name of the user by searching the users table in the bb_bb60 or BBLEARN table. For example:

SELECT user_id, firstname, lastname FROM users

Please note that the httpd log does not list users’ primary keys. To determine the user with an httpd log, the session id must be cross-referenced with the modperl log.

 

192.168.90.128 – _5_1 [03/Dec/2010:10:40:21 -0600] “GET /webapps/portal/frameset.jsp?tab_tab_group_id=_2_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3DCourse%26id%3D_2_1%26url%3D HTTP/1.1” 200 7620 “Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12” 132E22BA1D45421822B981B8326AC471 0.017 17 7620

– The date, time, and time zone when that the page was accessed.

 

192.168.90.128 – _5_1 [03/Dec/2010:10:40:21 -0600] “GET /webapps/portal/frameset.jsp?tab_tab_group_id=_2_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3DCourse%26id%3D_2_1%26url%3D HTTP/1.1″ 200 7620 “Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12” 132E22BA1D45421822B981B8326AC471 0.017 17 7620

–  The cs-method specified whether the call was a GET or a POST.

 

192.168.90.128 – _5_1 [03/Dec/2010:10:40:21 -0600] “GET /webapps/portal/frameset.jsp?tab_tab_group_id=_2_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3DCourse%26id%3D_2_1%26url%3D HTTP/1.1” 200 7620 “Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12” 132E22BA1D45421822B981B8326AC471 0.017 17 7620

– The cs-uri-stem is usually the URL or portion of the URL that was accessed. This along with the user’s primary key is the most valuable information contained in a log entry. It is the HTTP call that was made as a result of a user’s access (i.e. clicking a tab or link.) From this example we can tell exactly what the user accessed. By examining “tab_tab_group_id=_2_1” we know that the user clicked on a tab. To determine which tab we can look up the row in the “tab_tab_group” table with a primary key of 2.

SELECT * FROM tab_tab_group WHERE pk1=’2’

The result:

tab_pk1                pk1                          position                  tab_group_pk1

2                              2                              0                              2

From here where know the primary key of the tab and its tab group, which we can in turn look up.

SELECT * FROM tab WHERE pk1=’2’

The “label” field from the returned row is “Courses.label” and tells us that the tab that was clicked was the Courses tab.

The cs-uri-stem will vary greater based on the type of access and may contain more or less detailed information than the above example. However the details of most accesses can be determined either by querying the Blackboard databases, or replicating the access and observing the respective cs-uri-stem for comparison.

 

192.168.90.128 – _5_1 [03/Dec/2010:10:40:21 -0600] “GET /webapps/portal/frameset.jsp?tab_tab_group_id=_2_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3DCourse%26id%3D_2_1%26url%3D HTTP/1.1” 200 7620Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12 132E22BA1D45421822B981B8326AC471 0.017 17 7620

–  The User Agent specifies information regarding the client application that is sending the logging message. This includes their operating system, browser and version, language and dialect, version of the .NET CLR, etc.

 

192.168.90.128 – _5_1 [03/Dec/2010:10:40:21 -0600] “GET /webapps/portal/frameset.jsp?tab_tab_group_id=_2_1&url=%2Fwebapps%2Fblackboard%2Fexecute%2Flauncher%3Ftype%3DCourse%26id%3D_2_1%26url%3D HTTP/1.1” 200 7620 “Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12” 132E22BA1D45421822B981B8326AC471 0.017 17 7620

– The session id of the user that accessed the log. If using a version earlier than 9.0, the session id can cross-referenced in the modperl log to acquire the user’s primary key.