How to Monitor your Linux Server using osquery
Osquery is an open source Operating System monitoring, query, and analytics software. Created by Facebook, it exposes an operating system as a high-performance relational database that can be queried using SQL-based queries.
Osquery is a multi-platform software, can be installed on Linux, Windows, MacOS, and FreeBSD. Osquery allows us to explore the operating system profile, performance, security and many more metrics by using SQL-based queries.
In this tutorial, I will show you how to install osquery on the Linux distributions Ubuntu 18.04 LTS and CentOS 7. We will install osquery, learn how to use the 'osqueryi' interactive mode, and how to monitor a live system using osquery.
Prerequisites
- Linux OS (Ubuntu 18.04 or CentOS 7)
- Root privileges
What we will do
- Install osquery on Linux Operating System
- Basic Usage of osqueryi Interactive Mode
- Monitoring System using osquery
Step 1 - Install osquery on Linux Operating System
osquery provides its own repository for each platform. Ithis step, we will install the osquery package from the official osquery repository.
On Ubuntu
Add the osquery key.
export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys $OSQUERY_KEY
Install the osquery package on Ubuntu.
sudo add-apt-repository 'deb [arch=amd64] https://pkg.osquery.io/deb deb main'
sudo apt install osquery -y
On CentOS
Add the osquery key.
curl -L https://pkg.osquery.io/rpm/GPG | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
Install the osquery package on CentOS 7.
sudo yum-config-manager --add-repo https://pkg.osquery.io/rpm/osquery-s3-rpm.repo
sudo yum-config-manager --enable osquery-s3-rpm
sudo yum install osquery
Wait for all packages to be installed.
Step 2 - Basic Usage of osqueryi Interactive Mode
osquery provides two main interfaces to the users, osqueryi, and osqueryd.
osqueryi is the osquery interactive query console. It's like the 'mysql' command shell on the MySQL and the 'psql' shell on PostgreSQL database.
In this step, we will learn the basic usage of the 'osqueryi' interactive mode.
Type the 'osqueryi' command on the server terminal, and you will get the osquery interactive console mode.
osqueryi
Next, we will learn about the basic command of the osqueryi console mode.
Show all basic available commands on the interactive mode osqueryi.
.help
Show the current osquery configurations and settings.
.show
Osquery provides multiple view modes to show query results. The default mode is the 'pretty' mode.
Now we'll change the view mode of query results, for this guide, we will be using the 'line' mode.
.mode csv
.mode list
.mode column
.mode line
.mode pretty
Osquery exposes the operating system as a relational database system. All info about the system is stored in the osquery tables and we can explore the whole system info by querying all available tables.
To get a list all available tables in the osquery, run the command below.
.tables
Once we know all the available tables in the osquery system, we will look at the columns.
Use the following command to get the schema (columns, types) of tables.
.schema users
.schema processes
And you will get all schema columns of the table.
Step 3 - Basic Linux Monitoring using query
In this step, we will monitor a live Linux system using the osquery. We will monitor the system profile info, users, network interfaces etc through the osqueryi interactive mode.
Get System Info
Show details about the system hardware.
SELECT * FROM system_info;
SELECT hostname, cpu_type, physical_memory, hardware_vendor, hardware_model FROM system_info;
Get OS Version
Show the current operating system info, including the os version, platform, os patch, and codename.
SELECT * FROM os_version;
View Kernel Version and Modules
To check the kernel info of the system, osquery provides the tables 'kernel_info' and the 'kernel_modules'.
Show the kernel used by the system.
SELECT * FROM kernel_info;
Show all loaded kernel modules on the system.
SELECT * FROM kernel_modules LIMIT 5;
Checking Repository and Packages
osquery provides tables for checking repositories and installed packages on both Linux Ubuntu and CentOS.
- On Ubuntu
On Ubuntu, we can check the available repositories through the 'apt_sources' and check packages installed through the 'deb_packages'.
Check all available Ubuntu repositories.
SELECT * FROM apt_sources;
SELECT name, base_uri, release, maintainer, components FROM apt_sources ORDER BY name;
Check all packages installed using the deb_packages table.
SELECT * FROM deb_packages;
Show only the name of package and version.
SELECT name, version FROM deb_packages ORDER BY name;
For a specific package, add the name filter.
SELECT name, version FROM deb_packages WHERE name="nginx";
- On CentOS
On CentOS, we can check the available repository through the 'yum_sources' and check packages installed through the 'rpm_packages'.
Check all available CentOS repositories.
SELECT * FROM yum_sources;
SELECT name, baseurl, enabled FROM yum_sources;
Check enabled repository by adding the 'enabled' filter.
SELECT name, baseurl, enabled FROM yum_sources WHERE enabled=1;
Check all packages installed using the rpm_packages table.
SELECT * FROM rpm_packages;
SELECT name, version FROM rpm_packages ORDER BY name;
For specific package name, add the name filter.
SELECT name, version FROM rpm_packages WHERE name="firewalld";
Mount Disk Info
We can use the mounts table to check all details about the system drive, including free inodes, flags, type etc.
Check all disks mounted by the system.
SELECT * FROM mounts;
SELECT device, path, type, inodes_free, flags FROM mounts;
For the specific type of device.
SELECT device, path, type, inodes_free, flags FROM mounts WHERE type="ext4";
SELECT device, path, type, inodes_free, flags FROM mounts WHERE type="tmpfs";
Memory Info
Checking the system memory in bytes.
SELECT * FROM memory_info;
Network Interface Info
Checking the network address using 'interface_addresses'.
SELECT * FROM interface_addresses;
Checking the network interface details using 'interface_details'.
SELECT * FROM interface_details;
SELECT interface, mac, ipackets, opackets, ibytes, obytes FROM interface_details;
Server Uptime
Checking the server uptime.
SELECT * FROM uptime;
Checking User
osqery provides detail tables for checking system users. We can use the 'users' table to check all users on the system, using the 'last' table to check users last login, and using the 'logged_in_users' to get the logged in user with the active shell.
To check all available users on the server, use the 'users' table.
SELECT * FROM users;
For normal users, we can specify the uid to '>=1000'.
SELECT * FROM users WHERE uid>=1000;
To check the last login users, use the 'last' table.
SELECT * FROM last;
For normal users, fill 'type' to '7'.
SELECT username, time, host FROM last WHERE type=7;
Checking the logged in user with active shell, use the 'logged_in_users' tables.
SELECT * FROM logged_in_users;
IP Tables Firewall Info
With the 'tables' table, we can check all available rules of the firewall, including the chain, policy, src/dst IP and port etc.
Show all iptables rules.
SELECT * FROM iptables;
Specify the rule using the custom query below.
SELECT chain, policy, src_ip, dst_ip FROM iptables WHERE chain="POSTROUTING" order by src_ip;
Process Info
We can check all application process by using the 'processes' table. It provides detailed info about the process including pid, name, path, command etc.
Basic processes query for checking all running apps.
SELECT * FROM processes;
Specify columns for pid etc, path, and the command.
SELECT pid, name, path, cmdline FROM processes;
Checking Cron Job
Check available cron job and time of script run using the 'crontab' table.
SELECT * FROM crontab;
SUID Binary File
SUID (Set owner User ID up on execution) is a special type of file permissions given to a file and mostly binary executable files.
Check all available said binary file.
SELECT * FROM suid_bin;
Specify the username and group name.
SELECT * FROM suid_bin WHERE username="root" AND groupname="nobody" order by path;
And all the above is the basic Linux system monitoring using osquery.
