Use OSQuery to Explore Your Operatin System

Imagine having a magnifying glass that can peer into every corner of your operating system, offering insights and revealing details that are typically obscured. That’s OSQuery.

OSQuery is an open-source command-line tool that lets you query your operating system as if it were a database. Think of it as SQL, but instead of querying tables, you’re querying various aspects of your OS, from running processes to loaded kernel modules, file system information, and more. This makes it an incredibly versatile tool for system monitoring, analysis, and even cybersecurity.

What You Need

To complete this tutorial, you’ll need Homebrew installed, which you can do by following the Install Homebrew tutorial.

Install OSQuery

OSQuery is available on macOS, Windows, and Linux.

Install OSQuery on macOS via Homebrew with brew install osquery.

For Ubuntu and Debian-based Linux distributions, install with sudo apt install osquery.

For Windows, download the latest release from the OSQuery downloads page and run the installer.

Once OSQuery is installed, you can use it to inspect various aspects of your OS.

Explore the OSQuery Interactive Interface

To run OSQuery, open a command prompt or terminal window and run the command osqueryi.

osqueryi

This opens the interactive query prompt, where you’ll execute SQL queries to get information about your system.

Start by looking at system processes. At the osquery> prompt, enter the following query to see the top five processes using up the most memory:

SELECT pid, name, total_size FROM processes ORDER BY total_size DESC LIMIT 5;

This shows the process ID, name, and total memory size, but you can display other fields as well. Inspect the schema of the table with the following command:

.schema processes

You can also view the documentation to see the available schemas.

You can also check your open network connections:

SELECT * FROM listening_ports WHERE port != '0';

Since port 0 is not used for standard network services, filtering it out reduces extra noise.

Finally, you can join tables together. For example, you can users and the processes they’re running:

SELECT users.username, processes.name FROM users JOIN processes USING (uid);

Once you’re done querying your system, press CTRL+C to return to your prompt.

Passing Queries to OSQuery

You can pass queries directly to the osqueryi command tool. This way you don’t have to open the interactive prompt.

Try running the following command to see the logged-in users on your system:

osqueryi "SELECT user, time FROM logged_in_users ORDER BY time DESC LIMIT 10;"

You can use this approach in scripts, or even on a schedule.

Conclusion

OSQuery converts complex system data into a queryable format, but its real power lies in its ability to run more complex queries that can cross-reference different aspects of your system. You can create scheduled queries, generate reports, and even integrate OSQuery’s output into other monitoring tools for a comprehensive view of your system’s health and security.