cancel
Showing results for 
Search instead for 
Did you mean: 
pkreemer
Surveyor

Multiple login users report?

Jump to solution

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 (1)
1 Solution

Accepted Solutions
stimme
Explorer III

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

3 Replies
stimme
Explorer III

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

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é