I'm trying to run a query on our Blackboard OpenDB on discussions. If I run the query through the discussion_stream_event table, I only get the most recent discussion board form a course. Do you know why that would happen?
I bet that's only used in the activity stream in My Blackboard and/or the Blackboard App.
Here's a handful of DB queries that I use....
-- Discussions - FIND ALL entries for a specific user in MSG_MAIN
FROM MSG_MAIN MM
INNER JOIN users u ON MM.USERS_PK1 = u.pk1
WHERE u.user_id = 'chris'
ORDER BY DTCREATED
-- Discussions - show forum posts from Specific User
FROM DISCUSSION_STREAM_EVENT DSE
INNER JOIN users u ON DSE.ACTOR_PK1 = u.pk1
LEFT JOIN course_main cm ON DSE.CRSMAIN_PK1 = cm.pk1
LEFT JOIN forum_main fm ON DSE.FORUM_MAIN_PK1 = fm.pk1
LEFT JOIN msg_main mm ON DSE.MSG_MAIN_PK1 = mm.pk1
ORDER BY EVENT_DATE
-- Discussions - show forum replies from Specific User
INNER JOIN users u ON DSE.ACTEE_PK1 = u.pk1
-- I wanted to see date and other information about a specific discussion forum
WHERE CONFMAIN_PK1 = '249506'
What sort of information are you trying to gather?
Folks here are trying to make sure that we can provide proof of "regular and substantive interaction" between faculty and students. One of the things they would like reports on is on instructor's feedback to discussion forums/threads. This is being requested because of the possibility of DOE audits. It appears that the discussion_stream_event (dse) table only has data for the past 7 days. So, I've been trying to figure out how to pull this information without using the dse table. This is what I have:
/*This query gives the course_id, the name of the DB forum, the msg_main pk1, the msg_main.msgmain_pk1 which is
refering back to the msg_main pk1 it's associated with, the user_id of the user that posted the message, the
subject or title of the message, the date it was posted, and the text of the message. This is good, but I would
like to limit or filter this to only show the date the original thread was made, the date the instructors replied
to that thread, and the text of te reply.*/
SELECT cm.course_id,fm.name DBForum,mm.pk1,mm.msgmain_pk1,u.user_id,mm.SUBJECT,mm.POSTED_DATE,mm.MSG_TEXT
FROM conference_main cfm
JOIN course_main cm ON cm.pk1 = cfm.crsmain_pk1
JOIN forum_main fm ON fm.confmain_pk1 = cfm.PK1
JOIN msg_main mm ON mm.forummain_pk1 = fm.PK1
JOIN users u ON mm.users_pk1 = u.PK1
WHERE cm.course_id = '2018SP_BIO-211-W02'
ORDER BY cm.COURSE_ID,fm.NAME, mm.SUBJECT;
I was able to pull the date like this for blogs using these two queries:
/*This query gives the course_id, the Blog name, The user_id that created the blog entry,
the date the blog entry was created, and the blog text.*/
SELECT cm.course_id, b.title Blog, u.USER_ID, be.title Blog_Entry_Title, be.CREATION_DATE, be.DESCRIPTION Blog_Post
FROM blogs b
JOIN course_main cm ON cm.pk1 = b.crsmain_pk1
JOIN blog_entry be ON be.blog_pk1 = b.pk1
JOIN course_users cu ON be.CREATOR_USER_ID = cu.PK1
JOIN users u ON cu.USERS_PK1 = u.PK1
WHERE cm.course_id = '2018SP_ENG-205-001'
ORDER BY b.TITLE;
/*This query shows the course_id, Blog Title, and the comments posted by the instructor to the Blog along with the date and time the comment was posted. */
SELECT cm.course_id, b.title Blog, u.USER_ID, be.title Entry_Title, bc.CREATION_DATE, bc.DESCRIPTION Blog_Comment
JOIN blog_comment bc ON bc.blog_entry_pk1 = be.pk1
JOIN course_users cu ON bc.CREATOR_USER_ID = cu.PK1
AND cu.role = 'P'
ORDER BY b.title, be.TITLE;
It may not be perfect but this should be close.
For clarity, I used "post" (mm2) to identify the original message and "reply" (mm) to identify the instructor's response
Two primary additions.
1. Need to join in the course_users table so you can filter on role='P' for instructors
2. Need to join msg_main back on itself, so you can get the date of the post.
fm.name as "FORUM_NAME",
mm2.pk1 as "POST_PK1",
mm2.posted_date as "POST_DATE",
mm.pk1 as "REPLY_PK1",
mm.POSTED_DATE as "REPLY_DATE",
u.user_id as "INSTRUCTOR_ID",
mm.MSG_TEXT as "REPLY_TEXT"
JOIN msg_main mm2 on mm.MSGMAIN_PK1 = mm2.pk1 --join msg_main back on itself
JOIN course_users cu on u.pk1 = cu.users_pk1 and cm.pk1 = cu.crsmain_pk1
WHERE cm.course_id like '%bio%' and cu.role = 'P'
Retrieving data ...