[ARCHIVED] Multiple login users report?

Jump to solution
pkreemer
Community Participant

Hi, does anyone have tips on finding user accounts with more than one login? Ideally with an admin report or in Canvas Data. And for background, this situation can happen as a result of merging two user accounts into one, or from purposely adding a second login to a user account.

thanks!

Labels (2)
1 Solution
stimme
Community Coach
Community Coach

Hi  @pkreemer ‌,

Canvas Data stores the information for logins in the pseudonym_dim table. I have a couple of SQL queries that you can start with. I hope this helps find the multi-login users you're looking for. - Sam

____

/*SUMMARY*/
SELECT sortable_name
, canvas_id
, logins
FROM (
SELECT u.sortable_name
, u.canvas_id
, count(distinct p.unique_name) as login_count
FROM user_dim u
JOIN pseudonym_dim p ON p.user_id = u.id
GROUP BY u.sortable_name
, u.canvas_id ) x
WHERE x.logins > 1
ORDER BY logins DESC
, sortable_name
;

/*DETAIL*/
SELECT p.unique_name
, p.sis_user_id
, p.integration_id
, p.workflow_state
, x.canvas_id
, x.sortable_name
FROM canvas_data.pseudonym_dim p
JOIN (
SELECT u.id
, u.sortable_name
, u.canvas_id
, count(distinct p.unique_name) as login_count
FROM user_dim u
JOIN pseudonym_dim p ON p.user_id = u.id
GROUP BY u.id
, u.sortable_name
, u.canvas_id ) x ON x.id = p.user_id
WHERE x.login_count > 1
;

View solution in original post