Transcript files - Blackboard

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
Wider
General
Logins
Page hits
Deeper
Tool usage
Forum usage
Specific
Total content
Total content by type
User activity streams
Methods
• Basic numbers
Easier
• Thresholds
• Trends
• Patterns
• Activity streams
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
• users
– pk1
– user_id
– institution_roles_pk1
• institution_roles
–
–
–
–
pk1
role_name
description
role_id
• activity_accumulator
– event_type
(LOGIN_ATTEMPT,
PAGE_ACCESS)
– user_pk1
– timestamp
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
•
course_contents_files
–
•
links course_contents to files
files
– storage_type (CS or LOCAL)
–
crsmain_pk1
– file_name
–
content_handlers_pk1
– link_name
–
available_ind
– file_size
course_contents
–
every content item in a course
–
cnthndlr_handle
–
dtcreated
–
dtmodified
–
crsmain_pk1
–
available_ind
–
title
–
parent_pk1
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 NULL
ORDER 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
[email protected].
The subject of the email should be title of this
session:
Detecting Power Users Programmatically