About Queries

Get Started with OSquery

Collecting the right data requires asking the right questions. For anyone getting started, they must know the data’s location to know how to ask for it. The only way to ask the right questions is to understand how to ask, and it comes back to knowing the schema.

Understand the schema: https://osquery.io/schema/5.5.1/.

As of May 7, 2024, there are 274 tables in the schema. You can use all tables with the exception of:

• Tables with _events in the name.
• Tables that require YARA support.
• curl and carves tables.

Best Practices for Queries

Follow these best practices:

• Format the query so that it's easy to read, and keep it simple.
• Use Join and Sub searches judiciously.
• Define the information you want to collect and scope the query to that information.

  For example, if you're interested in running processes which could consume a lot of resources on the instance, you could write

  SELECT * from processes

  but that would generate a large volume data, possibly most of it unnecessary.

  Better practice would be to select only the columns you need, and to set an order by and limit.

  Here's an example that gets you a nicely formatted table for top 10 processes by memory consumption.

  SELECT pid, name, ROUND((total_size * '10e-7'), 2) AS memory_used FROM processes ORDER BY total_size DESC LIMIT 10;

Query Packs

OSquery query packs are pre built or custom sets of queries that are organized by type, such as compliance, or vulnerability management. You can use query packs to help you understand how your system responds to sets of queries and if necessary tailor the queries to your environment.

Find out more about query packs at osquery.readthedocs.io, Query packs.

You can find useful query packs online, for example this one on GitHub, https://github.com/osquery/osquery/tree/master/packs.

Example Queries

You can get started by running some basic local queries such as SELECT * FROM users or SELECT * FROM processed LIMIT 10 to learn how the tables work.

Also we have put together a list of suggested queries to help you get up and running.

1. Users (users): Search for all local user accounts that exist on a machine.

   Linux (Ubuntu):

   SELECT * FROM users WHERE gid < 65534 AND uid >= 1000;

   Note
   
   uid 1000 is where users typically start on Linux

   Linux: This query finds all users who have actual login shells (not bin/false or bin/true, with allowances for them living in different places, for example, /usr/bin/false or /bin/false.

   SELECT * FROM users WHERE shell NOT LIKE '%false' AND shell NOT LIKE '%true';
2. Process Open Sockets (process_open_sockets): Searches for processes making network connections, other than web connections.

   SELECT s.pid, p.name, local_address, remote_address, family, protocol, local_port, remote_port FROM process_open_sockets s JOIN processes p ON s.pid = p.pid WHERE remote_port NOT IN (80, 443) AND local_port NOT IN (0) AND family = 2;

3. Listening Ports (listening_ports): Looks for open ports on a system.

   SELECT DISTINCT p.pid, p.name, l.port FROM listening_ports AS l JOIN processes as p ON l.pid = p.pid WHERE l.address = '0.0.0.0';

4. Logged-In Users (logged_in_users): Shows who is currently logged in to a system.

   SELECT liu.*, p.name, p.cmdline, p.cwd, p.root FROM logged_in_users liu, processes p WHERE liu.pid = p.pid;

5. File (file): Returns information about the specified file on disk. The following queries, finds all files in all user Downloads folders to one level of folder depth.

   Linux:

   SELECT file.path, users.username AS owner, groups.groupname AS groups, datetime(file.btime,'unixepoch') AS created, datetime(file.mtime,'unixepoch') AS last_mod, ROUND((file.size * 10e-7),4) AS size_mb FROM file JOIN users USING (uid) JOIN groups USING (gid) WHERE path LIKE '/home/%/Downloads/%%' ORDER BY last_mod DESC;

   Windows:

   SELECT file.path, users.username AS owner, datetime(file.btime,'unixepoch') AS created, datetime(file.mtime,'unixepoch') AS last_mod, ROUND((file.size * 10e-7),4) AS size_mb FROM file JOIN users USING (uid) WHERE path LIKE 'c:\Users\%\Downloads\%%' ORDER BY last_mod DESC;