Detecting Power Users Programmatically

George Kroner

Innovative Insights

Agenda

• What is going on in there?

• Why would I want to know?

• Models and methods to consider and measurement metrics

• Get to know the database

• What tools exist to assist?

What is going on in there?

• Did you know that…

– Blackboard’s database is now open and documented for certain uses?

– Blackboard records (nearly) every click made in the system in an audit log?

• If you’ve turned this off, you likely still have HTTP logs that you can parse through

– Years of system usage data are likely present in your Blackboard system?

– The data locked inside is becoming increasingly relevant across a variety of applications?

Why would I want to know?

• Data to reinforce (or contradict) user feedback

• Find power users who can be advocates

• Find users familiar with certain toolsets

• Evaluating how the system meets your users’ needs

• …

Models, methods, and measurement metrics

• Who logs in the most

• Who access the most pages

• Who has the most students

• Who teaches the most courses

• Who adds or accesses the most content

• Who uses the most available tools

• Who interacts the most with others

General

Specific

Wider Deeper

LoginsPage hits

Total contentTotal content by typeUser activity streams

Tool usage

Forum usage

Methods

• Basic numbers

• Thresholds

• Trends

• Patterns

• Activity streams

Easier

More difficult, but more interesting

Database tables

• Logins

• Content

• Connections (discussion forums, enrollments)

• Tools

• Page hits

• bb_bb60 vs BBLEARN

• _stats (eg: bb_bb60_stats) is populated when you run PurgeAccumulator.sh/.bat

• Activity_accumulator_queue (flushed every 10 mins)

Logins & Page Hits

• activity_accumulator– event_type

(LOGIN_ATTEMPT, PAGE_ACCESS)

– user_pk1– timestamp

• users– pk1– user_id– institution_roles_pk1

• institution_roles– pk1– role_name– description– role_id

Logins & Page Hits (examples)

• Obtain total logins on a given day

– select count(*) from activity_accumulator where event_type = 'LOGIN_ATTEMPT' and timestamp between convert(datetime, '2010-07-11', 120) and convert(datetime, '2010-07-11', 120)

– to_date('2010-07-11','YYYY-MM-DD HH24:MI:SS') and to_date('2010-07-11','YYYY-MM-DD HH24:MI:SS')

• Obtain total # users who logged in today

– select count(*) from users where last_login_date between convert(datetime, '2010-07-10', 120) and convert(datetime, '2010-07-11', 120)

• Obtain total page hits on a given day

– select count(*) from activity_accumulator where event_type = 'PAGE_ACCESS' and timestamp between convert(datetime, '2010-07-11', 120) and convert(datetime, '2010-07-11', 120)

Content

• content_handlers

– handle

– Name

• course_content_handlers

– crsmain_pk1

– content_handlers_pk1

– available_ind

• course_contents

– every content item in a course

– cnthndlr_handle

– dtcreated

– dtmodified

– crsmain_pk1

– available_ind

– title

– parent_pk1

• course_contents_files

– links course_contents to files

• files

– storage_type (CS or LOCAL)

– file_name

– link_name

– file_size

Content (example)

• Obtain total content count by type

– select count (*) from course_contents where cnthndlr_handle = 'resource/x-bb-document'

• Obtain content added in the past day

– select count(*) from course_contents where dtcreated between convert(datetime, '2010-07-11', 120) and convert(datetime, '2010-07-11', 120)

Connections (discussion forums)

• Conference_main

– Forum_main (course-level, group-level)• Msg_main (parent-child relationship for replies)

• Example:

– Obtain total messages• SELECT count(*) from msg_main

Connections (enrollments)

• users

• courses

• course_users

– Links pk1 of users and courses tables

• course_roles

• Example

– Enabled instructor enrollments• SELECT COUNT(DISTINCT users.pk1) FROM course_users,

users WHERE course_users.row_status = 0 AND course_users.available_ind = 'Y' AND course_users.role IN ('B', 'G', 'P', 'T') AND users.pk1 = course_users.users_pk1 AND users.row_status = 0 AND users.available_ind = 'Y'

Tools

• navigation_item

– application (indicator of product subsystem)– internal_handle

• course_navigation_item

– internal_handle– enabled_ind

• application

– plugins_pk1 indicates a Building Block

• Blogs, journals, groups

– Others have relevant data beyond on/off – but in their own tables

Activity Streams

• Investigate individual user behavior for the duration of a session

• Investigate course behavior/activity on a given day

Activity Streams (example)

• User streams

– SELECT pk1, event_type, user_pk1, course_pk1, timestamp, data FROM activity_accumulator

WHERE user_pk1 IS NOT NULLORDER BY user_pk1, timestamp

• Course streams

– SELECT pk1, event_type, user_pk1, course_pk1, timestamp, data FROM activity_accumulator WHERE course_pk1 IS NOT NULL ORDER BY course_pk1, timestamp

Tools to help visualize (picking the right one)

• SQL Server Management Studio

• Aqua Data Studio

• Gephi (based on NetBeans)

• Talend Open Profiler

• Jaspersoft

• Blackboard-specific development

SQL Server Management Studio

Aqua Data Studio

Gephi

Blackboard Social Network Analysis Tool (user-facing!)

Talend Open Profiler

Jaspersoft

Blackboard-specific: ASTRO

Blackboard-specific: UMBC Reports

Blackboard-specific: EduTechnica.com

Please provide feedback for this session by emailing DevConFeedback@blackboard.com. 

The subject of the email should be title of this session:

Detecting Power Users Programmatically