Can I get data on chat activity in courses?

[BenjaminSelby]

Is there a way in which I can get data regarding chat activity in courses? 

I have been looking in the Canvas Data tables, but can't find anything particularly useful. Also, the API doesn't seem to have anything. 

Does anyone have any suggestions, please? 

[stimme]

Hi  @BenjaminSelby , the Chat tool is a first-party LTI from Instructure, which means it has a special non-quite-internal status. The place I have found it in Canvas Data is the requests table. (I actually load requests with urls containing 'external_tool' into a separate table for faster querying.) I have written a case statement with a couple of regular expressions to find the id to join requests to the external_tool_activation_dim table. This gives an impression of use, but does not contain any messages actually exchanged in Chat.
Here is a SQL query to start from. I hope it helps.

SELECT c.code
, COUNT(DISTINCT r.user_id) AS users
, COUNT(*) AS launches
FROM (
 SELECT user_id
 , course_id
 , CASE
   WHEN url REGEXP '^.*/external_tools/[0-9]+.*$'
   THEN SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX(s.url,'external_tools/',-1) ,'?',1) ,'/',1)
   WHEN url REGEXP '^.*/external_tools/sessionless_launch[.?.]id.*$'
   THEN SUBSTRING_INDEX( SUBSTRING_INDEX(s.url,'&',1) ,'=',-1)
   ELSE NULL
   END AS tool_canvas_id
 FROM requests ) r
JOIN course_dim c ON c.id = r.course_id
JOIN external_tool_activation_dim et ON et.canvas_id = r.tool_canvas_id
WHERE et.name = 'Chat Tool'
GROUP BY code;

[BenjaminSelby]

Thanks Samuel. Greatly appreciated!