Blackboard 9.1SP4 (and later) and Basic LTI Support

One of the new features/enhancements in Bb 9.1 SP4 was the support for Basic Learning Tools Interoperability (LTI).

From the Release Notes:


The Basic Learning Tools Interoperability (LTI) is an IMS derivative of the full Learning Tools Interoperability specification that provides a simple integration for instructors to install external learning tools and applications anywhere within their courses. Users do not have to sign in to multiple tools. External content and tools are added to a course the same way a URL is added. The tool or content is launched by clicking the link.

Developers can add Basic Learning Tools Interoperability extensions to their Building Blocks, increasing their market value.



Experimenting with Basic LTI

Step 1. In order to experiment with Basic LTI, I needed to find a Basic LTI Consumer. I found one on the IMS Global site.

URL: http://www.imsglobal.org/developers/BLTI/lms.php

Step 2. As System Administrator, configure Blackboard's Basic LTI Tools Providers

Reference: http://library.blackboard.com/ref/df5b20ed-ce8d-4428-a595-a0091b23dda3/Content/_admin_app_system/admin_app_basic_lti_tool_providers.htm

Go to System Admin > Building Blocks > Basic LTI Tools Providers > Register Provider Domain

1. Provider Domain Status

Set the status of the provider domain. You can also provide a list of additional hostnames to share this configuration if needed.

Provider Domain: www.imsglobal.org

Provider Domain Status: [x] Approved

2. Default Configuration

Basic LTI Tool Providers can request configuration per link, or can provide key and shared secret information for site-wide configuration.

Default Configuration: [x] Set separately for each link

3. Institutional Policies

Choose whether you want to override the institution policies for this specific tool provider domain.

Send User Data: [x] Send user data over any connection (though you may want "Send user data only over SSL" instead)

User Fields to Send: I chose all three
[x] Role in course
[x] Name
[x] Email Address

Show User Acknowledgment Message: [x] Yes
Message Text: "You are about to let us send your Role in Course, Name and Email Address to imsglobal.org."

and Submit

Also, if you just upgraded from 9.1SP3 (or earlier), then you should make sure the Basic LTI tool is enabled as a Course Tool.

System Admin > Course Settings > Course Tools

Basic LTI: [x] On

Step 3. As Instructor, set up the link in a course

Go to a Content Area > Build Content > URL

1. URL Information

Name: http://www.imsglobal.org/developers/BLTI/lms.php
URL: http://www.imsglobal.org/developers/BLTI/lms.php

[x] This link is to a Tool Provider

selecting this pops up two more fields for the Key and Secret provided by the Basic LTI Consumer.

Key: 12345
Secret: secret

and submit.

Step 4. Launch the link

Try the link as a student.

Note that you get the Confirmation (every time):

and when you click on the Launch button, you should be taken to the Basic LTI Consumer URL

Q. Where's the Blackboard username?

A. Well, you don't seem to get it. What Blackboard is sending as 'user_id' is the User primary key identifier (e.g., _16476_1) , and not the friendlier username (e.g., test1_student).

I tried passing a template variable (e.g., @X@user.id@X@)

http://www.imsglobal.org/developers/BLTI/tool.php?userid=@X@user.id@X@

but it didn't come through.

Suggested enhancement: Let us decide what to send back as user_id amongst the myriad of possibilities (external person key, username, email, etc)

Update April 27, 2011 - I've been told there will be some changes coming in 9.1SP8 to address the user_id/username.

Google Analytics on all of Bb jsp vs just on login page

On another test Bb server (still at 9.1SP3), I put the google tracking code on our custom login page.

With that I could get the same level of visitor information as before: visitors, their browser information, their geolocation, etc.

And until the content tracking is more meaningful, maybe that's all you really want.



Update: April 25th

A funny thing happened when I started looking at the visitor analytics of our Bb 9.1SP3 test server's login page.  I was seeing odd referring sites.  On investigation, it turns out the external DNS assigned to our test server was also, at one point, used by another site.  So we were picking up those hits!

Google Analytics (9.0SP5, 9.1SP4)

Blackboard Learn 9.1SP4 added the ability to easily embed tracking javascript onto the jsp pages.

From the release notes:

Google Analytics™ is a free service offered by Google for mining web traffic data to a website or application. The process for setting up Google Analytics is the same for each Blackboard Learn codeline including 9.0 SP5 and 9.1 SP4 and beyond.

1. Register with Google Analytics (http://www.google.com/analytics/)

• Identify a test server outside your firewall and note its domain name
• Create a Google Analytics Account
• Register the domain name of your server through the Google Analytics setup pages
• Save the script provided by Google Analytics

2. Open sharedDir/web_analytics/googleAnalyticsSnippet.vm (For example c:/blackboard/content/web_analytics/googleAnalyticsSnippet.vm), paste your script, and save the file.

3. You can restart Blackboard Learn Services for the changes to take effect immediately or wait until the .vm file cache is refreshed, once every 12 hours.

For information on Secure and Non-Secure sites, visit
http://www.google.com/support/analytics/bin/answer.py?hl=en&answer=55483


How often does Google Analytics update your stats?

Google Analytics generally updates your reports every 24 hours. This means that it could take 24 hours for data to appear in your account after you have first installed the tracking code.

---

When you get into your google analytics account, you can see the list of sites (Website Profiles) you are getting stats on. Remember it'll take up to 24 hours (well, midnight) to start seeing stats.

View Report - you get to a dashboard view of pretty charts, maps, etc of the visits.

From there you can drill down into the various sub-areas: Visitors, Traffic Sources, Content, etc.

Visitors - How many people came to your site and how extensively did they interact with your content? This was probably the most interesting to me.

It is useful to see what kinds of browsers/operating systems are visiting your Bb site. Other things you can find out: Screen Colors, Screen Resolutions, Flash Versions, Java Support. All useful for folk who develop their own website content.

And you can see geographically where your visitors are coming from

My test server isn't available externally, so my map overlay doesn't show much. I'll try to find better results with an external Bb server.

Traffic Sources - This report provides an overview of the different kinds of sources that send traffic to your site.

It was useless with my test server since all the traffic was "direct" - visits from people who typed in the url directly.

Content - This report provides an overview of pageview volume and lists the pages (Top Content) that were most responsible for driving page views.

Content Overview screen

Top Content - guess which link has the most hits?

/webapps/portal/execute/topframe?tab_tab_group_id=_1_1&frameSize=LARGE

Everyone has to access the topframe, see My Institution (typically the first tab group).


Content by Title

Content by Title has limited usefulness in the context of Blackboard because most of the page titles aren't that specific (e.g., Courses, Home Page, Users, Blackboard Learn)

Deleted Discussions, Part III

Blackboard gave me some SQL to look at the XYTHOS bits.


USE CMS_DOC;
SELECT xu.FILE_ID, xu.FULL_PATH, xb.STORAGE_FILENAME
FROM XYF_URLS xu, XYF_FILES xf, XYF_FILE_VERSIONS xv, XYF_BLOBS xb
WHERE xu.FULL_PATH like '%FILENAME.JPG'
AND xu.FILE_ID = xf.FILE_ID
AND xf.FILE_ID = xv.FILE_ID
AND xf.LATEST_VERSION = xv.VERSION
AND xv.BLOB_ID = xb.BLOB_ID;


And you get something like

FILE_ID	FULL_PATH	STORAGE_FILENAME
18804	/internal/courses/.../FILENAME.JPG	/2011/2011-04/2011-04-13/6801/7901.6

and THAT's where your file is.

Deleted Discussions, Part II

Ok, what about those images? Are they left behind as detritus somewhere in the bowels of Bb's legacy file system?

Small scale testing on our exploratory server

1. create a post

2. embed an image
3. see what src url is generated


img src="@X@EmbeddedFile.location@X@s%27meeps%20with%20bunnies.jpg"


The @X@EmbeddedFile.location@X@ is a blackboard template variable that's dynamically expanded (http://www.edugarage.com/display/BBDN/Template+variables)

My first attempt was to see if I can get to it from a content item (inserting appropriate code via the VTBE source mode). That did not work.

My second attempt was to see if I can get to it from another discussion posting in the same course (inserting appropriate code via the VTBE source mode). That did not work.

Then I noticed, during expansion, that the path referenced the PK1 of the post itself.

https://BBSERVER/courses/1/COURSEID/db/_MSGPK1_1/embedded/s%27meeps%20with%20bunnies.jpg

My third attempt was to insert the fully expanded code in a new discussion posting, referencing the MSGPK1 of the original post.

Voila! That worked.

Now, the true test. What happens when the original posting is deleted?

The URL still worked!

Could it be cached at the tomcat level? Maybe, so restart the server.

It still worked!

I went looking in the file system for it.

blackboard\content\vi\bb_bb60\courses\1\COURSEID\db\_MSGPK1_1 was blank!! No hidden files, no embedded directory.

The image is obviously there, but where?!

Deleted discussions, possibility of retrieval?

I'm sure you've come across this ...

One of our faculty members inadvertently deleted a large number of student responses to a discussion board this weekend and is looking to try to recover these responses.

Is there any way of recovering this data?


Solution:

We restored a database backup taken the morning prior to the deletion. I was able to retrieve the raw post data with a query like http://iangoh99.blogspot.com/2011/04/find-out-which-messages-are-published.html

In this case, there were image attachments they wanted.

In some cases, the image src's pointed to external URLs. In other cases, we were seeing @X@EmbeddedFile.location@X@file_name_paths.

Brett Stephens' SQL

Brett Stephens

cmsAdministrator @ COM:

Here are some of my queries to add to the list: http://bsblackboard.blogspot.com/

Thanks Brett!

Chris Bray's SQL Queries

Chris Bray at University of Arkansas also has posted his collection of SQL queries at http://bbadmin.uark.edu/tag/sql/

Distinct Users/Sessions Per Hour

I was validating some information I found in BbStats (more on this building block later).

I wanted a break down by month/day/hour of the users/sessions. BbStats does this but you can't go backwards in time several months.

So say you want to find the usage between Sept 2010 and Dec 2010.

use bb_bb60_stats;
SELECT datepart(year,timestamp) as YEAR, DATEPART(month,timestamp) as MONTH, DATEPART(day,timestamp) AS DAY, DATEPART(hour,timestamp) AS HOUR, COUNT(DISTINCT user_pk1) AS DISTINCT_USERS, COUNT(DISTINCT session_id) as DISTINCT_SESSIONS
FROM [bb_bb60_stats].[dbo].[activity_accumulator]
where 
  user_pk1 > 4 and
  timestamp >= '2010-09-01 00:00:00' and
  timestamp < '2011-01-01 00:00:00'
group by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp)
order by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp) 


This would be the distinct users/sessions per hour.

Why "user_pk1 > 4"?  Turns out the first few user records come OOTB with Blackboard so I exclude them from activity.

Now, you can go down to the minute by extending this with DATEPART(minute, timestamp).  But that's a WHOLE lot of records.

Here's a variant to get down to the quarter hour (basically doing some math to convert minutes to the nearest 0, 15, 30, 45)

SELECT datepart(year,timestamp) as YEAR, DATEPART(month,timestamp) as MONTH, DATEPART(day,timestamp) AS DAY, DATEPART(hour,timestamp) AS HOUR, 15 * ROUND(DATEPART(minute,timestamp)/15,0) AS QHOUR, COUNT(DISTINCT user_pk1) AS DISTINCT_USERS, COUNT(DISTINCT session_id) as DISTINCT_SESSIONS
FROM [bb_bb60_stats].[dbo].[activity_accumulator]
where
  user_pk1 > 4 and
  timestamp >= '2010-09-01 00:00:00' and
  timestamp < '2011-01-01 00:00:00'
group by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp), 15 * ROUND(DATEPART(minute,timestamp)/15,0)
order by datepart(year,timestamp), DATEPART(month,timestamp), DATEPART(day,timestamp), DATEPART(hour,timestamp), 15 * ROUND(DATEPART(minute,timestamp)/15,0)


Results would look like:

YEAR MONTH DAY HOUR QHOUR DISTINCT_USERS DISTINCT_SESSIONS
2010 10 5 12 0 432 448
2010 10 5 12 15 407 427
2010 10 5 12 30 359 399
2010 10 5 12 45 380 406

Concurrent Usage

Here's something Blackboard had in its performance/maintenance (?) document for Concurrent Usage. 

To give you an idea where you fall in terms of concurrent usage check your current session count using the following query (in recent Bb versions the timestamp is only being updated every 15 minutes, so this is as good as you can hope to get for measuring recent activity).

use bb_bb60;

select count(*) from sessions where datediff(minute, timestamp, getdate())< 15

Find out which messages are published and which are draft in a courses's discussion forum (including its groups)

Whats New module: Discussion Board unread messages - counts Draft Posts (AS-139044)

There's a bug in 9.1 (at least up to SP3) where the Whats New module counts unread messages that are in DRAFT mode. This issue is targeted to be fixed in Release 9.1 Service Pack 4 as bug number AS-139044

Note: DRAFT posts have msg_main.lifecycle = 'DRAFT'

DECLARE @course_batch_uid NVARCHAR(256);
SET @course_batch_uid = 'ian001'
--
SELECT [msg_main].[pk1]
      ,[msg_main].[dtcreated]
      ,[msg_main].[dtmodified]
      ,[msg_main].[posted_date]
      ,[msg_main].[last_edit_date]
      ,[msg_main].[lifecycle]
      ,[msg_main].[text_format_type]
      ,[msg_main].[post_as_annon_ind]
      ,[msg_main].[cartrg_flag]
      ,[msg_main].[thread_locked]
      ,[msg_main].[hit_count]
      ,[msg_main].[subject]
      ,[msg_main].[posted_name]
      ,[msg_main].[linkrefid]
      ,[msg_main].[msg_text]
      ,[msg_main].[body_length]
      ,[msg_main].[users_pk1]
      ,[msg_main].[forummain_pk1]
      ,[msg_main].[msgmain_pk1]
 FROM [bb_bb60].[dbo].[msg_main] as [msg_main]
 LEFT JOIN [bb_bb60].[dbo].[forum_main] as [forum_main]
 ON [msg_main].[forummain_pk1] = [forum_main].[pk1]
 LEFT JOIN [bb_bb60].[dbo].[conference_main] as [conference_main]
 ON [forum_main].[confmain_pk1] = [conference_main].[pk1]
 WHERE [conference_main].[conference_owner_pk1] in
 (
   -- subselect list of conference owner pk1's related to the course or groups within the course
   SELECT [conference_owner].[pk1]
   FROM [bb_bb60].[dbo].[conference_owner] as [conference_owner]
   LEFT JOIN [bb_bb60].[dbo].[groups] as [groups]
   ON [conference_owner].[owner_table]='GROUPS' AND [conference_owner].[owner_pk1]=[groups].[pk1]
   LEFT JOIN [bb_bb60].[dbo].[course_main] as [course_main]
   ON (
     ([conference_owner].[owner_pk1] = [course_main].[pk1] and [conference_owner].[owner_table] = 'COURSE_MAIN')
       OR [groups].[crsmain_pk1] = [course_main].[pk1]
      )
   WHERE [course_main].[batch_uid] = @course_batch_uid
)

Auditing users

Auditing Users (from CBergeron AT LAKELANDCC.EDU)

This query will show a user's activity in courses. Removing the 'and course_pk1 like '%'' clause to also see their activity in the admin GUI.

select * from activity_accumulator where
user_pk1 in
(select pk1 from users where user_id like ('YOUR_USERID'))
and timestamp > '2010-09-20'
and timestamp < '2010-09-30'
and course_pk1 like '%'
order by timestamp

Simple Blackboard Usage SQL Reports

I started to post some of the SQL Server queries that I've found handy (or collected) from others .

Caveat: I'm not a SQL DBA so my code is prob. inefficient, ugly, etc.

Applies to: Blackboard 9.1 (SP3) with MS SQL Server.  

What is the pk1 (internal id) for a user with user_id x?

SELECT [pk1]

FROM [bb_bb60].[dbo].[users]

where user_id='x'

tip: in the tomcat/bb-access-log the users pk1 shows up after the ipaddress - as _userpk1_1

e.g., the user with pk1 47575 will show up in the bb-access-log as...

aa.bb.cc.dd - _47575_1 [DD/MMM/YYYY:...

How many FA10 sites have been made available?

Assume the term FA10 appears in your course_id.

SELECT [course_main].pk1, [course_main].course_id, course_name

FROM [bb_bb60].[dbo].[course_main]

where [course_main].[course_id] like '%FA10' and [course_main].[available_ind] = 'Y'

Find faculty ("instructor" role) in FA10 sites that have been made available

Assume the term FA10 appears in your course_id.

SELECT cu.users_pk1, cm.course_id

FROM [bb_bb60].[dbo].[course_users] as cu

left join [bb_bb60].[dbo].[course_main] as cm

on cu.crsmain_pk1=cm.pk1

-- course role = 'P' are instructors, data_src_pk1 = '13' is our enrollments DSK

where cu.role = 'P' and cu.data_src_pk1 = '13'

-- courses that end in FA10 and have been made available

and cm.course_id like '%FA10' and cm.available_ind = 'Y'

Count students in FA10 sites that have been made available

Assume the term FA10 appears in your course_id

SELECT cm.course_id, count(cu.users_pk1) as counts

FROM [bb_bb60].[dbo].[course_users] as cu

left join [bb_bb60].[dbo].[course_main] as cm

on cu.crsmain_pk1=cm.pk1

-- course role = 'P' are instructors, 'S' for students

where cu.role = 'S'

-- and data_src_pk1 = '13' is our enrollments DSK

and cu.data_src_pk1 = '13'

-- and user status is enabled

and cu.row_status = '0'

-- courses that end in FA10

and cm.course_id like '%FA10'

-- course is available

and cm.available_ind = 'Y'

group by cm.course_id

order by cm.course_id