SQL query: courses with iClicker registrations

SELECT cm.course_id, u.firstname, u.lastname, u.email
  FROM bb_bb60.course_main cm
  INNER JOIN bb_bb60.course_users cu ON cm.pk1 = cu.crsmain_pk1
  INNER JOIN bb_bb60.users u ON u.pk1 = cu.users_pk1
  WHERE cm.row_status = 0 AND cu.row_status = 0 AND cu.available_ind = 'Y' AND cu.role = 'P' AND cm.course_id LIKE '%20148' AND cm.pk1 IN (
    SELECT TO_NUMBER(regexp_replace(ir.course_id, '_(.*)_1', '\1'))
    FROM bb_bb60.iclicker_registration ir WHERE regexp_like(ir.course_id,'_[0-9]+_1')
  );

Defining Custom Template Variables in a Building Block

The NSSE template variable b2 can be downloaded here.  My presentation from Bb DevCon 2015 can be found in that folder as well.

I would like to thank Simon Ponder of Purdue's Informatics group for sharing his code on which I based the below upon.  Hopefully, this post will give others looking to create their own custom template variables in Blackboard Learn enough information to get started.

To start off, we have to define our plugin as a java extension in the bb-manifest.xml file like so:

<?xml version="1.0" encoding="utf-8"?>
<plugin>

    <name value="bsTemplateVar" />
    <handle value="bs-template-var" />
    <description value="Brett's Custom Template Variable" />
    <default-locale value="en_US" />
 <webapp-type value="javaext"/>
...
</plugin>

Deleting Anonymous discussion posts from a course

When importing an archive into Blackboard, discussion postings may come over depending on your selection when importing and, of course, the content of the archive you are importing. These posts come in as being posted by Anonymous. This sql can be used to clear out all Anonymous posts in course and group discussion boards for any given course. Note: you must change the 'Your Course_ID goes here' for this to work. (e.g., 'ENGL-101-07IN-S12012').

User Activity per hour

My boss wanted some stats on when the Blackboard server was used most and least. This sql query will give it to you by the hour for as many days and you want to go back. The day of the week in integer form included for calculating average usage by day of the week over longer periods of time (change @dayint [highlighted below] to a multiple of 7, of course).

Remove adaptive release dates and set viewable all content for specific section

update cc
set start_date = NULL, end_date = NULL, available_ind = 'Y'
FROM course_contents cc
INNER JOIN course_main cm on cc.crsmain_pk1 = cm.pk1
WHERE cm.course_id = 'dev-cc-bs0607-1'

Count of grades entered, grouped by course_id

SELECT cm.course_id, COUNT(gg.pk1) AS GradeCount
FROM course_main cm
INNER JOIN course_users cu ON cm.pk1 = cu.crsmain_pk1
LEFT JOIN gradebook_grade gg ON cu.pk1 = gg.course_users_pk1
WHERE cm.available_ind = 'Y' AND cm.row_status = '0'
GROUP BY cm.course_id 
ORDER BY GradeCount ASC

Assign random student_id to test users

Assign random student_id (9xxxxxx) to test users in a particular class with user_id starting with xx
reference: Update a SQL Server Column with Random Numbers within a Range

UPDATE u
SET student_id = ABS(CHECKSUM(NEWID())) % 1000000 + 9000000
FROM users u
INNER JOIN course_users cu ON u.pk1 = cu.users_pk1
INNER JOIN course_main cm ON cu.crsmain_pk1 = cm.pk1
WHERE u.user_id LIKE 'xx%' AND cm.course_id = 'INT-Testcourse'