User Emails (or Student IDs ) CD2

[ZaidGandhi]

Hi, 

Does anyone know a way to find student IDs or user emails from CD2?

I have checked users and accounts tables in the canvas namespace - but they don't have "email" column. I only have the primary key of userID and accountID and only their full name. 

Is there some other place that I should be looking?

Thanks

Z

[aasmund_kvamme]

I'd also love to find this. I keep a "local" table that connects UserID and StudentId/email ☹️

[robotcars]

SIS ID for users is in pseudonyms table, and emails are in the communication_channels table.

https://data-access-platform-api.s3.eu-central-1.amazonaws.com/schema.json

[ZaidGandhi]

Great! Thanks for that - legend!

[ZaidGandhi]

Also 2 questions to you as I asked the same to Pete in the thread below:

1. I am not sure but looks like there is no need for communication_channels - atleast to get the Emails and SIS ID. The pseudonym table has the `Unique_ID` as Email and sis_user_id. Am I missing something here? 

2. Are you aware of this link to get the datasets schema? Was curious why are you using the JSON link for the schema. I presume you are doing something differntly?

Datasets — Data Access Platform Datasets documentation

[robotcars]

@ZaidGandhi 

1) UniqueID is the login_id, it could be an email, username, or whatever the institution has configured.

2) Noise, I guess.  The JSON page is fine for my purposes, Firefox makes it easily readable.

[Pete5484]

Keep in mind that a user can have more than one pseudonym and more than one communication channel. For instance:

SELECT
u.sortable_name,
p.user_id, 
p.unique_id, 
p.sis_user_id, 
p.authentication_provider_id,
cc.path_type, -- e.g. 'email', 'SMS' etc).
cc.path, -- e.g person@example.com

FROM
canvas.users u
LEFT JOIN canvas.pseudonyms p ON p.user_id = u.id
LEFT JOIN canvas.communication_channels cc ON cc.pseudonym_id = p.id;
 

You can use DISTINCT ON to select only one email per user/pseudonym

ie SELECT DISTINCT ON (p.user_id) ... WHERE cc.path_type = 'email' ORDER BY p.user_id, cc.path; 

OR use a CTE with LIMIT 1. Ideally you would also filter using workflow state.

[ZaidGandhi]

Great! Thanks for the query Pete. Yes I was planning to filter on workflow state too - I am doing the same for the courses table. 

I am not sure but looks like there is no need for communication_channels - atleast to get the Emails and SIS ID

The pseudonym table has the `Unique_ID` as Email and sis_user_id.

Am I missing something here? 

Below is the CTE that I'd be using to bring in Emails & SIS_ID to the users.

SELECT
	USER_ID,
	CREATED_AT,
	UPDATED_AT,
	WORKFLOW_STATE AS USERSTATUS,
	SIS_BATCH_ID,
	UNIQUE_ID AS EMAIL,
	LOGIN_COUNT,
	--LAST_REQUEST_AT,
	LAST_LOGIN_AT,
	--CURRENT_LOGIN_AT,
	SIS_USER_ID
FROM
	CANVAS.PSEUDONYMS

[stimme]

canvas.pseudonyms.unique_id represents login_id, not email per se. It sounds like your institution uses email address as the value for login_id. In that case, the unique_id values should match canvas.communication_channels.path for their email address. You probably do not need to bother with the communications_channels table.

[Pete5484]

Yep, if you're using something like School Data Sync/SSO then you unique_id (login_id) will be an email address. But like  @ZaidGandhi mentioned, it could be a username, a student/staff id number, etc.

If users are able to choose their preferred email address (ie not locked down in Account settings) then email (cc.path) might still differ from p.unique_id. It can also be relevant for LTI configs that use default email.