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.

Solutions

a small chevron
Device management

Device management

Remotely manage, and protect laptops and mobile devices.

Orchestration

Orchestration

Automate tasks across devices, from app installs to scripts.

Software management

Software management

Inventory, patch, and manage installed software.

Extend Fleet

Extend Fleet

Integrate your favorite tools with Fleet.


Customers

a small chevron
Stripe + Fleet

Stripe + Fleet

Stripe consolidates multiple tools with Fleet.

Foursquare + Fleet

Foursquare + Fleet

Foursquare quickly migrates to Fleet for device management.

What people are saying

What people are saying

Stories from the Fleet community.


Pricing

More

a small chevron
Docs

Docs

Guides

Guides

Support

Support

News

News

Get your license

Get your license

The handbook

The handbook

GitOps for device management

In-person workshop for beginners.

Join us
Get a demo Try it yourself
Solutions A small chevron
Device management

Device management

Remotely manage, and protect laptops and mobile devices.

Orchestration

Orchestration

Automate tasks across devices, from app installs to scripts.

Software management

Software management

Inventory, patch, and manage installed software.

Extend Fleet

Extend Fleet

Integrate your favorite tools with Fleet.

Customers A small chevron
Stripe + Fleet

Stripe + Fleet

Stripe consolidates multiple tools with Fleet.

Foursquare + Fleet

Foursquare + Fleet

Foursquare quickly migrates to Fleet for device management.

What people are saying

What people are saying

Stories from the Fleet community.

Pricing
More A small chevron

GitOps for device management

In-person workshop for beginners.

Join us
Docs

Docs

Guides

Guides

Support

Support

News

News

Get your license

Get your license

The handbook

The handbook

Try it yourself Get a demo
{{categoryFriendlyName}}/
{{thisPage.meta.articleTitle}}
search

Osquery: Consider joining against the users table

{{articleSubtitle}}

| The author's GitHub profile picture

Zach Wasserman

Share

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 Get a demoGet a demo
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
×