Find external tool usage in course shells

[nardell]

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 

[tnijssen]

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* 

[aliceanderson1]

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.

[robotcars]

 @aliceanderson1 ,

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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[fosterl]

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.

[robotcars]

 @fosterl , 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:

[kimhuang]

@robotcars and @nardell 

Both of you have provided very valuable way to search the usage of the external tool. Thank you!

Recently, I have imported the data from Canvas Data Portal into the local DB tables. Here is one of the cases that we are interested in:

We have installed the external tool- Panopto Video recording on the root account level and enabled it from the course navigation bar in all Spring 2021 courses by copying the course template to the course shell.

We want to know how many courses have used this tool for real, meaning at least there is one video file being created by the instructor.

When I query this two tables, external_tool_activation_dim and external_tool_activiation_fact, they don't provide the real usage instead all the courses in Spring 2021 are returned since the tool is enabled at navigation bar by default.(Even though the tool is enabled , the instructor can choose not to use it. ) 

Could you give any advice that I can pursue further?

Thanks so much,

-Kim

[robotcars]

Hi @kimhuang

I don't have experience with how Panopto works with Canvas or shares resources .I think @James might be familiar with this tool?

Either way... if the external tool is used in an assignment you should be able to join assignment_dim and file_dim (if it stores a file in Canvas) to associate whether Panopto was used in an assignment. If there's an associated assignment it should point to the external tool.

[JamesSekcienski]

Hello @kimhuang,

Since you are looking just to see that a video has at least been created for a course, it may be easier to check the analytics from Panopto rather than trying to search through the information in Canvas Data.  https://www.panopto.com/features/video-cms/video-analytics/ 

It looks like the analytics from Panopto can also give you a lot more information than Canvas Data would so you can get a better overview of its usage across courses and users.

[kimhuang]

Hi @robotcars  and @JamesSekcienski ,

Thank you both very much. Your advice are very helpful. I realized from the comment of @robotcars  that the video files were stored on Panopto, not in Canvas. When the Panopto is invoked from the course navigation bar either by the instructor or the student,  all the following steps are done on Panopto server. Therefore the analytics from Panopto will provide more accurate information.  Actually this applies to all the LTI tools. If any LTI tool is turned on for the course by default from the account level, we might not be able to get the actual usage of the tool from external_tool_activation_dim and external_tool_activation_fact.

I will join the assignment_dim with the external_tool_activation_dim to see what it gets me.

Thanks again,

-Kim

[agschmid]

Hi Kim,

I've been creating reports for our university for external tool usage in courses, and if it helps, for assignments, using Canvas Data, I look for assignment_dim.submission_types = 'external_tool' and join on assignment_dim.external_tool_id = external_tool_activation_dim.id. I pull the external_tool_activation_dim.url, which is used in a case statement so I can actually add the tool name to my report.

I also pull this information from modules, where  (module_item_dim.content_type = 'ContextExternalTool' OR module_item_dim.content_type = 'ExternalUrl')

[kimhuang]

Hi @agschmid Thanks so much, your tip is really helpful.  How many different reports do you generate regularly? Could you name some to give me some idea?  We have adopted Canvas since 2020, all courses activities will move to Canvas this coming Fall.  Last term, Spring 2021,  there were one-third of courses taught in Blackboard.

We would love to hear anything related to Canvas usage. 

Thanks again,

-Kim

[agschmid]

Hi @kimhuang When I pull the data for the external tools from Canvas Data, I also get all published courses for that term along with the section counts per course. (If there are 0 sections, then the section has been crosslisted and it doesn't make sense to include that course in the totals. I pull the external tools visible on the navigation and I have a ruby script that goes through and parses all the csv files.

This information gets loaded into Tableau, and then I do a join on Canvas Data so I can add a page into the Tableau report which will pull the instructors for the selected tool, along with their course list, making it easy for the staff person to contact instructors regarding a specific tool. 

The report has another page which allows selection by tool and/or term, which displays the number of participants in a course so if there is an issue, we can see if it affects large classes, etc.

The first page of the report lists all the tools per term and the counts of how many placements there are for that tool per term. Another page displays the courses by term and number of placements and where (assignments, modules and navigation), per course.

Another page displays a comparison of tools between two terms so we can see if usage increased or decreased.

Separate from this report, I also pull data as needed for QA testing. (I'm a QA tester.)