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.

Install osquery

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

using osqueryi command

Show the current osquery configurations and settings.

.show

Show osquery configuration

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

Get a list of all 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

Get osquery columns

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;

Show system hardware details

Get OS Version

Show the current operating system info, including the os version, platform, os patch, and codename.

SELECT * FROM os_version;

Show details about the OS

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;

get details about the used Linux Kernel

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;

Check installed packages on Ubuntu

For a specific package, add the name filter.

SELECT name, version FROM deb_packages WHERE name="nginx";

get details about a specific package, in this case 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;

Get Package details on CentOS

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";

Details about 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";

Get disk mounts

Memory Info

Checking the system memory in bytes.

SELECT * FROM memory_info;

Get memory details

Network Interface Info

Checking the network address using 'interface_addresses'.

SELECT * FROM interface_addresses;

Network Interface Info

Checking the network interface details using 'interface_details'.

SELECT * FROM interface_details;
SELECT interface, mac, ipackets, opackets, ibytes, obytes FROM interface_details;

Network 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;

System user details

To check the last login users, use the 'last' table.

SELECT * FROM last;

Last login

For normal users, fill 'type' to '7'.

SELECT username, time, host FROM last WHERE type=7;

Get details about normal users only

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;

IP Tables Firewall Info

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;

Details about Linux processes

Checking Cron Job

Check available cron job and time of script run using the 'crontab' table.

SELECT * FROM crontab;

Cronjobs

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;

List od SUID files

And all the above is the basic Linux system monitoring using osquery.

Reference

Share this page:

Suggested articles

1 Comment(s)

Add comment

Comments

By: SDD at: 2018-08-20 13:12:57

"Created by Facebook". Does this fact reassure anyone?