Blackboard DDA Queries

Here are some of the top Blackboard DDA Queries that have saved me time and effort.

Note: This is a living document, some information maybe incorrect or incomplete. Please contact me if you have any updates and/or corrections.

I use Artifical Intelligence services (ChatGPT, Claude, Microsoft Copilot, Google Gemini, and Perplexity) to help me plan, develop, and troubleshoot documentation and scripts shared on this site.

Generate an SIS Feed File from a Database Query

Sometimes an admin needs to make a change to multiple courses at the same time. An SIS flat feed file can be the simplest way to do that, but generating that feed file could be a pain. Well it isn’t with this SQL query. In this example, I’m taking all the cross listed courses (in this system they contain an -XLM- code in the Course ID) which are in the SYSTEM dsk and move them to the appropriate DSK so they will be archived.

SELECT CM.BATCH_UID, '|', CM.COURSE_ID, '|', DS.BATCH_UID, '|219F_COURSES'
FROM COURSE_MAIN CM
    JOIN DATA_SOURCE DS
     ON CM.DATA_SRC_PK1 = DS.PK1
WHERE DS.BATCH_UID = 'SYSTEM' 
  AND CM.COURSE_ID LIKE '219F-XLM%'

This generates output that will be easy to copy and paste into a text file for processing. You can change the '

' for whatever delimiter you currently use. You may also change the 219F_COURSES to the data source key name that you want to move the courses under.

Grab Information about the Device and Location of a User

Sometimes users will have an issue accessing content, report they were unable to complete a task, etc. These events can be difficult when the user is communicating indirectly with you the admin. If you are trying to troubleshoot, you must play a game of telephone to get simple information. However, if the issue happened recently and you know the date and/or time of the event you can use the data in the auth_provider_log table to get some data that can speed up the investigation. I use this query to get information on what type of system they are using.

select apl.*
from auth_provider_log apl
join users u on apl.username = u.user_id
join course_users cu on u.pk1 = cu.users_pk1
join course_main cm on cu.crsmain_pk1 = cm.pk1
where cm.course_id = [COURSE_ID_HERE]
and cu.role = [COURSE_USER_ROLE_HERE]
and apl.username = [BLACKBOARD_USER_ID]

/* Uncomment the line below and it will allow you to search for a specific value in the user agent string.

--and apl.user_agent like '%ANGEL Secure%'

The user agent string will look similar to this below.

Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36

I then copy and past this into a User Agent Lookup to find out the specific information. My regular go to is What Is My IP.

This can also be very helpful if you are a Respondus Lockdown Browser school and need to confirm that the user is accessing the test/assessment via the Respondus Lockdown Browser application and not going through a regular web browser.