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