Is there a sql query that can give me a list of all the users in the system and if possible also the users per course. preferably at least just the username, but more info will also help.
Sure. How familiar are you with SQL? The users are all in a table called "users", so
select * from users
would give you all the users. If you wanted to narrow it down to username, first name and last name, and email, with only users whose accounts are enabled (row_status = 0, that is, not disabled by the snapshot process), you could do something like
select user_id, lastname, firstname, email from users where row_status = '0'
The enrollments and staff assignments are in a table called "course_users", and it links to the "users" table for user information and "course_main" table for course information, using the primary key (pk1) for each table. So, a sql that pulls the course_id from course_main and the user_id from the users table for all the non-disabled records in course_users and arranges them by course_id would look something like:
select course_main.course_id, users.user_id
from course_users join course_main on (course_main.pk1 = course_users.crsmain_pk1)
join users on (users.pk1 = course_users.users_pk1)
where course_users.row_status = '0'
order by course_main.course_id
Does that help?
Thank you for the info. It worked fine. I appreciate the help.
Glad to help!
Hope all is well. I had a question, i thought you might have the answer to. How do i get the number of hits per faculty from the activity accumulator table for a given course ID for a given time period (ie. between jan 2011 to feb 2011)?
Thank you in advance,
You can get information like the "number of hits" by a particular user or users in a particular class over a particular time period by simply doing a Course Report. That's querying the activity accumulator table looking for access by that user in that class.
I found this code:
select to_char(to_date(c.thisdate,'YYYYMM'), 'Month DD') as month, count(*)-1as hitsfrom (select to_char(aa.timestamp,'YYYYMM') as thisdatefrom activity_accumulator aawhere aa.timestamp > sysdate-365
and aa.user_pk1=10834and aa.course_pk1=174132
select to_char(b.testdate,'YYYYMM') as thisdatefrom (select add_months(sysdate-365,a.rnum -1) as testdatefrom (select rownum rnum from course_main where rownum <= 12)a)b)c
group by to_char(to_date(c.thisdate,'YYYYMM'), 'Month YY'), c.thisdateorder by c.thisdate;
What database server are you using? I don't know about Oracle, but if you're using SQL Server, that code has a lot of unnecessary date/time conversions and system date comparisons, and doesn't figure out the proper keys for identifying the user or course by id. All you need to do is count the activity_accumulator records with a particular course and user that fall between two dates. A simpler version would be something like:
select(count(pk1)) from activity_accumulator aa where
aa.course_pk1 = (select pk1 from course_main where course_id = '<desired course id>')
and aa.user_pk1 = (select pk1 from users where user_id = '<desired user id>)
and aa.timestamp > '2010-12-31' and aa.timestamp < '2011-02-01'
Hope all is well. I had one more question for you :). Do you know which table(s) I can query to determine which of our team members has "Quick Enrolled" in a course?
Thank you in advance;
Not really. There's nothing in the database record for an enrollment that tells you for sure that it was done via "Quick Enroll", and the process does not create a record in the activity accumulator database. The only way you can find a "Quick Enroll" is to look in the access logs (blackboard\logs\tomcat\bb-access-log-<date>.txt) and look for entries that say "/webapps/blackboard/execute/doCourseMenuAction?method=quickEnrollToggle". The line will also contain a field that tells you the pk1 of the user, and of the course.
Hi Mike, Bob,
Sorry for only getting back to you guys now, have been busy with second semester enrollments of students. @Mike: While we're on the subject of log files, is there a way to identify the string in the logs that tell you when a course has been deleted. I know there is a string that shows when a course has been modified (although I'm not sure what it should look like ), but is it possible to derive from that string exactly which course and whether it has been deleted and/or created.
Thanx in advance
If we look at the Blackboard UI and you click on a course link, it will tell you if you are "Quick Enrolled" in that course. Is the Blackboard UI actually looking in the access log file: blackboard\logs\tomcat\bb-access-log-<date>.txt to get this data?
I would think that would be a bit inefficient. Since that could have easily been a column in some table
Where are you seeing this UI indicator that your enrollment in a class is from a "Quick Enrollment"?
When I click on a course and I get to the next page, I see a link in the bottom left hand side of the screen that says "Quick Enroll". When I enroll, it changes to "Quick Unenroll". The next time when i go into the same course, I still see "Quick Unenroll". Therefore, Blackboard is housing this info in a table somewhere (i am assuming). And this is linked with the USER ID and Course ID. If you like, I can send you a screen shot.
You see "Quick Unenroll" because you have "Quick Enroll" (and "Quick Unenroll") privileges, and because you're enrolled in the class. You would see the same thing no matter how you were originally enrolled in the class, whether it was via "Quick Enroll", or snapshot, or batch enrollments, or any other means.
© Blackboard, Inc.