# Check user email confirmed via DataExplorer **URL:** \ **Category:** Support\ **Created:** [November 4, 2020, 6:23am UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187 "2020-11-04T06:23:12Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![ds.discourse](https://avatars.discourse-cdn.com/v4/letter/d/c67d28/32.png) [@ds.discourse](https://meta.discourse.org/u/ds.discourse)\ **Post date:** [November 4, 2020, 6:23am UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/1 "2020-11-04T06:23:12Z")
Via the API, I need to determine whether user ids have already confirmed their email address or not. Using DataExplorer I am querying the _email\_tokens_ table, but am confused about how to interpret multiple rows for a given user id. When there are multiple rows, N-1 of them have _expired = true_, but the row with _expired = false_ doesn’t seem to be the current state of whether they have confirmed their email address. For example there are verified users whose email\_tokens row with expired = false showing _confirmed = false_ Probably going about this the wrong way. Please advise. Thank you! ---
**Author:** ![rishabh](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/rishabh/32/179446_2.png) [@rishabh](https://meta.discourse.org/u/rishabh)\ **Post date:** [November 4, 2020, 10:02am UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/2 "2020-11-04T10:02:28Z")
> [@ds.discourse](#): > > I need to determine whether user ids have already confirmed their email address or not. @michebs is it possible to track this using a [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query? ---
**Author:** ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)\ **Post date:** [November 4, 2020, 10:48am UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/3 "2020-11-04T10:48:39Z")
Hi David, When the user confirms the email, the `confirmed column` is updated, so by adding it as `true` to the filter, you will list only **confirmed emails**. Hope this helps. ```plaintext SELECT user, email FROM email_tokens WHERE expired = false AND confirmed = true ``` ---
**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\ **Post date:** [November 4, 2020, 12:03pm UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/4 "2020-11-04T12:03:44Z")
What are you trying to accomplish? ---
**Author:** ![ds.discourse](https://avatars.discourse-cdn.com/v4/letter/d/c67d28/32.png) [@ds.discourse](https://meta.discourse.org/u/ds.discourse)\ **Post date:** [November 4, 2020, 3:35pm UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/6 "2020-11-04T15:35:58Z")
@michebs - Yes. This is the query that I first tried, but found there is a small subset of our users that are active users, but are not returned by this query. Yet looking at one of those users on their Admin Users page, their email is valid (is not flagged _not verified_ ). @pfaffman - We use this data to drive one of the state transitions in the services we provide to our members both on the site. When a new member signs up and requests services, we want to first know that we can contact them to provide those services. ---
**Author:** ![ds.discourse](https://avatars.discourse-cdn.com/v4/letter/d/c67d28/32.png) [@ds.discourse](https://meta.discourse.org/u/ds.discourse)\ **Post date:** [November 4, 2020, 3:55pm UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/7 "2020-11-04T15:55:31Z")
To show an example, with this query: ```plaintext SELECT e.user_id, e.expired, e.confirmed, e.email FROM email_tokens as e WHERE e.user_id = 33 ``` Looking at one of our most active users returns: ```plaintext user_id,expired,confirmed,email 33,false,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,true,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,false,somebody@somewhere.com 33,true,true,somebody@somewhere.com ``` The only non-expired row shows the email as non-confirmed. ---
**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\ **Post date:** [November 4, 2020, 8:15pm UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/8 "2020-11-04T20:15:51Z")
If you have control over your system, then you might use a Discourse web hook to notify your system that the user is activated. But I’m pretty sure that the easy solution is You can check the `active` field in the user record. I’m pretty sure that it doesn’t get set until they’ve validated their email address. `http://localhost:3000/admin/users/1/jay.json` will give you a record like: ```json { id: 1, username: "jay", name: "Jay User", avatar_template: "/user_avatar/localhost/jay/{size}/3_2.png", email: "jay@nowehre.invalid", secondary_emails: [], active: true, admin: true, moderator: false, last_seen_at: "2020-11-04T20:13:04.240Z", last_emailed_at: null, created_at: "2020-10-05T21:14:52.570Z", last_seen_age: 27.86773951, last_emailed_age: null, created_at_age: 2588319.53785305, trust_level: 1, ..... }, ``` ---
**Author:** ![ds.discourse](https://avatars.discourse-cdn.com/v4/letter/d/c67d28/32.png) [@ds.discourse](https://meta.discourse.org/u/ds.discourse)\ **Post date:** [November 4, 2020, 8:25pm UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/9 "2020-11-04T20:25:23Z")
_users.active_ looks like exactly what I need. Thank you! ---
**Author:** ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)\ **Post date:** [December 4, 2020, 8:34pm UTC](https://meta.discourse.org/t/check-user-email-confirmed-via-dataexplorer/169187/10 "2020-12-04T20:34:15Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.