[ARCHIVED] Multiple login users report?

[pkreemer]

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!

[stimme · Accepted 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
;

[pkreemer]

Thanks, Sam, that looks like just what I want!

Paul

[jlugo1]

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é