are there fields for user_email and created_by _sis?

[martin_kabat]

I a setting up a query to check for users that need to be removed from our system.

We allocated two fields that would be helpful user_email and created_by_sis. However, I can not find them in canvas data tables.

Can anyone help?

[jerry_nguyen]

@martin_kabat 

The field for a user's email (Aka login id) is unique_name in the pseudonym_dim table. 

As for created by sis, normally if a user is created externally via an SIS, it will assign the ID to sis_user_id (in pseudonym_dim table). However, if you want to differentiate between a user created by SIS or API, you can refer to this post here https://community.canvaslms.com/t5/Canvas-Developers-Group/How-to-know-Enrollments-done-by-quot-Self...

[martin_kabat]

thank you

created_by _sis can be found on report from Canvas and helps us to differentiate between our system import and self enrolments

[stimme]

@martin_kabat Hello. I notice you say, "We allocated two fields that would be helpful user_email and created_by_sis." Does allocating fields mean adding custom fields using the Canvas Users API? (https://canvas.emory.edu/doc/api/users.html#method.custom_data.set_data) If so, you must use the related Canvas Users API to load custom user data, because it is not part of the Canvas Data schema.

For email addresses in Canvas Data, you could join the user_dim table and the communication_channel_dim tables on user_dim.id = communication_channel_dim.user_id and select where communication_channel_dim.type = 'email'.