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'

[IanGoh]

External Tools used in Assignments -- is it easier now that we can get  Lti_Resource_Links in CD2?

[agschmid]

Yes, but as stated above, the assignments table has a field called submission_types which needs to be parsed. 

Example json:

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

Then you can join on the context_external_tools  table.

[IanGoh]

I was trying

SELECT
	C.course_code,
	A.id,
	A.context_id,
	A.context_type,
	A.title
FROM  [Lti_Resource_Links] AS LRL
INNER JOIN  [Assignments] AS A
	ON LRL.context_id = A.id
		AND LRL.context_type = 'assignment'
INNER JOIN  [Courses] AS C
	ON A.context_id = C.id
		AND A.context_type = 'course'

[IanGoh]

With my query, I am finding some cases where there's a voicethread in the assignment (based on the LRL.context_external_tool_id) but the assignment submission type was : none (they didn't have to submit the assignment)

[agschmid]

@IanGoh The specific items I was looking for in my report:

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

I did not look if the instructor added something in the instruction text in Assignments. If your report needs this, amend the query accordingly.

[IanGoh]

Thanks - any idea how that compares to what Insights would provide?

[IanGoh]

Definitely like the view for tab configuration data; SQL isn't the best for dealing with JSON.

Testing - I think I can get away with a CROSS APPLY instead of the view for tab configuration.

WITH myCTE (course_code, context_external_tool_id) AS (
    SELECT
        C.course_code,
        -- O.[Key],
        -- O.Value
        -- P.value AS context_external_tool,
        IIF(LEN(P.value) > 22, RIGHT(P.value, LEN(P.value) - 22), '') AS context_external_tool_id -- safety check
    FROM canvas.Courses AS C
    CROSS APPLY OPENJSON(C.tab_configuration) AS O
    CROSS APPLY OPENJSON(O.value) AS P
    WHERE
        O.value LIKE '%context_external_tool%'
        AND O.value NOT LIKE '%"hidden":true%'
)
SELECT
    myCTE.course_code,
    CET.id,
    CET.name
FROM myCTE
LEFT JOIN canvas.Context_External_Tools AS CET
    ON CET.id = myCTE.context_external_tool_id
WHERE myCTE.course_code = <course to check>

[agschmid]

@IanGoh I have no idea about Insights

[IanGoh]

The only other thing I can think of is if external tools can appear in Wiki Pages?  But that might have the same issue as with Modules, no direct link to context_external_tools but urls

Examples: VoiceThread, Panopto

Does it still count as an LTI?

[agschmid]

I think it depends on what your goal for the report is. It helps to look at what is in an actual course and what you want to capture...do you want to know if users set up an account, submitted an assignment using an external tool, etc. If you look at the launch urls in a course for different locations (same tool), they may go to different places. I created a huge Google doc to capture the different urls and then looked at what we wanted and figured out  what I needed to use for the regex to get exactly the placements we want.

[IanGoh]

Thanks - basically, my end goal, is to capture what LTIs are used in a course.