cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Community Member

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
Highlighted
Learner II

Hi pkreemer@sbctc.edu‌,

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

2 Replies
Highlighted
Learner II

Hi pkreemer@sbctc.edu‌,

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

Highlighted
Community Member

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

Paul

0 Kudos