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;
