Canvas Report

[zwbratcher]

Since we are using RedShelf LTI is there a way to do a report out of canvas to pull the Course ID numbers out without having to go into each course one at a time. I was able to write a SQL statement to do this for our Moodle instance but would like to have some way to do this in Canvas. The one I wrote for Moodle was

select Shortname as 'Course Name', id, visible as Active, idnumber from mdl_course where
shortname like '%21/FA%'

[bmerkel · Accepted solution]

Do you have access to pull a Provisioning Report either through the front end or via the API? That will give you a CSV that you could load into a database to do what you describe. I think Canvas Data is a little overkill for this particular use case.

I pull a provisioning report every morning to cache "locally" so that I can do things just like this. I have automated it to pull the report through the API and drop it into a MySQL table. Actually, several tables as you can pull files for Courses, Enrollments, SubAccounts, Terms, etc.. pretty much anything you can upload via CSV you can grab a snapshot of as it stands in Canvas.

[Jeff_F]

@zwbratcher  

Did you figure out how to do this? I've attached basic SQL that enables you to filter by subaccount, course, enrollment type (teacher, etc.) and so much more.

And here is the line you were looking for:   

"course_dim"."canvas_id" AS "canvas_id (course_dim)",

Listing courses and including relevant data points such as the course Canvas ID # could be a foundational starting point for several dozen reports.  So there can be great future value in creating this. 

For example, I created a script that lists the edit urls of home pages of all future term courses.  This is given to our assistant team and they use this as an input file to process the auto entry of instructor names and term dates to these pages using a browser automation tool (assistants do not have API access).