Mobile Usage Activity

This page contains activity accumulator SQL queries for the Blackboard Learn database so that you can obtain activity information for Blackboard mobile usage. This information assumes you already have the ability to access your database and permissions to query information. These queries are designed for reporting purposes so there is no need for write permissions.

We recommend to use an account with read-only access to prevent accidental changes. Making changes directly to the database for any complex system can result in significant problems, including downtime. The queries listed on this page in this document do not make any changes to the system.

SQL query code samples are designated in this page. These queries should give you ideas for how additional things work. There are a few limitations in the way data is recorded. Currently, we can't determine from which app or platform any particular event was performed. This means that when looking at the data, Blackboard app and Blackboard Instructor all appear the same.

In the SQL query samples bblearn is used as the sample database schema. The Blackboard Learn database could have the data stored in a number of alternative schemas such as bb_bb60 or bblearn_stats for archived data. The first part of the query might need to be altered to take schema variations into account.

Count the total number of mobile logins for a specific time period

This query shows the number of times any user logged in to Blackboard Learn from any app on any device. Each time a user logs in it counts as one time. If a user is disconnected repeatedly in an area of bad reception, many logins could be reported, even though a user with automatic login will see it as one session from their device. This sample looks at one month of data.

select Count(*) from bblearn.activity_accumulator
where TIMESTAMP > '01-MAR-2016'
and TIMESTAMP < '01-APR-2016'
and data like '%Mobile Login%'

The activity accumulator table in the Blackboard Learn database records Mobile Login Attempt multiple times for every mobile app user login with Force to Web authentication. More on this known issue.

Count the number of unique users for a specific time period

This query shows the number of unique users that logged in during a specific time period. Since it uses the database ID associated with a login, regardless of how many different apps or devices used during this time period, the user will only show as one user. For example, if you are using this query repeatedly to build a month by month report, the user will be unique to each query. This means that each time you run it, each user is counted once. Each user is counted for each different month they logged in, but only once per month. This sample looks at one month of data.

select Count(Distinct(user_pk1)) from bblearn.activity_accumulator
where TIMESTAMP > '01-MAR-2016'
and TIMESTAMP < '01-APR-2016'
and data like '%Mobile Login%'

Count the number of unique user logins by course role

This query shows the number of unique users that logged in by their course role. It joins the activity_accumulator table with the course_users (enrollments) table. Joining the course_users table, which contains each user's course or organization role, can provide a rough estimate of the amount of users accessing the Blackboard and Blackboard Instructor apps. The same guidelines that apply to "count the number of unique users for a specific time period" apply to this query. This sample returns the number of unique instructor mobile logins for one month.

select Count(Distinct(user_pk1)) from bblearn.activity_accumulator aa
join bblearn.course_users cu on
aa.user_pk1 = cu.users_pk1
where cu.role = 'P'
and timestamp > '01-NOV-2018'
and timestamp < '30-NOV-2018'
and aa.data like '%Mobile Login%';

Note that for cu.role, the course or organization "ROLE ID" must be used. Also, some users may log in to the incorrect app. For example, an instructor may log in to the Blackboard app by mistake. Take this into account when analyzing the results.

Show all of the individual logins for a specified time period

This query is effectively the same as the "count the total number of mobile logins for a specific time period" query, with the distinction that it shows all of the raw data rather than just a count of the number of rows. This can be helpful if you are looking for patterns, or planning to export the data to be manipulated for deeper reports. The '*' character in the first line means that it will show all columns from the table.

select * from bblearn.activity_accumulator
where TIMESTAMP > '01-MAR-2016'
and TIMESTAMP < '01-APR-2016'
and data like '%Mobile Login%'

Show all of the individual times users viewed forum threads in a specific time period

This query, rather than looking at login data like the Mobile Analytics page, looks instead at what students are doing in the app. Whenever someone uses the Blackboard app to view a thread on a forum, the building block records a 'mobile.view.thread' item in the activity_accumulator table. This query shows when any student viewed any thread in any forum. If this data is combined with data from other tables or manipulated in a program such as Microsoft Excel, information about specific students, courses, and usage patterns can be determined.

select * from bblearn.activity_accumulator
where TIMESTAMP > '01-MAR-2016'
and TIMESTAMP < '01-APR-2016'
and data = 'mobile.view.thread'

Show all mobile usage

The following query pulls all Mobile items for a specific month. However, the query could include results for pages that simply contain the word "Mobile." While this information is useful, it may be better to convert the data into another format to find usage patterns. For example, see which items, that have recorded usage, are most commonly accessed from mobile devices.

select * from bblearn.activity_accumulator
where TIMESTAMP > '01-MAR-2016'
and TIMESTAMP < '01-APR-2016'
and data like '%Mobile%'

Advanced queries

The following queries include some of the basics from above and show ways to get more granular, or put data together to make it more readable.

Querying Blackboard users

This query includes all of the data in the users table. This includes each user's username and associated PK Value. This is all users within Blackboard Learn regardless of whether or not they use any of Blackboard's mobile products.

select *
from bblearn.users

Limit your query to specific columns

The queries in this page all used a '*' designator in the part of the query that lets you specify columns. This causes SQL to return all columns in the table, regardless if they provide any real value to you. The following query returns all lines from the activity_accumulator table, but it only returns 5 columns. These not only let us identify the user, course, and content item; they also give us the information from the data field and the timestamp from when the activity happened. It does not display any of the other columns.

select user_pk1, course_pk1, content_pk1, data, timestamp
from bblearn.activity_accumulator

Join multiple tables together

If you combine results from two tables it can help you filter, limiting your data to the relevant users. You can also pull in the user name to directly associate users with activities so that you don't have to look it up manually. SQL uses joins to combine the results from multiple tables. A join is a where statement that lets SQL know that the value from one table is equal to the same value in another table.

The following query is an example of an SQL Join that uses our tables. Joins can be difficult and sometimes require a lot of trial and error. If plan to do more complicated queries, it is recommended you learn about Joins. More complicated queries can require more work from the database server to process and have the potential for more to go wrong.

Here is a line-by-line example, as well as a good reference for building other queries. This query combines multiple pieces, introduced above, into one query that gives only specific columns from multiple tables with several limitations set.

1. Specify the columns from two different tables. The table names are defined after the columns.
2. Pick two tables and give them single-letter nicknames.
3. Where is the place to put conditions. In SQL, the explanation of how the tables are related is considered a condition. So you need to specify which columns are equivalent so that SQL knows how to relate the data together.
4. Additional condition: timestamp start date
5. Additional condition: timestamp end date
6. Additional Condition: data column limiter

select u.user_id, a.data, a.timestamp
from bblearn.activity_accumulator a, bblearn.users u
where u.pk1=a.user_pk1
and a.TIMESTAMP > '01-MAR-2016'
and a.TIMESTAMP < '01-APR-2016'
and a.data like '%Mobile Login%'

Similar to the query, a condition could be added to the user table such as u.user_id = 'StudentLogin'. This allows a query to run against the activity_accumulator table without having to figure out the users PK1 value, as long as the student's actual username was obtained.

Other tables can be joined. The more conditions and joins added, the longer the query takes, but better data may be achieved as a result. Instead of having many database IDs, as put out by the activity_accumulator by default, the content name and course names can be populated to the query.

Reference data

Mobile events

The following events are recorded by the Mobile Web Services building block in the activity_accumulator table. This list can change over time. As events are recorded by the building block, not the apps themselves, the format of the data is the same regardless of which app was used to perform the function. In the previous query for thread views, the 'mobile.view.thread' parameter can be replaced with any of the entries from the table below if the entry contains extra text besides the mobile listing itself. There are some instances where there are different entries for the same action. This is because the recording format changed at some point so you may have to search for both entry types to get results over time.

Supported Content Types in the Blackboard App

Event	Entry in activity_accumulator table 'Data' Field
view blog list	mobile.list.blogs
view post list	mobile.list.blog.entries
view post comments	mobile.list.blog.comments
add journal comment	mobile.list.journal.add.entry.comment
add blog comment	mobile.list.blog.add.entry.comment
add journal entry	mobile.list.journal.add.entry
add blog entry	mobile.list.blog.add.entry
view journal list	mobile.list.journals
view post list	mobile.list.journal.entries
view post comments	mobile.list.journal.comments
view forums	mobile.list.forums
view threads	mobile.list.threads
view thread	mobile.view.thread
view thread posts	mobile.view.thread.post
view announcements	mobile.view.announcements
view roster	mobile.view.roster
view grades	mobile.view.grades
add journal entry	mobile.add.journal.entry
add db post	mobile.add.thread.post
add db post comment	mobile.add.thread.post.comment
view content item	Mobile Content View