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'

User enrollment with data_source batch_uid for user, course, and enrollment

SELECT cu.pk1,
u.user_id,
cm.course_id,
dsc.batch_uid as Cds,
dsu.batch_uid as Uds,
dscu.batch_uid as CUds,
cu.available_ind,
cu.row_status,
cu.dtmodified
FROM course_users cu
INNER JOIN users u on cu.users_pk1 = u.pk1
INNER JOIN course_main cm on cu.crsmain_pk1 = cm.pk1
INNER JOIN data_source dscu on cu.data_src_pk1 = dscu.pk1
INNER JOIN data_source dsu on u.data_src_pk1 = dsu.pk1
INNER JOIN data_source dsc on cm.data_src_pk1 = dsc.pk1
WHERE u.user_id = 'auser' and cm.course_id LIKE '%S12011'

Select all student enrollments from FA2010 (course_users)

SELECT u.user_id, course_main.course_id, cu.available_ind,
cu.row_status, cu.data_src_pk1 as ENR_DSRC,
u.data_src_pk1 as USER_DSRC, cu.dtmodified
FROM course_users cu INNER JOIN course_main cm on cu.crsmain_pk1 = cm.pk1
INNER JOIN u ON cu.users_pk1 = u.pk1
WHERE course_main.course_id LIKE '%FA2010%'AND
cu.data_src_pk1 < '20' AND u.user_id NOT LIKE 'stu_%' AND
u.user_id not like '%student%' AND u.user_id not like '%faculty%' AND
u.user_id <> 'bross' AND u.user_id NOT LIKE 'temp-olw%' AND
cu.role = 'S' and u.institution_roles_pk1 = '1'

Disable enrollments of all students for FA2010 (course_users)

update cu
SET row_status = '2'
FROM course_users cu
INNER JOIN course_main cm ON cu.crsmain_pk1 = cm.pk1
INNER JOIN users u ON cu.users_pk1 = u.pk1
WHERE cm.course_id LIKE '%FA2010%' AND
course_users.data_src_pk1 < '20' AND u.user_id not like 'stu_%' AND
u.user_id not like '%student%' AND u.user_id not like '%faculty%' AND
u.user_id <> 'bross' AND
u.user_id NOT LIKE 'temp-olw%' AND cu.role = 'S' AND
u.institution_roles_pk1 = '1'

Turn off availability of student enrollments in a course

UPDATE cu
set available_ind = 'N'
FROM course_users cu
INNER JOIN course_main cm ON cu.crsmain_pk1 = cm.pk1
WHERE cm.course_id = 'Adjunct_Orientation' AND cu.role = 'S'

User activity raw (activity_accumulator)

USE BBLEARN
SELECT u.user_id, aa.event_type, cm.course_id,
aa.internal_handle, aa.data, aa.timestamp, aa.messages, aa.session_id,
datediff(dy, timestamp, CURRENT_TIMESTAMP) as ddiff
FROM activity_accumulator aa
INNER JOIN course_main cm ON aa.course_pk1 = cm.pk1
INNER JOIN users u ON aa.user_pk1 = u.pk1
WHERE u.user_id = 'someuser'
AND datediff(dy, timestamp, CURRENT_TIMESTAMP) < 10
AND course_id <> ''

List courses with course_id matching pattern with last - delmited token (ie the term in WBCT-1003-030IN-FA2010)

SELECT course_main.course_id, RIGHT(course_id,PATINDEX('%-%',REVERSE(course_id))-1) AS Term
FROM course_main
WHERE course_main.course_id like 'WBCT-%'

List courses created on a certain day, sorted by row_status, availability and course_id

DECLARE @Qdate  SMALLDATETIME
SET @Qdate = '12/02/2010'
SELECT cm.course_id, cm.dtcreated,
cm.dtmodified, cm.available_ind, cm.row_status
FROM course_main cm
INNER JOIN course_users cu ON cm.pk1 = cu.crsmain_pk1
INNER JOIN users ON cu.users_pk1 = users.pk1
WHERE cm.row_status = '0' AND cm.course_id like '%SP2011' AND
cm.dtcreated BETWEEN @Qdate AND DATEADD(day,1,@Qdate)
ORDER BY cm.row_status DESC, cm.available_ind ASC, cm.course_id ASC

Fix for users not showing up in messaging user dropdown

UPDATE course_users
SET receive_email_ind = 'Y', roster_ind = 'Y'
WHERE (roster_ind = 'N' OR receive_email_ind = 'N') AND available_ind = 'Y';

Forums with ENFORCE_MODERATION enabled

SELECT fr.pk1,
u.email,
cm.course_id,
fm.name,
fr.dtcreated,
fr.dtmodified
FROM forum_registry fr
INNER JOIN forum_main fm ON fr.forummain_pk1 = fm.pk1
INNER JOIN conference_main confm ON fm.confmain_pk1 = confm.pk1 
INNER JOIN course_main cm ONconfm.conference_owner_pk1 = cm.pk1
INNER JOIN (SELECT * from course_users WHERE role = 'P') AS cu ON cm.pk1 = cu.crsmain_pk1
INNER JOIN users u ON cu.users_pk1 = u.pk1
WHERE registry_key LIKE 'ENFORCE%' AND registry_value = 'Y'