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!
Solved! Go to Solution.
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
;
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
;
Thanks, Sam, that looks like just what I want!
Paul
Hi Sam,
And thanks for sharing these SQL queries. I hope you or someone can help.
Can these queries help identify multiple duplicate and triplicate logins that were created using Google O_Auth?
From the outset, my org used G-O_auth. We switched to the local login in August 2020 and discovered many duplicates. We need to identify a process for merging or deleting these logins.
Any assistance is greatly appreciated.
José