Osquery is an SQL-powered framework that serves for operating system instrumentation, monitoring, and analytics. It's open-source and was created by Facebook in 2014.
Basically, this framework allows us to use SQL queries to return various data, such as lists of the OS running processes, user accounts created on the host, processes of communication between the OS and certain suspicious domains, etc.
It is widely used by Security Analysts, Incident Responders, Threat Hunters, and so on.
Osquery is available on multiple platforms - Windows, Linux, macOS, and FreeBSD.
Once you have Osquery installed on your system, you can start using it in your terminal by running osqueryi command. Once started, to understand more about the functionality of the tool, you can start by running .help command in the terminal. For example:
ognard@localhost$ osqueryi
Using a virtual database. Need help, type '.help'
osquery> .help
Welcome to the osquery shell. Please explore your OS!
You are connected to a transient 'in-memory' virtual database.
.all [TABLE] Select all from a table
.bail ON|OFF Stop after hitting an error
.connect PATH Connect to an osquery extension socket
.disconnect Disconnect from a connected extension socket
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.features List osquery's features and their statuses
.headers ON|OFF Turn display of headers on or off
.help Show this message
.mode MODE Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns see .width
line One value per line
list Values delimited by .separator string
pretty Pretty printed SQL results (default)
.nullvalue STR Use STRING in place of NULL values
.print STR... Print literal STRING
.quit Exit this program
.schema [TABLE] Show the CREATE statements
.separator STR Change separator used by output mode
.socket Show the local osquery extensions socket path
.show Show the current values for various settings
.summary Alias for the show meta command
.tables [TABLE] List names of tables
.types [SQL] Show result of getQueryColumns for the given query
.width [NUM1]+ Set column widths for "column" mode
.timer ON|OFF Turn the CPU timer measurement on or off
Listing tables
To view all of the available tables that can be queried and inspected, you can use the .tables command.
Using a virtual database. Need help, type '.help'
osquery> .tables
=> appcompat_shims
=> arp_cache
=> atom_packages
=> authenticode
=> autoexec
=> azure_instance_metadata
=> azure_instance_tags
=> background_activities_moderator
=> bitlocker_info
=> carbon_black_info
=> carves
=> certificates
=> chassis_info
=> chocolatey_packages
For instance, if you want to check the tables associated with processes, you can use the command .tables processes:
Using a virtual database. Need help, type '.help'
osquery> .tables processes
=> appcompat_shims
=> arp_cache
=> atom_packages
=> authenticode
=> autoexec
=> azure_instance_metadata
=> azure_instance_tags
=> background_activities_moderator
=> bitlocker_info
=> carbon_black_info
=> carves
=> certificates
=> chassis_info
=> chocolatey_packages
or, if you want to check all of the tables associated to users, you can use .tables user and this will show all of the tables that contain data related to users on the OS:
osquery> .tables user
=> user_groups
=> user_ssh_keys
=> userassist
=> users
Table Schema
Now, in order to find out what information is contained within a table, or in other words, to find the columns of a table, you can use the command .schema table_name:
osquery> .schema users
CREATE TABLE users(`uid` BIGINT, `gid` BIGINT, `uid_signed` BIGINT, `gid_signed` BIGINT, `username` TEXT, `description` TEXT, `directory` TEXT, `shell` TEXT, `uuid` TEXT, `type` TEXT, `is_hidden` INTEGER HIDDEN, `pid_with_namespace` INTEGER HIDDEN, PRIMARY KEY (`uid`, `username`, `uuid`, `pid_with_namespace`)) WITHOUT ROWID;
This example above, provides the schema of the table users, and with that, you have the information for all of the available columns for the users table. After this, you can use SQL query to display the data from particular columns. For example:
osquery> SELECT username FROM users;
+--------------------+
| username |
+--------------------+
| Administrator |
| Guest |
| John |
| SYSTEM |
| LOCAL SERVICE |
| NETWORK SERVICE |
+--------------------+
Schema Documentation
Schema documentation is available at this link. This is a very useful resource where you can filter and preview the information for all of the available tables based on the Osquery version and OS. There is detailed information for every table and its columns.
SQL queries
The SQL language used for querying in Osquery is not the full SQL language that you may be familiar with but is a superset of SQLite.
Basically, your querying will always start with SELECT. UPDATE and DELETE are not possible, except in situations where you may create run-time tables (views) or if you are using an extension that supports updating or deleting data.
Other than SELECT, the following are also available:
- FROM
- LIMIT
- COUNT
- JOIN
- WHERE
- LIKE
- BETWEEN
Check out the documentation for more information on querying in Osquery.
Example queries
COUNT
osquery> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
WHERE
osquery> SELECT uid, gid, username, description FROM users WHERE username="John";
+------+-----+----------+-------------------+
| uid | gid | username | description |
+------+-----+----------+-------------------+
| 1009 | 544 | John | Backend Developer |
+------+-----+----------+-------------------+
JOIN
osquery> SELECT p.pid, p.name, u.username FROM processes p JOIN users u ON u.uid=p.uid LIMIT 10;
+------+-------------------------+----------+
| pid | name | username |
+------+-------------------------+----------+
| 1576 | rdpclip.exe | James |
| 4156 | svchost.exe | James |
| 4560 | svchost.exe | James |
| 3912 | taskhostw.exe | James |
| 2616 | sihost.exe | James |
| 5136 | ctfmon.exe | James |
| 5372 | explorer.exe | James |
| 5660 | ShellExperienceHost.exe | James |
| 5784 | SearchUI.exe | James |
| 5812 | RuntimeBroker.exe | James |
+------+-------------------------+----------+
Top comments (0)