AnsweredAssumed Answered

Determine Empty Courses

Question asked by bishop.thomas on Apr 2, 2019
Latest reply on Apr 18, 2019 by bl31720

Like • Show 0 Likes0
Comment • 7

I need to determine if certain courses are devoid of content at the start of term. Currently I use the following query which checks for zero course size and counts how many Grade Center columns there are. Blank courses only have two so I must further limit the results to only those. Is there a better way of doing this?:

SELECT cm.course_id, cm.course_name, Count(gm.title) AS gc_column_title_count, cs.size_total

FROM public.course_main cm

LEFT JOIN public.course_size cs ON cs.crsmain_pk1 = cm.pk1

LEFT JOIN public.course_contents cc ON cc.crsmain_pk1 = cm.pk1

LEFT JOIN public.gradebook_main gm ON gm.crsmain_pk1 = cm.pk1

WHERE cm.course_id LIKE '%.201840'

AND cm.course_name LIKE '%-OL%'

AND cm.course_name NOT LIKE 'SP19 X%'

AND cm.available_ind = 'Y'

AND cs.size_total = 0

GROUP BY cm.course_id, cm.course_name, cs.size_total

ORDER BY gc_column_title_count;

/* course_id will contain the term ID */

/* course_name will include "-OL" for fully online offerings */

/* filter out course numbers starting with 'X' as those are just workshops */

/* only courses still available (not terminated) */

/* course_size totals can be zero and still not devoid of content */

Would this be the definitive way of finding empty courses?

Is there a better way of limiting results to less than 3 for the aggregate 'gc_column_title_count'?

No one else has this question

Outcomes

rs0040357 Apr 4, 2019 10:49 AM

For simple quick analysis, I use course_main DTMODIFIED

Actions

goh.ian @ rs0040357 on Apr 4, 2019 7:49 PM

Wouldn’t that get updated by your SIS feed? Or do only send course data once?

Actions

jkelley_blackboard @ rs0040357 on Apr 5, 2019 10:21 AM

Roger,

FYI course_main.dtmodified wouldn't necessarily be updated by an instructor adding content to the course.

Actions

tg0047770 Apr 4, 2019 11:05 AM

We provision staff with templates that have minimal course content, so an 'empty' course for us would be one that has <12 records in the course_contents table

Actions

jkelley_blackboard Apr 5, 2019 10:25 AM

Thomas,

Seems like you are on the right track for the most common/likely places to look. There is always a possibility that the course has non-graded, non file based content - so there may still be some courses that are included as false-positives in your results, but I expect they are very few.

Actions

laura74223 Apr 15, 2019 12:29 AM

Typing a caret after the opening square bracket negates the character class. The result is that the character class matches any character that is not in the character class walgreenslistens.

Actions

bl31720 Apr 18, 2019 12:03 PM

I would check the numbers on your template. And then use that number to determine your usage beyond the creation status. So you need to run a script to determine number of default items in the course. Then run a script that looks for that number of course items to determine zero usage or beyond that to determine if the course was used. In our case of looking for usage, we will not necessarily account for a course that has had items removed, then a few added back in, but those would be very few cases. And I have not found that to be an issue for a general Bb usage reports.

Here are some hobbled together scripts that may help you or someone else in determining course usage beyond the course template. I have more scripts including an older way that looks explicitly for gradcenter columns, documents added, emails sent, etc. But I wanted to simply my process and I think what is below works well. Just modify it to fit your needs. But looking at content handles I think is the way to go. You just always need to know what your base is, your template count for each semester you are looking for.

/*---------------------------------------------------------------*/

/* DetermineTemplateContentHandles.sql */

/*---------------------------------------------

Determine the content handles in the template.

Use this to help determine the default for the template per semester.

Find courses with no instructor, verify no syllabus, thus should be just like the template. Disabled course preferable as they were not used upon creation, then subsequently disabled via the snapshot process due to Registrar changes to the course.

-----------------------------------------------*/

--Step 1 find a disabled (or verified unused) course, paste the course id below to find the pk1.

SELECT * from course_main where course_id = 'ANT-200-100-4185';

--Step 2 enter pk1 to find all content added.

SELECT cc.cnthndlr_handle, cc. web_url,cc.title,cc.dtcreated

FROM course_contents cc

WHERE crsmain_pk1 = '99999999'

order by cc.cnthndlr_handle, cc.title;

/*---------------------------------------------------------------*/

/* CourseUsageByToolsBeyondSyllabusAdd

20181107: changed to >61 for beyond syllabus check for Summer/Fall 2018 */

SELECT ncm.pk1, ncm.course_id, ncm.course_name

FROM files f

left join course_contents_files ccf on ccf.files_pk1=f.pk1

left join course_contents cc on cc.pk1=ccf.course_contents_pk1

left join course_main ncm on cc.crsmain_pk1=ncm.pk1

left join course_users cu on cu.crsmain_pk1=ncm.pk1

WHERE

ncm.batch_uid LIKE '4185%' /*Change semester code here */

AND ncm.course_id NOT LIKE ncm.batch_uid /* Exclude GUI created shells */

AND ncm.service_level = 'F' /* Course (F), not an organization (C) */

AND ncm.row_status <> '2' /* Course enabled (enabled=0, disabled=2) */

AND cu.role='S' /* Courses with Students only */

AND cc.parent_pk1 is not null

--AND LOWER(f.link_name) LIKE '%syll%' --we are looking for syllabus files but you may want to comment this out

AND (

(SELECT COUNT(*) AS CC1

FROM COURSE_CONTENTS zcc

WHERE zcc.crsmain_pk1 = ncm.pk1)) >61 --based off the number of items in our template; we are *looking* for usage

group by ncm.pk1, ncm.course_id, ncm.course_name;

Actions

7 Replies

rs0040357 Apr 4, 2019 10:49 AM
Mark Correct
Correct Answer
For simple quick analysis, I use course_main DTMODIFIED
Like • Show 0 Likes0
Actions
- goh.ian @ rs0040357 on Apr 4, 2019 7:49 PM
  Mark Correct
  Correct Answer
  Wouldn’t that get updated by your SIS feed? Or do only send course data once?
  Like • Show 0 Likes0
  Actions
- jkelley_blackboard @ rs0040357 on Apr 5, 2019 10:21 AM
  Mark Correct
  Correct Answer
  Roger,
  FYI course_main.dtmodified wouldn't necessarily be updated by an instructor adding content to the course.
  Like • Show 0 Likes0
  Actions
tg0047770 Apr 4, 2019 11:05 AM
Mark Correct
Correct Answer
We provision staff with templates that have minimal course content, so an 'empty' course for us would be one that has <12 records in the course_contents table
Like • Show 1 Like1
Actions
jkelley_blackboard Apr 5, 2019 10:25 AM
Mark Correct
Correct Answer
Thomas,
Seems like you are on the right track for the most common/likely places to look. There is always a possibility that the course has non-graded, non file based content - so there may still be some courses that are included as false-positives in your results, but I expect they are very few.
Like • Show 0 Likes0
Actions
laura74223 Apr 15, 2019 12:29 AM
Mark Correct
Correct Answer
Typing a caret after the opening square bracket negates the character class. The result is that the character class matches any character that is not in the character class walgreenslistens.
Like • Show 0 Likes0
Actions
bl31720 Apr 18, 2019 12:03 PM
Mark Correct
Correct Answer
I would check the numbers on your template. And then use that number to determine your usage beyond the creation status. So you need to run a script to determine number of default items in the course. Then run a script that looks for that number of course items to determine zero usage or beyond that to determine if the course was used. In our case of looking for usage, we will not necessarily account for a course that has had items removed, then a few added back in, but those would be very few cases. And I have not found that to be an issue for a general Bb usage reports.

Here are some hobbled together scripts that may help you or someone else in determining course usage beyond the course template. I have more scripts including an older way that looks explicitly for gradcenter columns, documents added, emails sent, etc. But I wanted to simply my process and I think what is below works well. Just modify it to fit your needs. But looking at content handles I think is the way to go. You just always need to know what your base is, your template count for each semester you are looking for.
/*---------------------------------------------------------------*/
/* DetermineTemplateContentHandles.sql */
/*---------------------------------------------
Determine the content handles in the template.
Use this to help determine the default for the template per semester.
Find courses with no instructor, verify no syllabus, thus should be just like the template. Disabled course preferable as they were not used upon creation, then subsequently disabled via the snapshot process due to Registrar changes to the course.
-----------------------------------------------*/
--Step 1 find a disabled (or verified unused) course, paste the course id below to find the pk1.
SELECT * from course_main where course_id = 'ANT-200-100-4185';
--Step 2 enter pk1 to find all content added.
SELECT     cc.cnthndlr_handle,    cc. web_url,cc.title,cc.dtcreated
FROM     course_contents cc
WHERE     crsmain_pk1 = '99999999'
    order by cc.cnthndlr_handle, cc.title;
/*---------------------------------------------------------------*/
/* CourseUsageByToolsBeyondSyllabusAdd
20181107: changed to >61 for beyond syllabus check for Summer/Fall 2018 */

SELECT ncm.pk1, ncm.course_id, ncm.course_name
FROM files f
    left join course_contents_files ccf on ccf.files_pk1=f.pk1
    left join course_contents cc on cc.pk1=ccf.course_contents_pk1
    left join course_main ncm on cc.crsmain_pk1=ncm.pk1
    left join course_users cu on cu.crsmain_pk1=ncm.pk1
WHERE
ncm.batch_uid LIKE '4185%'              /*Change semester code here */
AND ncm.course_id NOT LIKE ncm.batch_uid /* Exclude GUI created shells */
AND ncm.service_level = 'F'               /* Course (F), not an organization (C) */
AND ncm.row_status <> '2'                 /* Course enabled (enabled=0, disabled=2) */
AND cu.role='S'                           /* Courses with Students only */
AND cc.parent_pk1 is not null
--AND LOWER(f.link_name) LIKE '%syll%' --we are looking for syllabus files but you may want to comment this out
AND (
      (SELECT COUNT(*) AS CC1
         FROM COURSE_CONTENTS zcc
        WHERE zcc.crsmain_pk1 = ncm.pk1)) >61 --based off the number of items in our template; we are *looking* for usage
group by ncm.pk1, ncm.course_id, ncm.course_name;
Like • Show 1 Like1
Actions

Determine Empty Courses

Outcomes

Related Content

Recommended Content