Find external tool usage in course shells

Surveyor
11 5 2,318

As Canvas admins we have a need to track external tool usage, across all course shells. We often are very interested to know adoption of a tool at our institution. Or if we determine that a particular tool has a technical problem, we need to be able to find which teachers to contact. Unfortunately neither the LTI Tool Report nor External Tool API are suited for this task, in themselves. Both are only effective at finding tool installations either at tool or account contexts. What is needed is a way to find when an external tool is enabled in a course navigation, used in an assignment or module. Fortunately, we can gather this information from the Assignments API, Modules API, and Tabs API and weave the external tool info with course and teacher info gathered from the Courses API.  

In this post, (and in perhaps subsequent posts) I will describe my approach, which is developed in Python. I am eager to receive input from others to determine how my code can be improved. My Python code uses a perhaps idiosyncratic style, choosing to wrap api calls in Python generators with closures, rather than using objects and classes. It seems that this more lightweight approach is well suited for getting data out of Canvas APIs and weaving it together into reports.  Again, I am interested in having a discussion on this - there may be a better way than what I am doing. Folks familiar with Clojure and the videos lectures of Richard Hickey will guess my motivations -  ultimately I  am trying to make something complex simple. 

The heart of the report building process looks like this:


# ----Iterate over all courses ---------------------------
for c in all_courses():
    # Build report in a Python default dictionary, where the default type is list,
    # allows appending without checking key first
    xtool_data_for_course = defaultdict(list)
    # ---- And for each course, retrieve all installed x-tools, tabs, modules, and assignments
    # ---------XTOOL INSTALLS-----------
    for x in course_xtools(c['id']):
        xtool_data_for_course['XTOOL_INSTALLS'].append(x)

    # ---------TABS--------------------
    for t in course_tabs(c['id']):
        xtool_data_for_course['TABS'].append(t)

    # ---------MODULES---------------------
    for m in course_modules(c['id']):
        for i in module_items(direct_url=m['items_url']):
            xtool_data_for_course['MODULES'].append(i)

    # ---------Assignments---------------------
    for a in course_assignments(c['id']):
        xtool_data_for_course['ASSIGNMENTS'].append(a)

Each of the for loops is iterating over a Python generator. The generator is "produced" by the api_wrapper_function, which "encloses" all the parameters specific to a particular endpoint (URL, authentication, and options) and typically receive an ID that specifies which course to query. The generator also may have a filter predicate specified, so only  active, external tools are returned. Another module specifies the defining parameters for the generators, so our client code here does not need to sweat the details.

The data-structure produced is a list of Python dictionaries for each course that is found to have at least one external tool in-place. 


[{COURSE_INFO:{course_code, name, id},
     INSTRUCTOR_INFO: [{instructor INFO}, {}..],
     XTOOL_INSTALLS:[ {xtool tool info}, {},...],
     TABS: [ {tab info} ... ],
     MODULES: [ {module item info}, ...],
     ASSIGNMENTS: [ {assignment xtool info}]

Each record for a course will have COURSE_INFO and INSTRUCTOR INFO and may have XTOOLS, TABS, MODULES, ASSIGNMENTS.  This data-structure is processed into a CSV report by another script. Right now the process works, with a few problems I am still working on. As you may guess, a process that inspects each and every course with multiple (four)  endpoint calls is going to be slow. May look at running multiple threaded agents that split up the list and then aggregate results at the end. Another challenge is surfacing the right information about external tools in course sites, and excluding less critical info. For example, my current report shows up with bunch of redirects. In most cases that is not what we are interested in. Ok, that is it for now. If folks are interested will the underlying functions/generators that make this work. Bit of code-clean up to do first! Would like to thank Peter Love for the advice he offered and help tracking down all the endpoints needed to generate this report. Finally do let me know if there are better ways of achieving this same ends. Let me down easy... but do let me know, thanks!

References:

Assignments API (Assignments - Canvas LMS REST API Documentation 

Modules API (Modules - Canvas LMS REST API Documentation ) 

Tabs API (Tabs - Canvas LMS REST API Documentation )

Python Generators - Generators - Python Wiki 

5 Comments
Surveyor

Such a report shouldn't be too hard to build in to Canvas - it would probably only be one SELECT query on the table tracking external tools per course. Add a join to the course info table and you'd have the contact info of the owner too.

EDIT: It looks like the interesting tables are context_external_tool* 

Community Member

I need a clue on where to find the context_external_tool table and it's documentation. I have only found a small mention in the schema documentation for the external_tool_activation_dim --> Canvas Data Portal. But, the mention is just in the comment of one field. The structure of the context_external_tool table is not in the schema list.

Thanks for any help.

Community Advocate
Community Advocate

aliceanderson@ksu.edu,

I believe Thomas has a typo above referencing

Primary key for this record in the context_external_tools table in the Canvas database

which references a table outside our scope (in Canvas proper), but Canvas ID is the value we get for this tool 'activation'.

However, the table that Thomas points to in his comment is correct. Using Canvas Data I use external_tool_activation_dim for the following:

1)  List External Tools with URL and Description

-- unique external tools and descriptions
SELECT
     etad.name               AS external_tool,
     etad.url,
     etad.description
FROM
     CanvasLMS.dbo.external_tool_activation_dim etad
     JOIN CanvasLMS.dbo.course_dim cd ON (etad.course_id = cd.id)
     JOIN CanvasLMS.dbo.enrollment_term_dim etd ON (cd.enrollment_term_id = etd.id)
WHERE etad.workflow_state = 'active'
     AND cd.workflow_state IN ('available','completed')
     AND activation_target_type = 'course'
GROUP BY
     etad.name,
     etad.url,
     etad.description
ORDER BY etad.name;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

2) List Courses and External Tools. A little too much IMO, but I have 10,000+ rows

-- external tools by course
SELECT
     etad.course_id,
     cd.canvas_id,
     cd.name                    AS course_name,
     etd.name               AS term,
     etad.name               AS external_tool,
     etad.url,
     etad.description
FROM
     CanvasLMS.dbo.external_tool_activation_dim etad
     JOIN CanvasLMS.dbo.course_dim cd ON (etad.course_id = cd.id)
     JOIN CanvasLMS.dbo.enrollment_term_dim etd ON (cd.enrollment_term_id = etd.id)
WHERE etad.workflow_state = 'active'
     AND cd.workflow_state IN ('available','completed')
     AND activation_target_type = 'course'
GROUP BY
     etad.course_id,
     cd.canvas_id,
     cd.name     ,
     etd.name,
     etad.name,
     etad.url,
     etad.description
ORDER BY term, course_name, external_tool‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

3) This one might be best. This will list the External Tool Usage by Term

-- external tool count by term
SELECT
     etd.name               AS term,
     etad.name               AS external_tool,
     count(course_id)     tool_count
FROM
     CanvasLMS.dbo.external_tool_activation_dim etad
     JOIN CanvasLMS.dbo.course_dim cd ON (etad.course_id = cd.id)
     JOIN CanvasLMS.dbo.enrollment_term_dim etd ON (cd.enrollment_term_id = etd.id)
WHERE etad.workflow_state = 'active'
     AND cd.workflow_state IN ('available','completed')
     AND activation_target_type = 'course'
GROUP BY
     etd.name,
     etad.name
ORDER BY term, external_tool, tool_count‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
Surveyor

carroll-ccsd‌ Thanks a million for posting the SQL. I popped this into sqldeveloper (edited out your table names for ours, and added the course_dim.sis_source_id, as that's the easiest way for me to sort) and voila, I have the data I need! I especially like the external tools by course, so we can find who is using what that we're not aware of (Zybooks? never heard of it until now). We have almost 14,000 rows, but what an awesome cache of data! I can't thank you enough, I'm just not SQL savvy enough to have written this myself.

Community Advocate
Community Advocate

fosterl@oregonstate.edu, you're welcome, and thank you! The fantastic thing about Canvas Data is that any query the community can come up with, is likely going to help someone else, whether they can write SQL or not. We share them, and voila, no duplication of effort! :smileygrin: