Data Centers

How to monitor your data center Linux server with osquery

If you're looking to get as much information about your data center Linux servers as possible, Jack Wallen shows you how to install and use osquery on Ubuntu Server.

Image: Jack Wallen

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.

Installing osquery

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:

export OSQUERY_KEY=1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
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.

Configuring osquery

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:

template(
  name="OsqueryCsvFormat"
  type="string"
  string="%timestamp:::date-rfc3339,csv%,%hostname:::csv%,%syslogseverity:::csv%,%syslogfacility-text:::csv%,%syslogtag:::csv%,%msg:::csv%\n"
)
*.* 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:

{
    "options": {
        "config_plugin": "filesystem",
        "logger_plugin": "filesystem",
        "logger_path": "/var/log/osquery",
        "disable_logging": "false",
        "log_result_events": "true",
        "schedule_splay_percent": "10",
        "pidfile": "/var/osquery/osquery.pidfile",
        "events_expiry": "3600",
        "database_path": "/var/osquery/osquery.db",
        "verbose": "false",
        "worker_threads": "2",
        "enable_monitor": "true",
        "disable_events": "false",
        "disable_audit": "false",
        "audit_allow_config": "true",
        "host_identifier": "hakase-labs",
        "enable_syslog": "true",
        "syslog_pipe_path": "/var/osquery/syslog_pipe",
        "force": "true",
        "audit_allow_sockets": "true",
        "schedule_default_interval": "3600"
    },


    "schedule": {
        "crontab": {
            "query": "SELECT * FROM crontab;",
            "interval": 300
        },
        "system_info": {
            "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
            "interval": 3600
        },
        "ssh_login": {
            "query": "SELECT username, time, host FROM last WHERE type=7",
            "interval": 360
        }
    },

    "decorators": {
        "load": [
            "SELECT uuid AS host_uuid FROM system_info;",
            "SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
        ]
    },

    "packs": {
        "osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf"
    }
}

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

Basic usage

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:

osqueryi

You can issue the command .help to see a list of basic commands (Figure A).

Figure A

Figure A

The osquery help file.


You can see a list of the various tables osquery uses (Figure B) to store information by issuing the command .tables.

Figure B

Figure B

A sample listing of tables used by osquery.


Let's say you want to read the schema of the system_info table. To do that, issue the command:

.schema system_info

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).

Figure C

Figure C

Information about the system CPU.


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).

Figure D

Figure D

Information about the running Linux kernel.


Keep learning

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.

Also see

About Jack Wallen

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.

Editor's Picks

Free Newsletters, In your Inbox