I would like to track courses that have not being use during a specific term, either because they were not accessed by any instructor's or were not made available during this period. Or any other idea ?
after several days trying to adapt your querries and to use the table ACTIVITY_ACCUMULATOR , I finally ended with this query :
select DISTINCT cm.course_id, cm.course_name, cm.AVAILABLE_IND, cu.crsmain_pk1
from COURSE_USERS cu, course_main cm
WHERE cm.course_id like '17_%'
AND cu.crsmain_pk1 NOT IN (
from BBLEARN_STATS.COURSE_USERS cu, course_main cm
AND cu.LAST_ACCESS_DATE > '01/01/2017'
AND cu.ENROLLMENT_DATE > '01/01/2017');
So I'm checking if Students have access the courses in 2017, and I display the other (with the course ID starting by '17_' : our nomenclature for this school year)
I have a number of queries in the arsenal that I use to determine which (automatically created courses) were not used in a TERM.
This will show courses that didn't have any student, instructor, TA, builder, etc accesses:
SELECT DISTINCT cm.COURSE_ID, cm.COURSE_NAME, cm.AVAILABLE_IND, cm.ROW_STATUS
FROM COURSE_MAIN cm
WHERE cm.PK1 NOT IN (
FROM ACTIVITY_ACCUMULATOR aa, USERS u, COURSE_USERS cu
WHERE cm.PK1 = aa.COURSE_PK1
AND aa.USER_PK1 = u.PK1
AND u.PK1 = cu.USERS_PK1
AND cu.ROLE IN ('T', 'B', 'G', 'S'))
AND cm.PK1 NOT IN (
AND cu.ROLE ='P'
AND u.USER_ID NOT IN ('bbadmin'))
AND (cm.COURSE_ID like '1173%-THEUA%-SEC9%' or cm.COURSE_ID like 'MASTER-1173%-SEC9%')
ORDER by cm.COURSE_ID
but if you're using merged courses, the child courses won't have accesses, so you'll need to generate a list of child/parent associations and compare those.
select cm2.course_id AS child,
cm.course_id AS parent
from course_course cc
join course_main cm on cm.pk1=cc.CRSMAIN_PARENT_PK1
join course_main cm2 on cc.CRSMAIN_PK1=cm2.pk1
where cm2.course_id like '1179%'
order by child
First, I look for courses that have Zero enrollments. Those are easy ones to kill off.
-- From: David Carter-Tod <dcartertod@VCCS.EDU>
-- This works for no enrollments at all
-- returns different results than Shannon's query, which only looks for specific roles (no designers, for example)
WHEN cm.ROW_STATUS = 0 THEN 'ENABLED'
WHEN cm.ROW_STATUS = 2 THEN 'DISABLED'
END AS COURSE_ROW_STATUS,
WHEN cm.AVAILABLE_IND='Y' THEN 'Yes'
WHEN cm.AVAILABLE_IND='N' THEN 'No'
END AS COURSE_AVAILABLE
from course_main cm
left join course_users cu
where cu.pk1 is null
AND (cm.COURSE_ID like '1159-THEUA%' or cm.COURSE_ID like 'MASTER-1159%')
ORDER By COURSE_ID
Once I've identified courses that "probably" were not in use, I move them to a "PURGE" DSK.
I check to see if they have any content based on file size:
(This checks all courses within a specific DSK)
/* coursefiles = data stored in course files aka content collection */
/* protectedfiles = Files that are used in assignments, tests, and student submissions */
/* legacyfiles = files used prior to Learn 9.1. However, Some course tools that require private file storage may be stored in Legacy Filesystem Files instead of Protected Files. */
JOIN course_size cs ON (cm.pk1 = cs.crsmain_pk1)
INNER JOIN BBLEARN.DATA_SOURCE DS ON CM.data_src_pk1 = DS.pk1
ORDER by CS.SIZE_TOTAL
I check to see if there are any gradebook columns / attempts:
SELECT cm.course_id, u.user_id, gm.title, gm.score_provider_handle AS "Item Type", to_char(a.attempt_date,'mm/dd/yyyy HH24:MI:SS') "Attempt Date", a.last_graded_date, a.grade
FROM BBLEARN.attempt a
INNER JOIN BBLEARN.gradebook_grade gg ON a.gradebook_grade_pk1 = gg.pk1
INNER JOIN BBLEARN.gradebook_main gm ON gg.gradebook_main_pk1 = gm.pk1
INNER JOIN BBLEARN.gradebook_type gt ON gm.gradebook_type_pk1 = gt.pk1
INNER JOIN BBLEARN.course_users cu ON gg.course_users_pk1 = cu.pk1
INNER JOIN BBLEARN.users u ON cu.users_pk1 = u.pk1
INNER JOIN BBLEARN.course_main cm on cu.crsmain_pk1 = cm.pk1
order by cm.course_id, u.user_id, a.attempt_date
and verify that there haven't been any enrollments:
WHEN CM.ROW_STATUS = 0 THEN 'Enabled'
WHEN CM.ROW_STATUS = 2 THEN 'Disabled'
END AS ROW_STATUS,
FROM course_main cm
LEFT JOIN course_users cu ON cm.pk1 = cu.crsmain_pk1
INNER JOIN DATA_SOURCE DS ON CM.data_src_pk1 = DS.pk1
GROUP BY cm.course_id, CM.ROW_STATUS, cm.AVAILABLE_IND
my workflow for purging courses is:
Thank you so much !
I'll tri that and adapt it to our system.
Retrieving data ...