CD2 and LTI Usage

[IanGoh]

Got my hands on the LTI tables in CD2 now, trying to query if I can list all LTI 1.1 and 1.3 related to a specific course.

Context_External_Tools (LTI 1 and 1.3) - has two kinds of tools (context_type) - Account, Course

• if context_type: course, then I can use the context_id -> courseid
• if context_type: account, that just tells me it's installed at that context_id -> accountid and presumably everything underneath it (nested).

Do I then assume that if a courseid is nested under that accountid, that it's got the tool?  Where does it take into account the course navigation (visible vs hidden)?

I also looked at Lti_Resource_links, that gets me from Context_External_Tools -> Lti_Resource_Links -> Assignments, so at least I can get assignments using external tool (LTI 1.3 only)

So it seems two-fold

1) where LTI tools can appear (course navigation, assignments, etc) and then

2) unioning up the various sources at the course level.

Any ideas? Am I going about this the wrong way?

[IanGoh]

Hmm, so there are Lti_Resource_links w/ context type: course as well.

Do those Lti_Resource_links

• resource_link_uuid (UUID) – A UUID identifying the resource link.

go anywhere else? I don't see them linked on the Entity Relationship Diagram

[IanGoh]

Oh, it appears they didn't get me the context_external_tools.settings.custom_fields (at least) -- trying to replicate the LTI report in admin tools

Any others that are useful to determining which tools are really in a course?

[marco_divittori]

Do you mean the "LTI Report" within the account "Settings" area? The issue with that report is that it doesn't show course usage for tools that are installed globally (at the account level).

It looks like Instructure is adding LTI usage metrics to their new Intelligent Insights product offering.
https://community.canvaslms.com/t5/The-Product-Blog/Introducing-Intelligent-Insights-a-New-Product-O...

[IanGoh]

Right trying to avoid having to pay extra.  The LTI Report I can replicate to show my query is working (least once I get the settings.custom_fields)

But you are correct, context_external_tools only lists course tools (which we have few) and account tools (which we have many).  Eventually, I want an accurate picture of all LTI tools used within a course.

[pgo586]

Just mentioning this in case it is useful. We depend on our own reports to analyze LTI tools usage. I know you're after a different question (how to tell which tools are installed in a course), but analyzing tools' usage might be potentially even more useful, depending on your use case of course. We do this based on Canvas live events (we have Splunk queries to generate the report based on these events - joined as needed with Canvas data 2). 

[IanGoh]

Good to know - what particular live event are you relating to? asset_accessed?
https://jhudemo.instructure.com/doc/api/file.data_service_canvas_asset.html#asset_accessed

[pgo586]

I'm actually using the Caliper format (versus the raw format that you mention), and I specifically use the 'NavigatedTo' action in the event, extracting those events that contain strings of the form:  'urn:instructure:canvas:context_external_tool:<long tool id.>). In these events, I interpret a few fields out of those events as follows:
- data{}.actor.extensions.com.instructure.canvas.user_login  (is the user's sis id)
- data{}.group.id (is the Canvas -long- course_id)
- data{}.object.id (is the ToolId)
- data{}.object.extensions.com.instructure.canvas.context_account_id (is the ContextAccountId )
- data{}.object.extensions.com.instructure.canvas.url (is the ToolUrl,)
- data{}.object.extensions.com.instructure.canvas.asset_name is the ToolName
There are some caveats associated to extracting the fields above, some of which are as follows:
- the ToolName field doesn't necessarily correspond to your 'official' LTI tool names, as it can be set by the instructor installing the LTI, so we tend to use a combination of  ToolUrl and ToolName to actually distinguish the LTI tools later on. 
This ends up being pretty useful nonetheless (and does not require a subscription fee). If you want to see the actual Splunk queries let me know (they're complex and of course not SQL but SPL).

[pgo586]

Strange, I thought I had already posted the answer to your last question here but I don't see it (I apologize if it comes out eventually as a duplicate though). I use the Caliper format for Live Events (not the raw format which you point to above), and specifically the 'NavigatedTo' action value to find the events (restricted to those which mention a urn with the format:"urn:instructure:canvas:context_external_tool:<long tool id>). I obtain the following 'attributes' from each event: the user sis_id, the tool id, the context account id, the tool url, the course id (note: most ids are in their 'long' form). I then join with CD2 tables as convenient to obtain any extra info. that I need. For example, I have one report that displays LTI usage by Canvas Sub-account. Let me know if you'd like more specifics about these queries (note that they're not SQL but SPL though). 

[JamesSekcienski]

@IanGoh 

Have you checked out @agschmid's blog post? CD2: An Example for Retrieving External Tools Used 

[IanGoh]

Thanks!