CD2: An Example for Retrieving External Tools Used

[agschmid]

Years ago I used the API to find which LTI tools were used in a course, then switched to CD1 and now I'm using CD2. 

I built a Tableau report with several filters, including:

• Type of course: sis, non-sis or all
• State of the course: published, unpublished or all

How tools are included:

• Navigation: if a tool is visible on the navigation, then it is counted
• Assignment: if the assignment contains the launch URL for a given tool and is published, it is counted
• Modules: if the module item contains the launch URL for a given tool and is published, it is counted

Since there are course-level placements and account-level placements, and the names for a given tool at times vary, I use a case statement and regex to parse the URL.

There are some quirks with CD2 when pulling the data, and I documented what I used in CD1, then figured out how to find that in CD2, with example results for each query in CD2 for specific data before doing any joins.

Data Access Platform Datasets - CD2 table information

https://data-access-platform-api.s3.eu-central-1.amazonaws.com/tables/catalog.html#tables-in-canvas

The following is just one way of pulling the data. I have more “where” clauses, excluding URLs I do not want to pull, but the following are generic queries that can be adapted.

Tips:

In CD1 the workflow state for external tools was active, but that does not work in CD2. Instead you need to query the context_external_tools table for workflow_state !=  'deleted' AND workflow_state IS NOT NULL.

External Tools Visible on the Navigation

The courses table has a field called tab_configuration which needs to be parsed. 

Variations found in the tab_configuration json arrays for external tools:

{"id":"context_external_tool_<id>"},

{"id":"context_external_tool_<id>","hidden":true},

{"id":"context_external_tool_<id>","hidden":null,"position":11},

{"id":"context_external_tool_<id>","hidden":false,"position":52},

The {"id":"context_external_tool_<id>"} entry needs to be included, so it may be easier to set a default hidden value to null when you create the view. Entries to be counted as being on the navigation: hidden_type IS NULL OR hidden_type = false

Example: create a view using the tab_configuration data and the courses table containing the following (courses_tab_configuration):

• course_id
• sis_source_id
• course_code
• account_id
• workflow_state
• enrollment_term_id
• id (tab id)
• hidden 
• external_tool_id (parsing the id from context_external_tool_<id>

Example query:

SELECT c.external_tool_id, c.course_id, c.sis_source_id, c.course_code, c.account_id, c.workflow_state, c.enrollment_term_id, e.url

FROM courses_tab_configuration c 

INNER JOIN context_external_tools e ON (c.external_tool_id = e.context_external_tools_id) WHERE c.workflow_state <> 'deleted' 

AND c.tab_configuration_id LIKE '%context_external_tool%' 

AND c.external_tool_id IS NOT NULL 

AND ((c.hidden_type IS NULL) OR (c.hidden_type = false)) 

External Tools used in Assignments

The assignments table has a field called submission_types which needs to be parsed. 

Example json:

{"v":{"f":[{"v":[{"v":{"f":[{"v":"external_tool"}]}}]}]}}

Example: create a view using the submission_types data and the assignments table containing the following (assignments_submission_types):

• assignments_id
• context_id
• submission_types
• workflow_state

SELECT DISTINCT(a.assignments_id), a.context_id, c.sis_source_id, c.course_code, c.account_id, c.workflow_state, c.enrollment_term_id, ct.url

FROM assignments_submission_types a 

INNER JOIN content_tags ct ON(a.assignments_id = ct.context_id) 

INNER JOIN courses_tab_configuration c ON (a.context_id = c.course_id) 

WHERE a.workflow_state = 'published'

AND a.submission_types = 'external_tool' 

AND ct.url IS NOT NULL 

AND c.workflow_state <> 'deleted'

External Tools used in Modules

Module entries don’t seem to have an id that links to the context_external_tools table, so for modules I go by the URL.

SELECT DISTINCT(ct.content_tags_id), ct.context_id, c.sis_source_id, c.course_code, c.account_id, c.workflow_state, c.enrollment_term_id, ct.url

FROM content_tags ct 

INNER JOIN courses_tab_configuration c ON (ct.context_id = c.course_id) 

WHERE ct.content_type = 'ContextExternalTool' 

AND ct.url IS NOT NULL 

AND ct.context_type = 'Course' 

AND ct.workflow_state = 'active' 

AND c.workflow_state <> 'deleted'