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