Let me start by saying how much I appreciate the Developer Virtual Machine and the work that Blackboard has put into optimizing the install and upgrade process of Learn.

Unfortunately, I cannot use the DVM directly on my machine because I already have Hyper-V in use. It would require bcdedit & a reboot to toggle hypervisors.

To get going "quickly," I used VirtualBox's tools to convert the .vmdk to a .vhd. Then I manually attached the hard disk to a custom VM in Hyper-V. It works perfectly with the exception of shutting down/rebooting. The VM has to be reset again to get the bootloader to fire. My DVM eventually needs to be rebooted after repeatedly deploying a building block.

Our production and test environments are inside Blackboard's managed hosting, so going into this I had 0 experience with the server-side operations of Learn.

Once my project was done, I started to get curious. Now that I'll need to test my building block regularly, how do I install a signed certificate so that testers' browsers stop complaining?

• The official documentation briefly goes over installing a signed certificate on a single-server install, but it leaves out the fact that the keystore password is now encrypted.

The DVM available was out of date. How do I upgrade it?

• The upgrade process requires an installer.properties that was not left behind by the creator of the DVM. I found a blog post Upgrade Your DVM that includes everything you need to upgrade to the latest Learn version.

Ultimately the DVM is designed to be a throw-away install, but I need it to last longer and be a tad more secure. So I've documented the install process from scratch.

You will need:

• The license file from a pre-existing install of the DVM
  • /usr/local/blackboard/config/license/blackboard-license.xml - Developer Virtual Machine - DVM
• CentOS 7 x64 (Minimal) iso
  • CentOS-7-x86_64-Minimal-XXXX.iso - https://www.centos.org/download/
• Oracle JDK 8 (Linux x64 rpm)
  • jdk-8uXXXXX-linux-x64.rpm - https://www.oracle.com/technetwork/java/javase/downloads/index.html
• Learn Installer zip
  • learn-installer-XXXXX.zip - from Behind the Blackboard

RHEL 7.5 is a supported operating system, and I am familiar with it, so I am using CentOS.

You will also need a way to transfer files to the server. There are several ways including using scp, wget from a [local] server, mounting a share, or configuring samba.

With the hard drive capacity set to 20 GB, it will be 52% full after Learn installation. If you delete the installer.zip, it will be 43% full. If you delete the installer directory, it will be 27% full.

(Scenario A) Hyper-V Virtual Machine Setup

I use Hyper-V Manager on Windows 10 Pro.

Manually create a new Virtual Machine

• under Specify Generation: select Generation 2
• under Assign Memory: set 4096 MB, DISABLE the option "Use Dynamic Memory for this virtual machine"
• under Configure Networking: select external switch
• under Connect Virtual Hard Disk: create a virtual hard disk with 20 GB
• under Installation Options: select Install an operating system from a bootable image file, and select .iso

Edit the newly created VM Settings

• under Security: DISABLE the option "Enable Secure Boot"
• under Processor: increase the "Number of virtual processors" (at least 2)
• under Network Adapter: verify settings are correct (I need to set a VLAN id in my environment)
• under Checkpoints: DISABLE the option "Use automatic checkpoints" - unless you want them!

(Scenario B) vSphere 6.5 (ESXi) Virtual Machine Setup

Manually create a new Virtual Machine

• under Select a guest OS: select "Linux" family, select "CentOS 7 (64-bit)" version
• under Customize hardware:
  • CPU: 2, cores per socket
  • Memory: 4 GB
  • New Hard disk: 20 GB
  • New Network: as needed
  • New CD/DVD Drive:
    • if you uploaded the iso to a datastore, select Datastore ISO file and ENABLE the option "Connect At Power On"
    • otherwise, select Client Device - you must manually connect the iso from the remote console

Starting the server build

Start the VM

• The iso's bootloader should fire. You might have to play with the boot order.
• Select Install CentOS 7

CentOS installer

• under Network & Host Name:
  • click ON to enable Ethernet
  • set the Hostname if desired - make sure you click apply
• if you are not using the Minimal installer iso:
  • under Software Selection: select Minimal Install

During install

• under Root Password:
  • set Password as needed
• under User Creation
  • set "Full name" to bbuser
  • set "User name" to bbuser
  • ENABLE the option "Make this user administrator"
  • set Password as needed

Reboot

Post OS install

If you need to manually set a static IP, using the console, login as bbuser

$ sudo nmtui

• Edit a connection
  • set IPv4 - manual (did not test with IPv6 - ignore)
• Activate a connection
  • toggle state for IP changes to take affect immediately

Login as bbuser using SSH

(optional) configure the wheel group to not require a password for sudo

$ echo '%wheel ALL=(ALL) NOPASSWD: ALL' | sudo tee /etc/sudoers.d/wheel

Update to the latest packages

$ sudo yum update -y

Install the needed packages

$ sudo yum install open-vm-tools wget unzip postgresql-server postgresql-contrib policycoreutils-python

Transfer the latest Oracle JDK 8 for Linux x64 rpm to server (can be deleted later to reclaim space)

• You can copy the link from the Oracle Downloads page then:
  • $ wget --header 'Cookie: oraclelicense=accept-securebackup-cookie' https://download.oracle.com/otn-pub/java/jdk/.../jdk-8uXXXXX-linux-x64.rpm

Install the jdk8 rpm

$ sudo yum localinstall jdk-8uXXXXX-linux-x64.rpm

Add a missing shared library for Java (the rpm does not include the link).

• You can confirm this by running $ ldd `which java`

$ echo '/usr/java/latest/jre/lib/amd64/jli' | sudo tee /etc/ld.so.conf.d/java.conf; sudo ldconfig

Add JAVA_HOME (not required, but recommended) and PGDATA (required, location of DB) to default environment

$ echo -e '#!/bin/bash\nexport JAVA_HOME=/usr/java/latest\nexport PGDATA=/usr/local/bbdata' | sudo tee /etc/profile.d/bb-environment.sh

(optional) prepare for publickey auth

$ mkdir --mode=750 ~/.ssh; touch ~/.ssh/authorized_keys; chmod 640 ~/.ssh/authorized_keys

• add your keys

Configure OpenSSH to only allow bbuser to login (no root)

$ echo -e 'AllowUsers bbuser' | sudo tee -a /etc/ssh/sshd_config

(option A) Redirect 8080/8443 to 80/443

$ sudo firewall-cmd --permanent --add-masquerade; sudo firewall-cmd --permanent --add-forward-port=port=80:proto=tcp:toport=8080; sudo firewall-cmd --permanent --add-forward-port=port=443:proto=tcp:toport=8443

(option B) Open 8080/8443

$ sudo firewall-cmd --permanent --add-port=8080/tcp; sudo firewall-cmd --permanent --add-port=8443/tcp

(optional) Allow postgresql remotely

$ sudo firewall-cmd --permanent --add-service=postgresql

(optional) Allow tomcat debugging remotely

$ sudo firewall-cmd --permanent --add-port=2222/tcp

(optional) Configure postfix if you need local mail configuration

$ sudo vi /etc/postfix/main.cf; sudo systemctl enable postfix

Set max number of open file descriptors

$ echo -e '@bbuser soft nofile 5000\n@bbuser hard nofile 5000' | sudo tee -a /etc/security/limits.conf

Overwrite default PGDATA environment variable for postgresql service

$ echo -e '.include /lib/systemd/system/postgresql.service\n[Service]\nEnvironment=PGDATA=/usr/local/bbdata' | sudo tee /etc/systemd/system/postgresql.service

Create PGDATA directory and assign ownership to postgres

$ sudo mkdir -p /usr/local/bbdata; sudo chown postgres:postgres /usr/local/bbdata

Relabel PGDATA's SELINUX context

$ sudo semanage fcontext -a -s system_u -t postgresql_db_t '/usr/local/bbdata(/.*)?'; sudo restorecon /usr/local/bbdata

(if ever needed) Disable SELINUX

• temporarily:
  • $ sudo setenforce permissive
• permanently, requires reboot
  • $ sudo sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

Reboot so that environment, firewall, and other settings take affect

$ sudo reboot

Login as bbuser using SSH

Initialize postgresql db; enable and start service

$ sudo postgresql-setup initdb; sudo systemctl enable postgresql; sudo systemctl start postgresql

Launch into psql as postgres

$ sudo -i -u postgres psql

From the postgresql shell paste in:

• ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'postgres';
• CREATE ROLE "BBLEARN" WITH LOGIN ENCRYPTED PASSWORD 'postgres';
• CREATE ROLE "BBLEARN_admin" WITH LOGIN ENCRYPTED PASSWORD 'postgres';
• CREATE ROLE "BBLEARN_cms" WITH LOGIN ENCRYPTED PASSWORD 'postgres';
• CREATE ROLE "BBLEARN_cms_doc" WITH LOGIN ENCRYPTED PASSWORD 'postgres';
• CREATE ROLE "BBLEARN_stats" WITH LOGIN ENCRYPTED PASSWORD 'postgres';
• CREATE ROLE "BBLEARN_report" WITH LOGIN ENCRYPTED PASSWORD 'password';
• \q

Configure postgresql authentication to require a password instead of using "current user"

$ sudo sed -i -e 's/ ident$/ md5/' -e 's/ peer$/ md5/' $PGDATA/pg_hba.conf; sudo systemctl restart postgresql

Add blackboard service to systemd; enable it

$ echo -e '[Unit]\nDescription=blackboard\nAfter=postgresql.service\n\n[Service]\nLimitNOFILE=5000\nType=oneshot\nRemainAfterExit=yes\nExecStart=/usr/local/blackboard/tools/admin/ServiceController.sh services.start\nExecStop=/usr/local/blackboard/tools/admin/ServiceController.sh services.stop\n\n[Install]\nWantedBy=multi-user.target' | sudo tee /etc/systemd/system/blackboard.service; sudo systemctl enable blackboard

Create blackboard and bbinstaller directories and assign ownership to bbuser

$ sudo mkdir -p /usr/local/blackboard /usr/local/bbinstaller; sudo chown bbuser:bbuser /usr/local/blackboard /usr/local/bbinstaller

Create blackboard license file to server (plain-text in /usr/local/blackboard/config/license/blackboard-license.xml in a working DVM/test environment)

$ vi /usr/local/bbinstaller/blackboard-license.xml

Create installer.properties (included below)

$ vi /usr/local/bbinstaller/installer.properties

Install (and upgrade) Learn

Create a checkpoint/snapshot. It should be taken when the system is shutdown for maximum data consistency.

Set temporary variable with the new version. For example $ BBVERSION='3500_0_2'

$ BBVERSION='version_tag'

Create install directory (can be deleted later to reclaim space)

$ mkdir /usr/local/bbinstaller/$BBVERSION

Transfer learn-installer.zip file to server (can be deleted later to reclaim space)

Unzip installer.zip to install directory

$ unzip learn-installer-XXXXX.zip -d /usr/local/bbinstaller/$BBVERSION

Temporarily change to installer directory and then run the installer

$ pushd /usr/local/bbinstaller/$BBVERSION; ./installer.sh -c /usr/local/bbinstaller/installer.properties; popd

Run the PushConfigUpdates admin tool (also tries to fix any permission issues)

$ /usr/local/blackboard/tools/admin/PushConfigUpdates.sh --no-restart

Start the blackboard service

$ sudo systemctl restart blackboard

(optional) Install (and upgrade) Starting Block

Install and set AVAILABLE the Starting Block B2

$ /usr/local/blackboard/tools/admin/B2Manager.sh -i /usr/local/blackboard/system/autoinstall/internal.developer/allavailable/starting-block.war; /usr/local/blackboard/tools/admin/B2Manager.sh -s AVAILABLE bb-starting-block

Test to make sure it works

• https://yourserver/webapps/bb-starting-block-BBLEARN/execute/help

Post Install

After installing (and upgrading) review the output in the log files.

• /usr/local/blackboard/logs/tomcat/stdout-stderr-*.log
• /usr/local/blackboard/logs/tomcat/bb-access-log.*.txt
• /usr/local/blackboard/logs/bb-services-log.txt

If you see ClassNotFoundException errors for com.blackboard.partners.* the Partner Cloud B2 is missing.

$ /usr/local/blackboard/tools/admin/B2Manager.sh -i /usr/local/blackboard/system/autoinstall/market.pro/allavailable/partner-cloud.war

(optional) Implement CA-signed Certificate for HTTPS

Run the ShowCleartextPasswords admin tool to output the current keystore password

$ /usr/local/blackboard/tools/admin/ShowCleartextPasswords.sh | grep appserver.keystore

Set temporary variable with the keystore file

$ BBKEYSTORE='/usr/local/blackboard/config/keystores/tomcat.keystore'

Rename current keystore so we have it in case of issues

$ mv $BBKEYSTORE $BBKEYSTORE.bak

Generate new private key for certificate

$ keytool -genkeypair -keystore $BBKEYSTORE -storetype jks -alias tomcat -keysize 2048 -keyalg RSA

• When asked for "first and last name" input the CN of the certificate.

Output CSR

$ keytool -certreq -keystore $BBKEYSTORE -alias tomcat

Submit CSR to CA. The resulting signed certificate file should be a .p7b.

Transfer signedcert.p7b to server (it is plain text)

Import signed certificate into keystore

$ keytool -importcert -trustcacerts -keystore $BBKEYSTORE -alias tomcat -file signedcert.p7b

Run the PushConfigUpdates admin tool

$ /usr/local/blackboard/tools/admin/PushConfigUpdates.sh --no-restart --fast

Restart blackboard service

$ sudo systemctl restart blackboard

Contents of installer.properties

## Hostname and port numbers used when building URLS that get sent ##
##        to browsers or included in notification emails.        ##
##  These should reflect the frontend hostname and ports that are  ##
## used to access the application.                                ##
bbconfig.frontend.fullhostname=localhost.localdomain
bbconfig.frontend.portnumber=443
bbconfig.frontend.protocol=https

##  NOTE: this property is read at initial installation only. The  ##
##  value thereafter is set on Admin->System Config->Email Config  ##
bbconfig.admin.email=root@localhost.localdomain

## The SMTP host name via which Learn sends email out. This is a mandatory setting. ##
bbconfig.smtpserver.hostname=localhost
## The port on SMTP server port which Learn connects to send email. It will be protocol-default if not specified. ##
bbconfig.smtpserver.port=
##  The boolean flag to indicate whether the SMTP server requires authentication, the value can be either true or false(default)  ##
bbconfig.smtpserver.auth.required=false
bbconfig.smtpserver.username=
bbconfig.smtpserver.password=
## Connection type specifies the way how the emails be encrypted, the valid options are ##
## Default:  the emails are not encrypted.                                              ##
## StartTLS: the emails are encrypted via TLS                                          ##
## SSL:      The emails are emails via SSL                                              ##
## Any other value (including null) will be token as Default.                          ##
bbconfig.smtpserver.connectiontype=default

##                  tomcat developer properties                    ##
bbconfig.tomcat.debug.enable=true

# default passwords inside Learn, modify as desired #

antargs.default.users.integration.password=password
antargs.default.users.administrator.password=password
antargs.default.users.guest.password=password

# default postgresql passwords, as pre-configured #

antargs.default.vi.db.password=postgres
antargs.default.vi.stats.db.password=postgres
antargs.default.vi.report.user.password=password
bbconfig.database.admin.password=postgres
bbconfig.cs.db.cms-user.pass=postgres
bbconfig.database.server.systemuserpassword=postgres

## Enable caching of plugins to local file systems for performance ##
# true - Mimics B2 behavior of Learn SaaS
bbconfig.plugins.cache.enabled=true

# typical defaults, do not need editing #

bbconfig.appserver.fullhostname=localhost.localdomain

bbconfig.basedir=/usr/local/blackboard
bbconfig.file.license=/usr/local/bbinstaller/blackboard-license.xml
bbconfig.java.home=/usr/java/latest

bbconfig.database.datadir=/usr/local/bbdata
bbconfig.database.type=pgsql
bbconfig.database.server.instancename=
bbconfig.database.server.fullhostname=localhost

bbconfig.database.server.instancenametype.oracle=SID
bbconfig.oracle.client.drivertype=thin
bbconfig.database.indexdir.oracle=/usr/local/bbdata

bbconfig.unix.max.open.files=5000

bbconfig.inst.name=Blackboard, Inc.
bbconfig.inst.city=Washington
bbconfig.inst.state=DC
bbconfig.inst.zip=20001
bbconfig.inst.country=USA
bbconfig.inst.type=Developer

##      java virtual machine config - java bound processes        ##
bbconfig.min.heapsize.tomcat=2048m
bbconfig.max.heapsize.tomcat=2048m
bbconfig.max.stacksize.tomcat=1M

bbconfig.jvm.options.extra.tomcat=-XX:+UseCompressedOops -XX:+DoEscapeAnalysis -Xverify:none

bbinstaller.skip.db.comments=true

# end of installer.properties

Feb 11, 2019 - added Starting Block installation

Mar 12, 2019 - updated Starting Block installation and added Post Install section

Happy New Year, 2019!

After two years of presenting our intention to deprecate SOAP, I am excited to say that 2019 is the year for our deprecating Learn SOAP Web Services! You can find the full deprecation announcement at the end of this document.

Over the past couple years I have had a few folks ask me “Mark, why would you remove the SOAP Web Services?” and “Mark, why would you replace the SOAP Web Services with REST?” The following addresses these questions.

APIs can use different architectures for transfer of data from the remote server to the client software. In the case of Blackboard the two web service architectures are SOAP and REST. Historically, SOAP was the go-to messaging protocol for web services. But SOAP suffers from limitations that present concerns as we develop new services and products. These SOAP limitations also hinder you from building integrations with Blackboard products.

The need for lightweight web and mobile applications, firewall transparency, broad language support with easily found examples, security, and data transfer in a compact format have driven the popularity of RESTful architecture. The flexibility of REST has led us to adopt REST as our primary integration architecture for our products.

REST, in many use cases, offers server and client performance improvements over SOAP. These improvements decrease data access times such as page loads.

Finally, the developer and blackboard administrator experiences are simply better with REST applications. The developer portal allows easy documentation for documenting APIs and for managing and delivering integrations with modern authorization workflows. We also have the developer community and GitHub sites that provide discussion, source code, and examples about our REST APIs. And, to be blunt, it is easier for developers to build integrations with REST APIs than with SOAP web services.

Blackboard introduced Learn SOAP Web Service APIs in 2007. Since that time our Learn product has undergone substantial change. As a result SOAP is not suitable for SaaS and Ultra integrations. Since its introduction, REST has emerged as the de facto and preferred API architecture.

The selection of REST as the architecture behind our integration-enabling strategy enables Blackboard and our developer community to move forward with a modern, lightweight, and flexible integration architecture. For this reason SOAP is being deprecated.

Thank you for your interest in developing integrations for Blackboard Learn and I am looking forward to talking with you about your REST-based integrations in 2019!

Cheers,

-m

Mark O'Neil

Director, Product Management, Platform and APIs

Blackboard Inc.

Blackboard SOAP Web Services Deprecation Announcement

This SOAP deprecation announcement pertains to Blackboard SOAP APIs. It does not apply to the IMS Learning Information Services (LIS) SOAP Web Service-based SIS Services.

With the release of Blackboard Learn Q2 2019, Blackboard deprecates our SOAP APIs. Blackboard SOAP APIs will be available for two self-hosted and managed-hosted releases, starting with the release of Q2 2019 and will be removed from the Q2 2020 and corresponding SaaS releases of Blackboard Learn. We recommend that if you use Blackboard's SOAP APIs in any capacity, as an external application or as part of a Building Block, that you immediately refactor your integration using our Blackboard REST APIs.

We are making this change to modernize our integration model for developers through the move to REST APIs. You may read about this strategy in more detail in the below “Why SOAP Deprecation?” article on our Community site.

We have created several documents on our Community site to provide change guidance

• For mapping SOAP to REST APIs: SOAP-to-REST Migration: Mapping
• For viewing REST API documentation: Explore APIs
• For learning about our REST APIs: REST

Should you determine that your existing integration cannot be refactored using current REST APIs, please contact us at developers@blackboard.com.

Blackboard SOAP APIs will become unavailable with the release of Blackboard Learn Q2 2020 and it's corresponding SaaS release.

Challenge:  Batch assigning users to Admin roles within Nodes.  When you take advantage of the Community systems Learning Context Hierarchy it can be time consuming to assign many users roles is each node.  This was a challenge for Queen Margret University and  Joe Currie talked to us at TLC in Manchester on Dev Day to see if we had a better way.

Solution:  I have created a very simple B2 to help with this task.   This B2 adds a new system Admin tool.

Batch Node Admin Roles 

Copy and paste Pipe separated List of User and Roles with the Node Batch UID. This currently only supports one role per user per Node. Use a carriage return for each new user record. The format is user name|Node Batch UID|role name e.g. fflintstone|68555d7b-6445-43b0-a5e1-dd794d0ce2f1|User Administrator

Example:

AH1|3875808e-9a59-4ae7-8782-be9961a40b5f|User Administrator

AH2|3875808e-9a59-4ae7-8782-be9961a40b5f|User Administrator

AH3|3875808e-9a59-4ae7-8782-be9961a40b5f|User Administrator

AH4|3875808e-9a59-4ae7-8782-be9961a40b5f|User Administrator

AH5|3875808e-9a59-4ae7-8782-be9961a40b5f|User Administrator

AH6|3875808e-9a59-4ae7-8782-be9961a40b5f|User Administrator

Technical Details:

The B2 is based from the fabulous temple B23 form the All the Ducks Team.  GitHub - AllTheDucks/atd-b2-stub: Building Block template project

The Node manager does all the hard work for you.  Load in the Node ID a user list and a role list.

NodeManagerImpl n = new NodeManagerImpl();
n.addNodeAdmins(getnodeinfo.getNodeId(), userlist, rolelist);

Limitations:  Only one role can be assigned via this tool at the moment per user per node.  If a user already has a role this will be removed and updated with the new role.

Going Forward: I am happy to share the B2 or the code with anyone who wants to test or modify this project.  There is plenty of scope for improvements and I hope this will be made redundant in future as our REST API's get more mature and feature rich.

Hello,

Who does not feel frustrated using the "Volume Exclusion" tool and does not have the Learn buttons also deleted? Or by using a course as a base, replicating this content for numerous other courses using the "Copy Course" tool. These in turn, also depending on the deletion of buttons before the replica.

Having this and other scenarios I share with you the post (text in Portuguese).

I've been working in a proof of concept application to use LDAP to authenticate users in Blackboard Collaborate using the simplest possible approach. Now I have something working and I want to share it with the community.

First of all I'm not a PHP programmer, so the code you'll see needs a lot of improvement and is not production ready, but I think is enough in order to understand the concept. I've choosen PHP because of the simple LDAP interaction that it has out of the box.

The concept is that you can create a login page that binds the user with the LDAP server, and if the binding is successful then it creates a LTI request with the user data in a unique context and redirect the user to the management interface.

Here you can watch a demo and code walkthrough, (sorry, it is in Spanish, I'll upload an English version as soon as I have enough time) : Demo LDAP-Collaborate - eLearning Media

The code used in the video is available in Github: GitHub - eLearningMedia/LDAP-Collaborate-login: Simple application to manage Blackboard Collaborate sessions and recordi…

I want to share my experience creating a "blank page" content item in the course TOC programatically.

My motivation was to add the Collaborate recordings download links from a course to a content in order to keep them available after restoring the course in other instance of Learn after archiving, because the pk1 and the Collaborate plugin tables are not kept.

The core of the solution is this code:

  CourseToc newToc = new CourseToc();

  newToc.setTargetType(Target.CONTENT_ITEM);
  newToc.setCourseId(course.getId());
  newToc.setLabel("Recordings");
  newToc.setLaunchInNewWindow(false);
  newToc.setIsEnabled(true);
  newToc.setAllowGuests(false);

  Content content = new Content();

  content.setTitle("Recordings");
  content.setCourseId(course.getId());
  content.setIsAvailable(true);
  content.setAllowGuests(false);
  content.setIsFolder(false);
  content.setLaunchInNewWindow(false);
  content.setContentHandler("resource/x-bb-blankpage");
  content.setBody(FormattedText.toFormattedText(contentString));

  ContentDbPersister.Default.getInstance().persist(content);

  newToc.setContentId(content.getId());

  CourseTocDbPersister.Default.getInstance().persist(newToc );

  CourseMapManagerFactory.getInstance().invalidateCache(course.getId());

The contentString variable contains the HTML to show in the blank page content.

Hope this will help somebody who want to create any kind of content programatically, not just my particular user case.

Hello, I recently just had to run a report via Open DB and supply some Learn Information to a vendor that will be creating a PoC. I was tasked on getting this information but the information of course would violate FERPA. So I had to come up with a way to mask the user information to still supply an accurate model but protect the user information at the same time. So I decided to use an old python script that converted CSV files to XLSX and make some modifications.

I used openpyxl to create the Excel XLSX files, Docopt to make the CLI creation easy, and some built in core modules: csv, json, and random.

The main challenge was that I had two reports from Open Db that correlated between Activity and Gradebook Data by the USER_ID. I ended up doing some Python wizardry and use a global (gasp!! what globals in Python, your fired!) Anyways, yes the use of a global in Python is frowned upon, but hey it got the job done! That’s what re-factoring is for...right?

Anyways, I thought I would share this application and share my work and hopefully see how useful this can be. I plan on implementing a way to connect directly to Open Db (Already have it done in another project) and then add in some common functionality: Mongo DB Storage, Better Configuration Methods, etc.

You are all welcome to use, share, clone, hack, and destroy this code here:

https://github.com/elmiguel/CSV2XLSX

You will just need to query and save your own data sets… Luckily for you, I will share my queries:

Activity Accumulator:

SELECT AA.PK1
,AA.EVENT_TYPE
,U.USER_ID
,CM.BATCH_UID
,AA.GROUP_PK1
,AA.FORUM_PK1
,AA.INTERNAL_HANDLE
,AA.CONTENT_PK1
,AA.DATA
,AA.TIMESTAMP
,AA.STATUS
,AA.MESSAGES
,AA.SESSION_ID
FROM BBLEARN.ACTIVITY_ACCUMULATOR AA
INNER JOIN BBLEARN.USERS U
ON AA.USER_PK1 = U.PK1
INNER JOIN BBLEARN.COURSE_MAIN CM
ON AA.COURSE_PK1 = CM.PK1
WHERE CM.COURSE_ID IN (‘LIST COURSE_IDS HERE’
)
ORDER BY AA.TIMESTAMP DESC;

 
  Gradebook Data:

SELECT
CM.COURSE_ID,
U.USER_ID,
U.FIRSTNAME,
U.LASTNAME,
GM.TITLE,
GM.DUE_DATE,
A.SCORE,
CASE A.STATUS
WHEN 1 THEN 'NOT_ATTEMPTED'  
WHEN 2 THEN 'ABANDONED'
WHEN 3 THEN 'IN_PROGRESS'  
WHEN 4 THEN 'SUSPENDED'
WHEN 5 THEN 'CANCELLED '
WHEN 6 THEN 'NEEDS_GRADING'  
WHEN 7 THEN 'COMPLETED'
WHEN 8 THEN 'IN_MORE_PROGRESS'  
WHEN 9 THEN 'NEEDS_MORE_GRADING'
ELSE 'NO STATUS'
END AS STATUS,
A.ATTEMPT_DATE,
A.FIRST_GRADED_DATE,
A.LAST_GRADED_DATE,
A.DATE_ADDED,
A.DATE_MODIFIED,
A.LATEST_IND
-- A.STUDENT_SUBMISSION
-- A.PK1 ATTEMPT_PK1,
-- A.QTI_RESULT_DATA_PK1,
-- GG.GRADEBOOK_MAIN_PK1,
-- GG.PK1 GG_PK1
FROM GRADEBOOK_MAIN GM  
INNER JOIN GRADEBOOK_GRADE GG
ON GG.GRADEBOOK_MAIN_PK1 = GM.PK1
INNER JOIN ATTEMPT A
ON A.GRADEBOOK_GRADE_PK1 = GG.PK1
INNER JOIN COURSE_USERS CU
ON GG.COURSE_USERS_PK1 = CU.PK1
INNER JOIN COURSE_MAIN CM
ON CU.CRSMAIN_PK1 = CM.PK1
INNER JOIN USERS U
ON CU.USERS_PK1 = U.PK1
WHERE CM.COURSE_ID IN (‘LIST COURSE_IDS HERE’);