Skip to main content /* Submitted right after the opening tab */

Selected Blackboard DDA Queries: Home

Selected Blackboard Data Direct Access (DDA) Queries.

Introduction

Introduction

The sections below document selected SQL queries used to extract information from a specific instance of a Blackboard Data Direct Access (Bb DDA) database. As of March of 2018:

  • The database schema is Blackboard SaaS 3400.0.0 (CLICK HERE to see the schema)
  • Queries are coded in PostgreSQL v. 10 or higher (CLICK HERE  to see a manual)
  • Database is accessed via Windows Desktop tool pgAdmin 4 v. 3.2 or higher  (CLICK HERE to see documentation)
  • Scheduled queries are executed using a command-line client for Windows (CLICK HERE to see documentation)
  • The command-line client must be able to locate a password file for access to the database (CLICK HERE to see documentation).
  • CLICK HERE to see Usage Notes on selected aspects of the Blackboard Database.

Student Submissions

Dates of Work Submitted by Student and Course

Description
For students with the Institution Role 'Online Student', the query finds discussion board posts, submissions recorded in a course gradebook, and the date of last access to the course. The query is limited to work submitted within the last 61 days. Submissions recorded in a gradebook include a file uploaded by the student to an assignment, text written by the student in an assignment, results of an online test, or work that leaves a link references in the linkrefid column of the attempt table.
 
Query Code
 
WITH
   online_student_pk1s AS (
      SELECT DISTINCT pk1
      FROM users
      WHERE institution_roles_pk1 IN
         (SELECT pk1 from institution_roles
          WHERE role_name = 'Online Student')
   ),
   db_posts AS (
      SELECT
      u.lastname, u.firstname, u.user_id, cm.course_id, 
  TO_CHAR(msgm.dtcreated, 'YYYY-MM-DD HH24:MI') AS submit_date,
      'DB Post'::TEXT AS submit_type,
      fm.name AS title
      FROM online_student_pk1s
      LEFT JOIN users u on u.pk1 = online_student_pk1s.pk1
      LEFT JOIN msg_main msgm on msgm.users_pk1 = u.pk1
      LEFT JOIN forum_main fm on fm.pk1 = msgm.forummain_pk1
      LEFT JOIN conference_main confmain on confmain.pk1 = fm.confmain_pk1
      LEFT JOIN course_main cm on cm.pk1 = confmain.crsmain_pk1
  WHERE AGE(msgm.dtcreated) < INTERVAL '61 Days'
   ),
   gb_submissions AS (
      SELECT
      u.lastname, u.firstname, u.user_id, cm.course_id, 
  TO_CHAR(att.attempt_date, 'YYYY-MM-DD HH24:MI') AS submit_date,  
      (CASE WHEN atf.files_pk1 IS NOT NULL THEN 'UPLOADED FILE' 
            WHEN att.student_submission IS NOT NULL THEN 'WRITTEN TEXT'
            WHEN att.qti_result_data_pk1 IS NOT NULL THEN 'ONLINE TEST'
            WHEN att.linkrefid IS NOT NULL THEN 'EXTERNAL WORK' ELSE '**UNKNOWN TYPE**' END)::TEXT
  AS submit_type,
      gm.title
      FROM online_student_pk1s
      LEFT JOIN users u on u.pk1 = online_student_pk1s.pk1
      LEFT JOIN course_users cu on cu.users_pk1 = u.pk1
      LEFT JOIN course_main cm on cm.pk1 = cu.crsmain_pk1
      LEFT JOIN gradebook_grade gg on gg.course_users_pk1 = cu.pk1
      LEFT JOIN gradebook_main gm on gm.pk1 = gg.gradebook_main_pk1
      LEFT JOIN attempt att on att.pk1 = gg.last_attempt_pk1
      LEFT JOIN attempt_files atf on atf.attempt_pk1 = att.pk1
      WHERE (atf.files_pk1 IS NOT NULL OR att.student_submission IS NOT NULL 
          OR att.qti_result_data_pk1 IS NOT NULL OR att.linkrefid IS NOT NULL)
  AND AGE(att.attempt_date) < INTERVAL '61 Days'
   ),
   max_act_accum AS (
   SELECT online_student_pk1s.pk1, aa.course_pk1,
   TO_CHAR(MAX(aa.timestamp), 'YYYY-MM-DD HH24:MI') AS maxtime
   FROM online_student_pk1s
   JOIN activity_accumulator aa on aa.user_pk1 = online_student_pk1s.pk1
   WHERE aa.course_pk1 IS NOT NULL
   AND AGE(aa.timestamp) < INTERVAL '61 DAYS'
   GROUP BY online_student_pk1s.pk1, aa.course_pk1
   ),
   last_course_access AS (
   SELECT
      u.lastname, u.firstname, u.user_id, cm.course_id,
      max_act_accum.maxtime AS submit_date,
      'ACCESS'::TEXT AS submit_type,
      'Last Course Access'::TEXT AS title
   FROM max_act_accum
   LEFT JOIN users u on u.pk1 = max_act_accum.pk1
   LEFT JOIN course_main cm on cm.pk1 = max_act_accum.course_pk1
   )
SELECT
   lastname, firstname, user_id, submit_date, course_id, submit_type, title
FROM 
   (SELECT * FROM db_posts
    UNION
    SELECT * FROM gb_submissions
    UNION
    SELECT * FROM last_course_access) SUBQ
ORDER BY lastname, firstname, user_id, course_id, submit_date DESC, submit_type

Last Course Access for Selected Students

Last Course Access for Selected Students

Description:

Lists the date and time of last Blackboard course access for a set of students identified by student ID number (users.user_id). The last access date/time comes from the course_users table, which is the many-to-many nexus between users and courses. Courses are limited to one semester by the name of the term associated with a course and the structure of the course ID number. Blackboard allows two or more courses to be merged (rolled up) into a parent course, and course activity is recorded at the level of the parent course in such cases. For this reason, the query is limited to course_users rows that have no parent, meaning that the course either is itself a parent of other courses or is an individual course not merged with others (cmparent.pk1 IS NULL). Output fields list the course for which activity is recorded, and the related child course if there is one. For the purposes of matching registration in the Student Information System (LORA), a "LORACOURSE" field displays the Course ID of the child course if there is one, or of the parent course if there is no child.

Query Code:

SELECT 
lastname, firstname, user_id, bbdcourse, lastaccess, childcourse, loracourse
FROM (SELECT

t.name term_name, 
u.lastname, u.firstname, u.user_id,
cm.course_id AS BBDCOURSE,
cu.last_access_date AS LASTACCESS,
cmchild.course_id AS CHILDCOURSE,
(CASE WHEN cmchild.course_id IS NULL THEN cm.course_id ELSE cmchild.course_id END) LORACOURSE,
(CASE WHEN cmchild.course_id IS NULL THEN cm.course_id || u.user_id ELSE cmchild.course_id || u.user_id END) SEARCHKEY
FROM users u
LEFT JOIN course_users cu ON cu.users_pk1 = u.pk1
LEFT JOIN course_course cc ON cc.crsmain_pk1 = cu.crsmain_pk1
LEFT JOIN course_main cm ON cm.pk1 = cu.crsmain_pk1
LEFT JOIN course_main cmchild ON cmchild.pk1 = cu.child_crsmain_pk1
LEFT JOIN course_main cmparent ON cmparent.pk1 = cc.crsmain_parent_pk1
LEFT JOIN course_term ct ON ct.crsmain_pk1 = cu.crsmain_pk1
LEFT JOIN term t ON t.pk1 = ct.term_pk1
WHERE cmparent.pk1 IS NULL) SQ1

WHERE user_id IN ( -- list of student ID's goes here -- )
AND term_name = '2018 Spring'
ORDER BY lastname, firstname, user_id, bbdcourse

Scheduled Execution:

This report is automatically executed on a schedule, using the Windows "psql" command-line client. The scheduled job must first set an environment variable that identifies the location of a file containing the database password, and can then invoke the psql executable with the necessary command-line options:

SET PGPASSFILE=<<passwordfilepath>>
"C:\Program Files (x86)\pgAdmin 4\v2\runtime\psql" -h <<hostname>> -U <<username>> -d <<databasename>> -w -f <<sqlpath>> -o <<outputpath>>

The items in angle-brackets have to be replaced as follows:

<<passwordfilepath>>

File path specifying the location of a file describing the password required to access the database. 

<<hostname>>

The name of the host on which the DDA database resides.

<<username>>

The name of the user whose privileges define access to the database.

<<databasename>>

The name of the DDA database being accessed.

<<sqlpath>>

File path specifying the SQL code to be executed, in plain text format.

<<outputpath>>

File path specifying the file to receive the output of the query. The format of the output is determined by options in the command-line, falling back to defaults if no formats are specified.

 

Grade Report for Selected Students

Grade Report For Selected Students

Description:

Lists the scores received for selected students in selected courses. An inner query selects the primary keys of the users table for users who are part of a named hierarchy node (domain). 

SQL Code:

SELECT 
cm.course_id,
u.user_id,
u.lastname,
gm.title gradebook_title,
gm.pk1 gradebook_id,
gm.user_created_ind manual,
REPLACE(gt.name,'.name','') category,
gm.possible point_value,
(CASE WHEN gm.multiple_attempts = 1 THEN 'SINGLE' ELSE 'MULTIPLE' END) attempts,
(CASE WHEN gm.aggregation_model = 1 THEN 'LAST' WHEN gm.aggregation_model = 2 THEN 'HIGH'
   WHEN gm.aggregation_model = 3 THEN 'LOW' WHEN gm.aggregation_model = 4 THEN 'FIRST'
   WHEN gm.aggregation_model = 5 THEN 'AVG' ELSE '???' END) aggregation_type,
(CASE WHEN gg.manual_score IS NOT NULL AND gg.last_override_date IS NOT NULL THEN gg.manual_score 
   WHEN gm.aggregation_model = 1 THEN attlast.score WHEN gm.aggregation_model = 2 THEN atthigh.score
   WHEN gm.aggregation_model = 3 THEN attlow.score WHEN gm.aggregation_model = 4 THEN attfirst.score
   WHEN gm.aggregation_model = 5 THEN gg.average_score ELSE NULL END) counted_score,
gg.manual_score,
gg.last_override_date override_date,
gg.average_score,
attfirst.score score_first,
attlast.score score_last,
atthigh.score score_high,
attlow.score score_low
FROM course_users cu
JOIN course_main cm on cm.pk1 = cu.crsmain_pk1
JOIN users u on u.pk1 = cu.users_pk1
JOIN gradebook_grade gg on gg.course_users_pk1 = cu.pk1
JOIN gradebook_main gm on gm.pk1 = gg.gradebook_main_pk1
LEFT JOIN gradebook_type gt on gt.pk1 = gm.gradebook_type_pk1
LEFT JOIN attempt attfirst on attfirst.pk1 = gg.first_attempt_pk1
LEFT JOIN attempt attlast on attlast.pk1 = gg.last_attempt_pk1
LEFT JOIN attempt atthigh on atthigh.pk1 = gg.highest_attempt_pk1
LEFT JOIN attempt attlow on attlow.pk1 = gg.lowest_attempt_pk1
WHERE cu.users_pk1 IN
   /* Select keys of users who are members of a named hierarchy node */
   (SELECT duc.user_pk1 
    FROM domain_user_coll duc
    JOIN domain dom on dom.pk1 = duc.domain_pk1
    WHERE dom.name = 'HigherEd2018F')
AND cm.course_id LIKE '18F-%'
AND gm.deleted_ind = 'N'
AND gm.scorable_ind = 'Y'
AND gm.calculated_ind = 'N'
AND gg.exempt_ind = 'N'
AND gg.excluded_ind = 'N'
ORDER BY cm.course_id, u.user_id, gm.title, gm.pk1

Accounts with Elevated Privileges

Accounts With Elevated Privileges

Background:

See "User Account Privileges" on the Notes page.

Description:

The objective of this query is to list all user accounts that have elevated system privileges, meaning any account with a Primary System Role or Secondary System Role other than 'N' (for 'None) or NULL. The listing is implemented as the union of two queries. The first query selects all users with a users.system_role other than 'N'. The second query includes all users listed in the domain_admin table. 

SQL Code:

(SELECT 
(CASE WHEN u.system_role = 'N' THEN 'None' WHEN u.system_role = 'C' THEN 'Course Admin'
WHEN u.system_role = 'U' THEN 'Guest' WHEN u.system_role = 'O' THEN 'Obsever'
WHEN u.system_role = 'R' THEN 'Support' WHEN u.system_role = 'Z' THEN 'System Admin'
WHEN u.system_role = 'H' THEN 'System Support' WHEN u.system_role = 'A' THEN 'User Admin'
ELSE u.system_role END) SystemRole,
'default' DomainName,
'Primary' RoleType,
u.lastname, u.firstname, u.user_id,
(CASE WHEN u.row_status = 0 AND u.available_ind = 'Y' THEN 'Can Login' ELSE 'No' END) CanLogin
FROM users u
WHERE u.system_role <> 'N') 
UNION ALL
(SELECT
(CASE WHEN da.system_role = 'N' THEN 'None' WHEN da.system_role = 'C' THEN 'Course Admin'
WHEN da.system_role = 'U' THEN 'Guest' WHEN da.system_role = 'O' THEN 'Obsever'
WHEN da.system_role = 'R' THEN 'Support' WHEN da.system_role = 'Z' THEN 'System Admin'
WHEN da.system_role = 'H' THEN 'System Support' WHEN da.system_role = 'A' THEN 'User Admin'
ELSE da.system_role END) SystemRole,
dom.name AS DomainName,
'Secondary' RoleType,
u.lastname, u.firstname, u.user_id,
(CASE WHEN u.row_status = 0 AND u.available_ind = 'Y' THEN 'Can Login' ELSE 'No' END) CanLogin
FROM domain_admin da
LEFT JOIN domain dom ON dom.pk1 = da.domain_pk1
LEFT JOIN users u on u.pk1 = da.user_pk1)
ORDER BY systemrole, domainname, roletype, lastname, firstname, user_id

Parent-Child Course Relationships

Parent-Child Course Relationships

Description:

A simple query listing "merged enrollments,"  parent-child relationships between Blackboard courses, for a single semester. The parent-child relationship is recorded in the course_course table.

Query Code:

SELECT
parent.course_id Parent_Course, child.course_id Child_Course
FROM course_course cc
JOIN course_main parent on parent.pk1 = cc.crsmain_parent_pk1
JOIN course_main child on child.pk1 = cc.crsmain_pk1
LEFT JOIN course_term ct on ct.crsmain_pk1 = parent.pk1
LEFT JOIN term t on t.pk1 = ct.term_pk1
WHERE parent.course_id like '18S-%' OR t.name = '2018 Spring'
ORDER BY parent.course_id, child.course_id

Older Courses Still Available

Older Courses Still Available

Description:

Produces a list of Course IDs of courses that are marked as available in the course_main table but haven't been accessed after a specified cutoff date. The sub-queries find 1) the last modification date for each available course in course_main, 2) the last access date for each user enrolled in a course that is still available, as recorded in course_users, and 3) the last transaction date of any action related to an available course as recorded in the activity accumulator. 

Query Code:

SELECT sq1.course_id, MAX(sq1.maxdate) AS maxmaxdate
FROM
    (
        (SELECT 
        cm.course_ID, MAX(aa.timestamp) AS MAXDATE
        FROM activity_accumulator aa
        JOIN course_main cm on cm.pk1 = aa.course_pk1
        WHERE aa.course_pk1 IS NOT NULL
        AND cm.available_ind = 'Y'
        GROUP BY cm.course_ID
        ORDER BY cm.course_ID)
    UNION ALL
        (SELECT 
        cm.course_ID, MAX(cu.last_access_date) AS MAXDATE
        FROM course_users cu
        JOIN course_main cm on cm.pk1 = cu.crsmain_pk1
        WHERE cm.available_ind = 'Y'
        GROUP BY cm.course_ID
        ORDER BY cm.course_ID)
    UNION ALL
        (SELECT 
        cm.course_ID, dtmodified AS MAXDATE
        FROM course_main cm
        WHERE cm.available_ind = 'Y'
        ORDER BY cm.course_ID)
    ) SQ1
GROUP BY sq1.course_id
HAVING MAX(sq1.maxdate) < '1-JAN-2017'
ORDER BY sq1.course_id

Users in a Hierarchy Node

Users in a Hierarchy Node

If a hierarchy node (means of classifying courses and users) has been created and users added to it, a simple listing can be obtained with:

SELECT
dom.name,
u.lastname, u.firstname, u.user_id
FROM domain_user_coll duc
JOIN users u on u.pk1 = duc.user_pk1
JOIN domain dom on dom.pk1 = duc.domain_pk1

Selected Student Submissions

Selected Student Submissions

Description:

For students who are members of a named hierarchy node (domain), lists submitted work (attempts) by due date. Gradebook columns with no due date are excluded. An attempt that was made after the due date is labeled 'LATE' and an attempt never made is labeled 'MISSING' if the due date has passed.

SQL Code:

SELECT 
u.user_id,
u.lastname,
cm.course_id,
gm.due_date,
attfirst.attempt_date,
(CASE WHEN attfirst.attempt_date IS NOT NULL and attfirst.attempt_date > gm.due_date THEN 'LATE'
   WHEN attfirst.attempt_date IS NULL and gm.due_date < NOW() THEN 'MISSED' ELSE ' ' END) status,
gm.title gradebook_title,
gm.pk1 gradebook_id,
gm.possible point_value,
(CASE WHEN gm.multiple_attempts = 1 THEN 'SINGLE' ELSE 'MULTIPLE' END) attempts
FROM course_users cu
JOIN course_main cm on cm.pk1 = cu.crsmain_pk1
JOIN users u on u.pk1 = cu.users_pk1
JOIN gradebook_main gm on gm.crsmain_pk1 = cu.crsmain_pk1
LEFT JOIN gradebook_grade gg on gg.course_users_pk1 = cu.pk1 AND gg.gradebook_main_pk1 = gm.pk1
LEFT JOIN attempt attfirst on attfirst.pk1 = gg.first_attempt_pk1
WHERE cu.users_pk1 IN
   /* Select keys of users associated with a named hierarchy node */
   (SELECT duc.user_pk1 
    FROM domain_user_coll duc
    JOIN domain dom on dom.pk1 = duc.domain_pk1
    WHERE dom.name = 'HigherEd2018F')
AND cm.course_id LIKE '18F-%'
AND gm.deleted_ind = 'N'
AND gm.scorable_ind = 'Y'
AND gm.due_date IS NOT NULL
ORDER BY u.user_id, cm.course_id, gm.due_date, gm.title, gm.pk1

Give Now