"email" column does not exist for policy using "users" table query

[jmwatts]

Fleet version: v4.62.0

Web browser and operating system: N/A

---

💥  Actual behavior

level=debug ts=2025-01-17T14:40:46.533137Z query=fleet_policy_query_93 message="no such column: email" hostID=30

🧑‍💻  Steps to reproduce

1. Create a policy with the query SELECT 1 from users where email='[email protected]';
2. Note that email is listed in columns and the policy says it's compatible with Windows, Linux, Mac, and Chromebooks (with fleetd chrome extension)
   
3. Run the policy

🕯️ More info (optional)

osquery does not list email as an available column

There is an additional column "include_remote" that we do not list. I don't know if it works because I don't have an AD server to bind to but maybe worth investigating.

🛠️ To fix

Update tooltip text for "Compatible with":

Estimated compatibility based on the tables used in this query. Check the table documentation (schema) to verify compatibility of individual columns.

Only live queries are supported on ChromeOS.

Querying iPhones & iPads is not supported.

[RachelElysia]

@jmwatts when you hover over the email key, you can see a tooltip that says only available on Chrome so the error you're seeing with users is expected. Please cmiiw though!

This comes from /schema/tables/users.yml that /schema/tables/ directory is used to add tables/columns that do not match directly to osquery, and then we (usually Eric Shaw) run a script that Eric Shaw wrote website/scripts/generate-merged-schema.js to merge that with the osquery tables. (Our tables are more robust, especially because ChromeOS doesn't use Osquery)

Hope that helps clarify things.

As for include_remote, I don't know when we update the osquery tables data.

[sharon-fdm]

Hey team! Please add your planning poker estimate with Zenhub @dantecatalfamo @jacobshandling @lucasmrod @sgress454

[jmwatts]

@sharon-fdm as @RachelElysia pointed out, this may not be a bug.
Per the hover text, the email field only works for ChromeOS (which only works if you have the fleetd Chrome extension)
If we are happy that the hover text points out that it's only "available on chrome", then this can be closed as not a bug.

[rachaelshaw]

Updated the issue description. We'll update the "Compatible with" tooltip to clarify that we're not taking individual columns into account:

Estimated compatibility based on the tables used in this query. Check the table documentation (schema) to verify compatibility of individual columns.

Only live queries are supported on ChromeOS.

Querying iPhones & iPads is not supported.