Tuesday, April 5, 2011

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

No comments:

Post a Comment