cancel
Showing results for 
Search instead for 
Did you mean: 
tlc_OU
Learner II

Masquerade Reports

Hello!

Has anyone been able to run a masquerade/act as user report for their school? We are wanting to run a report that basically tells us 1) the person doing the masquerading, 2) the person being masqueraded, and 3) date/time this was done. We have gotten a few requests, and it's been tough going getting information from Canvas.

6 Replies
bneporadny
Navigator

Hi Tomika,

This is a great question and one that I don't believe Canvas has a report for right out of the box.  You might be able to pull a report by using the #request#table‌ in #canvas data‌.  I haven't look at doing this share this over with the Data and Analytics‌ and Canvas Developers‌ groups to see if anyone in these groups.  Maybe someone in one of these groups has had this request in and can share how they went about pulling the data.

robotcars
Lamplighter II

Hi tcox

Here's mine, MSSQL flavored for Canvas Data. I have a different strategy for Live Events that actually marks each event as masq or not. Depending on the size of your requests table or the partition you work on this can be kind of slow.

This one will return more of a per request log of the masquerading user.

-- users masquerading as other users, timestamp with url
SELECT
ru.canvas_id
, ru.name AS 'masquerading'
, masq.canvas_id
, masq.name AS 'as user'
, timestamp
, url
FROM
CanvasLMS.dbo.requests r
JOIN CanvasLMS.dbo.user_dim ru ON (r.real_user_id = ru.id)
JOIN CanvasLMS.dbo.user_dim masq ON (r.user_id = masq.id)
WHERE
r.real_user_id != r.user_id
AND r.real_user_id IS NOT NULL
AND r.user_id IS NOT NULL
-- white list by name
-- AND masq.name NOT IN ()
ORDER BY
ru.name
, masq.name
, timestamp‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This one does grouping for the user masquerading, who they acted as, the day, start and end time, and the number of requests they performed.

-- users masquerading as other users, time spent with start/end time
SELECT
ru.canvas_id
, ru.name AS 'masquerading'
, masq.canvas_id
, masq.name AS 'as user'
, CASE
WHEN CHARINDEX('E',pd.sis_user_id) = 1 THEN 'employee'
WHEN ISNUMERIC(pd.sis_user_id) = 1 THEN 'student'
WHEN CHARINDEX('MA',sis_user_id) = 1 THEN 'manual'
WHEN pd.sis_user_id IS NULL THEN pd.sis_user_id
END AS 'user_type'
, timestamp_day
, CONVERT(VARCHAR(12),MIN(timestamp),114) AS start_time
, CONVERT(VARCHAR(12),MAX(timestamp),114) AS end_time
, DATEDIFF(minute,MIN([timestamp]),MAX([timestamp])) AS time_spent_m
, COUNT(r.id) AS requests
, remote_ip
FROM
CanvasLMS.dbo.requests r
JOIN CanvasLMS.dbo.user_dim ru ON (r.real_user_id = ru.id)
JOIN CanvasLMS.dbo.user_dim masq ON (r.user_id = masq.id)
JOIN CanvasLMS.dbo.pseudonym_dim pd ON (pd.user_id = masq.id AND pd.workflow_state != 'deleted' AND pd.position = 1)
WHERE
r.real_user_id != r.user_id
AND r.real_user_id IS NOT NULL
AND r.user_id IS NOT NULL
-- white list by name
-- AND masq.name NOT IN ()
GROUP BY
ru.canvas_id
, ru.name
, masq.canvas_id
, masq.name
, pd.sis_user_id
, timestamp_day
, r.remote_ip
ORDER BY
ru.name
, masq.name
, timestamp_day‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Robert,

This is excellent. Thank you!

You're welcome!

Glad someone will get use of it. With only 5 people here masquerading most of what I see is 'Test Student', our admins troubleshooting things for other instructors, and API calls where I masquerade. I got bored with it.

I did however walk into one of our Canvas meetings and dropped 10 pages down and said 'look I can identify users who masquerade'... sometimes it might be enough just to show people you can audit all the things. :smileygrin:

Hi Robot,

Can you please share how you extract the data from Canvas to run this script. Is there a built in report that you use to build your tables?

Thank you,

Kathie

I found my own answer. The files are in the Canvas Data Portal section. The files are .GZ files and need to be decompress with WinZip.

😄