Fleet logo
Menu An icon indicating that interacting with this button will open the navigation menu.
Fleet logo An 'X' icon indicating that this can be interacted with to close the navigation menu.
Multi platform
Device management   (+ MDM) Orchestration   (+ monitoring) Software management   (+ CVEs) Integrations

Docs
Stories
News Ask around Share your story COMPANY
The handbook What people are saying

Pricing Schedule a demo
Multi platform
Device management + MDM Orchestration + monitoring Software management + CVEs, usage, app library Integrations
Docs
Stories
News Ask around Schedule a demo Share your story COMPANY The handbook What people are saying
Pricing Try it yourself
{{categoryFriendlyName}}/
{{thisPage.meta.articleTitle}}
search

Osquery: Consider joining against the users table

{{articleSubtitle}}

| The author's GitHub profile picture

Zach Wasserman

Share this article on Hacker News Share this article on LinkedIn Share this article on Twitter

On this page

{{topic.title}}
Docs Docs REST API REST API Guides Guides Talk to an engineer Talk to an engineer
Suggest an editSuggest an edit

Try it out

See what Fleet can do

Start now
macOS Windows Linux

Osquery: Consider joining against the users table

{{articleSubtitle}}

| The author's GitHub profile picture

Zach Wasserman

Osquery: Consider joining against the users table

Proper use of JOIN to return osquery data for users

Osquery: Consider joining against the users table

Many an osquery user has encountered a situation like the following:

$ osqueryi
Using a virtual database. Need help, type '.help'
osquery> SELECT uid, name FROM chrome_extensions LIMIT 3;
+-----+--------------------------------------------+
| uid | name                                       |
+-----+--------------------------------------------+
| 501 | Slides                                     |
| 501 | Docs                                       |
| 501 | 1Password extension (desktop app required) |
+-----+--------------------------------------------+
osquery>

$ sudo osqueryi
Using a virtual database. Need help, type '.help'
osquery> SELECT uid, name FROM chrome_extensions LIMIT 3;
W0519 09:35:27.624747 415233472 virtual_table.cpp:959] The chrome_extensions table returns data based on the current user by default, consider JOINing against the users table
W0519 09:35:27.625207 415233472 virtual_table.cpp:974] Please see the table documentation: https://osquery.io/schema/#chrome_extensions

Our query runs as expected when osqueryi is run as a normal user, but returns a warning message and no results when run as root via sudo osqueryi.

This same issue manifests on many tables that include a uid column:

  • authorized_keys
  • chrome_extension_content_scripts
  • chrome_extensions
  • crashes
  • docker_container_processes
  • firefox_addons
  • known_hosts
  • opera_extensions
  • safari_extensions
  • shell_history
  • user_ssh_keys

What’s going on here?

As stated in the error message, these tables return “data based on the current user by default”. When run as a normal user, the implementations know to look in paths relative to the user’s home directories. A query running as root does not know which directories to check.

The solution

Show osquery which users to retrieve the data for. Typically this is achieved by a JOIN against the users table to retrieve data for every user on the system:

SELECT uid, name
FROM users CROSS JOIN chrome_extensions USING (uid)

Writing the query with this JOIN ensures that osquery first generates the list of users, and then provides the user uids to the chrome_extensions table when generating that data.

Note: It is important to use CROSS JOIN as this tells the query optimizer not to reorder the evaluation of the tables. If we use a regular JOIN it is possible that reordering could result in the original error being encountered (because the chrome_extensions table generates with no uid in its context).

Fleet logo
Multi platform Device management Orchestration Software management Integrations Pricing
Documentation Support Docs API Release notes Get your license
Company About News Jobs Logos/artwork Why open source?
ISO 27001 coming soon a small checkmarkSOC2 Type 2 Creative Commons Licence CC BY-SA 4.0
© 2025 Fleet Inc. Privacy
Slack logo GitHub logo LinkedIn logo X (Twitter) logo Youtube logo Mastadon logo
Tried Fleet yet?

Get started with Fleet

Start
continue
×