SRC: txt file downloaded from https://community.blackboard.com/docs/DOC-2351-common-queries

Bb

/*
Common Queries for Beginners
December 17, 2010 12:01 PM
Hello Everyone. Below is a list of common queries a gentlemen at Blackboard Managed Hosting gave me a while back. This list of queries is a great starting point to get to know the ASR side of Blackboard. There are a few things you need to be aware of before copying and pasting these into your favorite query analyzer:

Aqua Data Studio does not end its statements with semicolons like this ;

Some queries may not work without modification.

If you feel like the query you are running is going to pull a large amount of data, use where rownum <= 10 at the end of your statement, this will limit your findings to 10 rows of data.

The formatting of the query may need to be changed.
--------------------------------------------------
*/



--List course ID's without activity in the last 90 days. 

select course_id from course_main where pk1 not in (select distinct course_pk1 from activity_accumulator where timestamp > sysdate-90) 



--Students with no course enrolment

SELECT user_id,pk1 FROM users u WHERE NOT EXISTS (SELECT pk1 FROM course_users WHERE users_pk1=u.pk1 AND role='S');

/*
This yields all that are not in a student role in any course - you can easily modify this to get those with no course_user record at all, or to exempt those with row_status=0 only (enabled enrollments). 

The query below will give you all users in the system who are not enrolled in a course; you can modify the query as you please.
*/
 
select firstname, lastname, user_id, pk1 from users where pk1 not in ( select users_pk1 from course_users) order by pk1 ASC



--List Unique users of a desired role in a course with activity between two dates

select unique u.user_id from users u, course_users cu, activity_accumulator aa where u.pk1=cu.users_pk1 and u.pk1=aa.user_pk1 and aa.timestamp > '01-JUL-07' and aa.timestamp < '30-JUN-08' and cu.role in ('P','B','T','G');



--List Student role users with activity in courses between two dates.

select unique u.user_id from users u, course_users cu, activity_accumulator aa where u.pk1=cu.users_pk1 and u.pk1=aa.user_pk1 and aa.timestamp > '01-JUL-07' and aa.timestamp < '30-JUN-08' and cu.role='S'; 



--List users with activity between two dates. 

select u.user_id from users u, course_users cu, activity_accumulator aa where u.pk1=cu.users_pk1 and u.pk1=aa.user_pk1 and aa.timestamp > '01-JAN-08' and aa.timestamp < '10-MAY-08' and cu.role='S';


--List courses created between two dates. 

select course_id from course_main where dtcreated > '01-JUL-07' and dtcreated < '30-JUN-08'; 


--List courses depending on modification date.

select course_id||','||course_name||','||dtmodified from course_main where DTMODIFIED < to_date('08-01-2004','MM-DD-YYYY')

 /*
 Querying the dtmodified on course_main only determines courses where certain properties are changed, such as the course name, description, etc.  It does not pick up the majority of changes and it certainly does not pick up course accesses.
 */



/*
Query that will display all courses that do NOT have the following roles in them:

P = Instructor
B = course builder
T = TA aka Teacher That Helps Teacher
G = Grader
*/
 
select pk1||','||course_name||','||course_id from course_main minus (
select pk1||','||course_name||','||course_id from course_main where pk1 in (select distinct crsmain_pk1 from course_users where role in ('P','T','B','G'))); 



--List User Information by role. 

Select users.data_src_pk1, users.batch_uid, users.user_id, system_roles.system_role, institution_roles.role_id, users.row_status, users.email, users.firstname, users.middlename, users.lastname, users.company FROM users,institution_roles,system_roles WHERE role_id='ECPD'; 



--List courses and enrolled users by role.  

SELECT users.user_id,course_main.course_id
From users,course_main,course_users
where users.pk1=course_users.users_pk1
AND course_users.role='T'
AND course_users.crsmain_pk1=course_main.pk1 



--List Student Last Login date by course batch_uid/external course key.

SELECT user_id, users.last_login_date
FROM course_users, course_main, users
WHERE users.last_login_date is not null
AND course_users.users_pk1 = users.pk1
AND course_main.pk1 = course_users.crsmain_pk1
    AND (course_main.batch_uid like '%F2008'
         or course_main.batch_uid like '%Y2009')
    AND course_users.role = 'S'
ORDER BY user_id; 



--List duplicate users. 

select lastname,firstname,user_id,DATA_SRC_PK1
from users
where (lastname,firstname)
in
(SELECT lastname,firstname
FROM users
GROUP BY lastname, firstname
HAVING ( COUNT(1) > 1 ) )
order by lastname,firstname,user_id,data_src_pk1 



--List of courses without events in Activity Accumulator. 

select course_id from course_main where pk1 not in (select distinct course_pk1 from activity_accumulator where timestamp > sysdate-90) 



--List of courses by last access date. 

SELECT cm.course_id||' : '||cm.course_name AS COURSE,
TO_DATE(cr.registry_value,'MM-DD-YYYY:HH24::MI:SS') AS LAST_ACCESS,
u.user_id||' - '||u.firstname||' '||u.lastname AS INSTRUCTOR FROM course_main cm LEFT OUTER JOIN course_registry cr ON (cm.pk1=cr.crsmain_pk1 AND cr.registry_key='last access') LEFT OUTER JOIN course_users cu ON (cm.pk1=cu.crsmain_pk1 AND cu.role='P' AND cu.row_status=0) LEFT OUTER JOIN users u ON (u.pk1=cu.users_pk1 AND u.row_status=0) WHERE (cr.registry_value IS NULL OR TO_DATE(cr.registry_value,'MM-DD-YYYY:HH24::MI:SS') < sysdate - 365) ORDER BY 2 DESC NULLS LAST; 



--Last access of a course by date querying activity accumulator

select course_id, course_name,DTCREATED, LAST_ACCESS, nvl(trunc(DISK_USAGE / 1024),0),lastname,firstname from course_main, (SELECT * FROM  (SELECT crsmain_pk1, max(decode(registry_key,'last access',registry_value)) LAST_ACCESS, sum(decode(registry_key,'disk_usage',registry_value)) DISK_USAGE FROM course_registry
GROUP BY crsmain_pk1)
) subq,
(select cu.crsmain_pk1,u.firstname,u.lastname,u.user_id from
course_users cu,users u
where u.pk1=cu.users_pk1
and  cu.role='P'
) profs
where course_main.pk1=subq.crsmain_pk1
and   course_main.pk1=profs.crsmain_pk1
order by nvl(trunc(DISK_USAGE / 1024),0) desc 



--List of users by last login date, excluding internal accounts.

select USER_ID, LASTNAME, FIRSTNAME, LAST_LOGIN_DATE from users
where USER_ID!='integration' and USER_ID!='root_admin' and
USER_ID!='bbsupport' and USER_ID!='guest'and last_login_date < '01-SEP-05'
order by LAST_LOGIN_DATE

 


--List students and last login by course external key.

SELECT user_id, users.last_login_date
  FROM course_users, course_main, users
  WHERE users.last_login_date is not null
    AND course_users.users_pk1 = users.pk1
    AND course_main.pk1 = course_users.crsmain_pk1
    AND (course_main.batch_uid like '%F2008'
         or course_main.batch_uid like '%Y2009')
    AND course_users.role = 'S'
ORDER BY user_id; 



--List top 10 active users by institution role, and LOGIN_ATTEMPTS between two dates 

select /*+ no_index( ACTIVITY_ACCUMULATOR
ACTIVITY_ACCUMULATOR_IE3) */ mycount, user_id
from ( select /*+ no_index(ACTIVITY_ACCUMULATOR
ACTIVITY_ACCUMULATOR_IE3) */ count(1) mycount ,user_id
from activity_accumulator , users
where event_type='LOGIN_ATTEMPT'
and timestamp between
to_date('AUG-01-2008','MON-DD-YYYY') and
to_date('DEC-31-2008','MON-DD-YYYY')
and user_pk1 is not null
and user_pk1 != 6
and user_pk1 = users.pk1
and institution_roles_pk1=2
group by user_id
ORDER BY COUNT(1) desc
) where rownum <= 10



--List top 10 active users from a specific institution_role by number of hits between two dates.

select /*+ no_index( ACTIVITY_ACCUMULATOR
ACTIVITY_ACCUMULATOR_IE3) */ mycount, user_id
from ( select /*+ no_index(ACTIVITY_ACCUMULATOR
ACTIVITY_ACCUMULATOR_IE3) */ count(1) mycount ,user_id
from activity_accumulator , users
where
timestamp between
to_date('AUG-01-2008','MON-DD-YYYY') and
to_date('DEC-31-2008','MON-DD-YYYY')
and user_pk1 is not null
and user_pk1 != 6
and user_pk1 = users.pk1
and institution_roles_pk1=2
group by user_id
ORDER BY COUNT(1) desc
) where rownum <= 10



--List the top 10 active courses of a specific ID, by hits tracked between two dates.

select /*+ no_index( ACTIVITY_ACCUMULATOR
ACTIVITY_ACCUMULATOR_IE4) */ mycount, course_id
from ( select /*+ no_index(ACTIVITY_ACCUMULATOR
ACTIVITY_ACCUMULATOR_IE4) */ count(1) mycount ,course_id
from activity_accumulator , course_main
where timestamp between
to_date('AUG-01-2008','MON-DD-YYYY') and
to_date('DEC-31-2008','MON-DD-YYYY')
and course_pk1 is not null
and activity_accumulator.course_pk1=course_main.pk1
and (course_id like '%f08' or course_id like
'%F08')
group by course_id
ORDER BY COUNT(1) desc
) where rownum <= 10



--Courses with Discussions. 

SELECT DISTINCT pk1, course_id FROM course_main
WHERE pk1 IN
  (SELECT course_pk1 FROM conference_by_course cc, forum_main fm
  WHERE fm.confmain_pk1=cc.conference_pk1);
 

--Amount of Forums in Courses. 

SELECT cm.pk1, cm.course_id, count NUM_FORUMS
FROM course_main cm, conference_by_course cc, forum_main fm
WHERE cm.pk1=cc.course_pk1 AND cc.conference_pk1=fm.confmain_pk1
GROUP BY cm.pk1, cm.course_id
ORDER BY 3 DESC;
/*
You may also want to differentiate by conference (the course conference aka discussionboard is one thing, and always has the course_id as name; a group conference is another thing, and carries the group name), you may want to use this one:

SELECT cm.pk1, cm.course_id, cf.name, count NUM_FORUMS
FROM course_main cm, conference_by_course cc, conference_main cf, forum_main fm
WHERE cm.pk1=cc.course_pk1 AND cc.conference_pk1=fm.confmain_pk1 AND cc.conference_pk1=cf.pk1
GROUP BY cm.pk1, cm.course_id, cf.name
ORDER BY 4 DESC;

Finally if you want to rather get a count of messages per forum and not a count of forums you could add msg_main mm as additional join table, with the condition mm.forum_pk1=fm.pk1, and grouping by fm.pk1 - the exact syntax is left as an exercise to the esteemed reader.  
*/



--Courses without users. 

select course_id,course_name,row_status from course_main
where pk1 IN
(select crsmain_pk1 from course_users
where crsmain_pk1 IN
(select distinct(crsmain_pk1) from course_users
minus
select distinct(crsmain_pk1) from course_users where ROLE =
'P'))
and course_id != 'SYSTEM'; 


--A report of all course ID's in which no users at all are enrolled.

select course_id,course_name,row_status from course_main
where pk1 NOT IN (select distinct(crsmain_pk1) from course_users)
and course_id != 'SYSTEM'; 


--A report of the last login dates for all users.
select user_id,to_char(max(timestamp),'DD-MON-YYYY HH24:MI:SS') from users
u,activity_accumulator aa where user_pk1 =u.pk1 group by user_id 



--A list of users, courses and what title who have html tags in a course menu navigation button.

select a.course_id, a.course_name, b.title, c.user_id, c.Firstname, c.lastname
from course_main a, course_contents b, users c, course_users d
where d.Crsmain_pk1=a.pk1
And d.users_pk1=c.pk1
And a.pk1=b.crsmain_pk1
And b.title like '%>%'
And d.role='P'  

 

--What is the query for the  ACTIVE USER count on behind the blackboard on the INSIGHT REPORT

 SELECT COUNT(1)
   FROM users
  WHERE pk1 IN
  (SELECT users_pk1
     FROM course_users
    WHERE row_status= 0
  AND available_ind ='Y'
  and crsmain_pk1 in (select pk1 FROM course_main where row_status= 0 and available_ind='Y')
  )
AND row_status   =0
AND available_ind='Y';  

 

--Report of users not enrolled in a course or an organization

select user_id from users where pk1 not in (select unique users_pk1 from course_users)  

 

--# of Unique Courses being accessed

select count(distinct course_pk1) from activity_accumulator where timestamp between to_date ('MAR-01-2009','MON-DD-YYYY')
and to_date('APR-01-2009','MON-DD-YYYY'); 



--# of Unique Students accessing the system and # of Unique Instructors accessing the system
--currently existing users (Note this is pulling for the activity_accumulator table. So if a users been delete it'll still show until they have been purged out of the AA table, typically after 180 days.)

select count(distinct user_pk1),role_name from
activity_accumulator aa,users u, institution_roles ir
where aa.user_pk1=u.pk1
and ir.pk1=u.institution_roles_pk1
and aa.timestamp between to_date ('MAR-01-2009','MON-DD-YYYY')
and to_date('APR-01-2009','MON-DD-YYYY')
group by role_name;



--Total # of users per month

select count(distinct user_pk1) from activity_accumulator where timestamp between to_date ('MAR-01-2009','MON-DD-YYYY')
and to_date('APR-01-2009','MON-DD-YYYY');  

 

--Free space" reports data files free space        

select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
,      round(free.p / 1024 / 1024) || ' MB' "Free space"
from (select bytes from v$datafile
      union all
      select bytes from v$tempfile
      union all
      select bytes from v$log) used
,    (select sum(bytes) as p from dba_free_space) free
group by free.p 



--Database Uptime
SELECT TO_CHAR(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;



--Overall Database Size

select sum(bytes)/1024/1024 "Meg" from dba_data_files;