finid and Brian Hogan
osquery is an open-source security tool that takes an operating system and turns it into one giant database, with tables that you can query using SQL-like statements. With these queries, you can monitor file integrity, check on the status and configuration of the firewall, perform security audits of the target server, and more.
It’s a cross-platform application with support for recent versions of macOS, Windows 10, CentOS, and Ubuntu. It’s offically described as an “SQL-powered operating system instrumentation, monitoring, and analytics” framework, and originated from Facebook.
With osquery, you can run commands like select * from logged_in_users ;
against your server, and get back a result like this:
Output+-----------+----------+-------+------------------+------------+------+
| type | user | tty | host | time | pid |
+-----------+----------+-------+------------------+------------+------+
| login | LOGIN | ttyS0 | | 1483580429 | 1546 |
| login | LOGIN | tty1 | | 1483580429 | 1549 |
| user | root | pts/0 | 24.27.68.82 | 1483580584 | 1752 |
| user | sammy | pts/1 | 11.11.11.11 | 1483580770 | 4057 |
| boot_time | reboot | ~ | 4.4.0-57-generic | 1483580419 | 0 |
| runlevel | runlevel | ~ | 4.4.0-57-generic | 1483580426 | 53 |
+-----------+----------+-------+------------------+------------+------+
If this appeals to you, you’ll love using osquery as a system security monitoring and intrusion detection tool for your server.
Installing osquery gives you access to the following components:
osqueryi
: The interactive osquery shell, for performing ad-hoc queries.osqueryd
: A daemon for scheduling and running queries in the background.osqueryctl
: A helper script for testing a deployment or configuration of osquery. It can also be used instead of the operating system’s service manager to start/stop/restart osqueryd
.osqueryi
and osqueryd
are independent tools. They don’t communicate, and you can use one without the other. Most of the flags and options needed to run each are the same, and you can launch osqueryi
using osqueryd
’s configuration file so you can customize the environment without using lots of command-line switches.
In this tutorial, you’ll:
osqueryi
and osqueryd
.osqueryi
to look for security issues.Logs generated by osqueryd
, the daemon, are intended to be shipped off to external logging endpoints that require additional expertise to set up and use properly. This tutorial will not cover that configuration, but you’ll learn how to configure and run the daemon and save results locally.
To complete this tutorial, you’ll need to have the following in place:
You should also have a basic understanding of SQL and a fundamental knowledge of Linux system security.
You can install osquery by compiling it from source, or by using the package manager. Since there’s no installable package in the official Ubuntu repository, you’ll have to add the project’s official Ubuntu repository to the system.
First, add the repository’s public key:
- sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 1484120AC4E9F8A1A577AEEE97A80C63C9D8B80B
Then add the repository:
- sudo add-apt-repository "deb [arch=amd64] https://osquery-packages.s3.amazonaws.com/xenial xenial main"
Update the package database:
- sudo apt-get update
Finally, install osquery:
- sudo apt-get install osquery
Out of the box, osquery is not incredibly useful; it is not a plug-and-play application. Whether you intend to use the interactive shell or the daemon, you have to pass some flags and options, either from the command line or via a configuration file. To view the flags and options available to the daemon, type:
- osqueryd --help
The output will include dozens of command-line flags and configuration options. Shown below is a partial output from the test server used for this article.
Outputosquery 2.1.2, your OS as a high-performance relational database
Usage: osqueryd [OPTION]...
osquery command line flags:
--flagfile PATH Line-delimited file of additional flags
--config_check Check the format of an osquery config and exit
--config_dump Dump the contents of the configuration
--config_path VALUE Path to JSON config file
--config_plugin VALUE Config plugin name
--config_tls_endpoint VALUE TLS/HTTPS endpoint for config retrieval
--config_tls_max_attempts VALUE Number of attempts to retry a TLS config/enroll request
--config_tls_refresh VALUE Optional interval in seconds to re-read configuration
--daemonize Run as daemon (osqueryd only)
...
...
osquery configuration options (set by config or CLI flags):
--audit_allow_config Allow the audit publisher to change auditing configuration
--audit_allow_sockets Allow the audit publisher to install socket-related rules
--audit_persist Attempt to retain control of audit
--aws_access_key_id VALUE AWS access key ID
--aws_firehose_period VALUE Seconds between flushing logs to Firehose (default 10)
--aws_firehose_stream VALUE Name of Firehose stream for logging
--aws_kinesis_period VALUE Seconds between flushing logs to Kinesis (default 10)
--aws_kinesis_random_partition_key Enable random kinesis partition keys
--aws_kinesis_stream VALUE Name of Kinesis stream for logging
--aws_profile_name VALUE AWS profile for authentication and region configuration
--aws_region VALUE AWS region
To see additional command-line flags only available to the interactive shell, type:
- osqueryi --help
Running osqueryi
is the easiest method for listing and querying the osquery tables available out of the box. As an example, launch it using the following command:
- osqueryi --verbose
This will place you in an interactive shell, and you’ll see an output similar to this:
OutputI0105 01:52:54.987584 4761 init.cpp:364] osquery initialized [version=2.1.2]
I0105 01:52:54.987808 4761 extensions.cpp:351] Could not autoload extensions: Failed reading: /etc/osquery/extensions.load
I0105 01:52:54.987944 4761 extensions.cpp:364] Could not autoload modules: Failed reading: /etc/osquery/modules.load
I0105 01:52:54.988209 4761 init.cpp:606] Error reading config: config file does not exist: /etc/osquery/osquery.conf
I0105 01:52:54.988334 4761 events.cpp:886] Error registering subscriber: socket_events: Subscriber disabled via configuration
I0105 01:52:54.993973 4763 interface.cpp:307] Extension manager service starting: /home/sammy/.osquery/shell.em
Using a virtual database. Need help, type '.help'
osquery>
Because of the error and information messages in the output, it’s obvious that all parts of osquery are not functioning correctly. Certain queries, like select * from yara ;
w ill return nothing, showing that the table has not been populated with data.
Other queries, like select time, severity, message from syslog ;
will return a message like the following, indicating there’s more work we need to do:
OutputW1202 15:44:48.600539 1720 virtual_table.cpp:492] Table syslog is event-based but events are disabled
W1202 15:44:48.600587 1720 virtual_table.cpp:499] Please see the table documentation: https://osquery.io/docs/#syslog
We’ll make some modifications to our server’s configuration to resolve this issue.
Exit the console by typing:
- .exit
In the next section, we’ll modify aspects of the operating system that osquery needs to function properly.
In this step, we’ll modify the operating system’s syslog application to allow osquery to consume and query the system log. On Ubuntu 16.04, that means modifying the Rsyslog configuration file. And the only modification you need to make is append a few lines of code to the configuration file.
To begin, open the /etc/rsyslog.conf
file:
- sudo nano /etc/rsyslog.conf
We need to add some lines of configuration that tell Rsyslog what pipe to write to, and which syslog parameters to write to that pipe. By default, the pipe is /var/osquery/syslog_pipe
. osquery then populates its syslog
table from information written to that pipe.
Append the following lines to the file:
/etc/rsyslog.conftemplate(
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. To apply the changes, restart the syslog daemon:
- sudo systemctl restart rsyslog
Now let’s create a configuration file that sets up some default options and schedules some queries.
Creating a configuration file makes it easier to run osqueryi
. Instead of having to pass a lot of command line options, osqueryi
can read those options from a configuration file located in /etc/osquery/osquery.conf
. And, of course, the configuration file will also be available to the daemon.
The configuration file also holds queries that need to be executed on schedule. However, most of the queries that you can run on schedule are shipped as what are called packs. Packs are files located in the /usr/share/osquery/packs
directory.
osquery doesn’t come with a configuration file, but there’s a sample configuration file that you may copy over to /etc/osquery
and modify. However, that file does not have all the options you need to run it on a Linux distribution like Ubuntu, so we’ll create our own.
There are three sections to the configuration file:
osqueryi
.The following is a list of the options we’ll be using for our configuration file, what they mean and the values we’ll be setting them to. This list of options is sufficient for running osqueryi
and osqueryd
on Ubuntu 16.04 annd other Linux distributions.
filesystem
.filesystem
./var/log/osquery
.false
we enable logging.true
, each line in the results logs will represent a state change.10
, which is a percentage./var/osquery/osquery.pidfile
.3600
./var/osquery/osquery.db
.false
.2
by default, so we’ll be leaving it at that.true
.false
.false
.true
.true
.hostname
or uuid
. Out of the box, it’s set to hostname
, so we’ll use that value.true
.3600
.You’ve already seen how to view all the command line flags and configuration options avialable to osqueryi
and osqueryd
, but the above options will be enough to run osquery on this server.
Create and open the configuration file using the following command:
- sudo nano /etc/osquery/osquery.conf
The configuration file uses the JSON format. Copy the following content into the file:
{
"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": "hostname",
"enable_syslog": "true",
"audit_allow_sockets": "true",
"schedule_default_interval": "3600"
},
The next part of the configuration file is the scheduling section. Each query is identified by a key or name, which must be unique in the file, followed by the query to run and the interval, in seconds, to run the query. We’ll add a scheduled query that looks at the crontab
table every 300 seconds.
Add these lines to the configuration file:
"schedule": {
"crontab": {
"query": "SELECT * FROM crontab;",
"interval": 300
}
},
You can write any number of queries that you’d like. Just keep the correct format. If you don’t, the file will fail validation. For example, to add a couple more queries, add these lines:
"schedule": {
"crontab": {
"query": "SELECT * FROM crontab;",
"interval": 300
},
"system_profile": {
"query": "SELECT * FROM osquery_schedule;"
},
"system_info": {
"query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
"interval": 3600
}
},
After the scheduled queries, you can add special queries called decorators, which are queries that prepend data to other scheduled queries. The decorator queries shown here will prepend the UUID of the host running osquery and the username of the user to every scheduled query.
Append these lines to the file:
"decorators": {
"load": [
"SELECT uuid AS host_uuid FROM system_info;",
"SELECT user AS username FROM logged_in_users ORDER BY time DESC LIMIT 1;"
]
},
Finally, we can point osquery to a list of packs that contain more specific queries. Every installation of osquery comes with a default set of packs located in the /usr/share/osquery/packs
directory. One of the packs is for macOS, while the rest are for Linux systems. Though you can use the packs from their default location, you may also copy them to the /etc/osquery
directory.
Add these lines to the file to complete the file.
"packs": {
"osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf",
"incident-response": "/usr/share/osquery/packs/incident-response.conf",
"it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
"vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
}
}
Note the closing curly brace at the end, which matches the opening curly brace on the first line of the file. Your completed configuration file should look like this:
{
"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": "hostname",
"enable_syslog": "true",
"audit_allow_sockets": "true",
"schedule_default_interval": "3600"
},
"schedule": {
"crontab": {
"query": "SELECT * FROM crontab;",
"interval": 300
},
"system_profile": {
"query": "SELECT * FROM osquery_schedule;"
},
"system_info": {
"query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
"interval": 3600
}
},
"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",
"incident-response": "/usr/share/osquery/packs/incident-response.conf",
"it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
"vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
}
}
Save and close the file, then validate it using the following command:
- sudo osqueryctl config-check
The output should look like this:
OutputI0104 11:11:46.022858 24501 rocksdb.cpp:187] Opening RocksDB handle: /var/osquery/osquery.db
If there’s an error, the output will indicate the location of the error so you can fix it.
Once you have a valid configuration file, you can move on to configuring the osquery pack required for file integrity monitoring.
Keeping a watchful eye on the integrity of the files on your server is a critical aspect of monitoring its system security. To this end, osquery offers a ready solution.
The packs you added to the configuration in the previous section ship out of the box. In this section, we’ll add one more pack to the list, which will contain the query and directives that will be used for file integrity monitoring. For this exercise, we’ll call the file fim.conf
.
Create this file and open it in your editor:
- sudo nano /usr/share/osquery/packs/fim.conf
We’ll create a pack that will monitor for file events in the /home
, /etc
, and /tmp
directories every 300 seconds. The complete setup for the pack file is shown in the following file listing. Copy it into the file.
/usr/share/osquery/packs/fim.conf{
"queries": {
"file_events": {
"query": "select * from file_events;",
"removed": false,
"interval": 300
}
},
"file_paths": {
"homes": [
"/root/.ssh/%%",
"/home/%/.ssh/%%"
],
"etc": [
"/etc/%%"
],
"home": [
"/home/%%"
],
"tmp": [
"/tmp/%%"
]
}
}
Save and close the file.
To make the new file and its rules available to osquery, reference it in the pack list at the end of /etc/osquery/osquery.conf
. Open the file for editing:
- sudo nano /etc/osquery/osquery.conf
Then modify the packs section to include the new file:
/etc/osquery/osquery.conf
...
"packs": {
"fim": "/usr/share/osquery/packs/fim.conf",
"osquery-monitoring": "/usr/share/osquery/packs/osquery-monitoring.conf",
"incident-response": "/usr/share/osquery/packs/incident-response.conf",
"it-compliance": "/usr/share/osquery/packs/it-compliance.conf",
"vuln-management": "/usr/share/osquery/packs/vuln-management.conf"
}
Save and close the file. And just to be sure that you didn’t make a mistake in the file, validate it again:
- sudo osqueryctl config-check
Now let’s start using osqueryi
to query the system.
There are many places where osquery comes in handy. In this section, you’ll perform various security checks on your system using osqueryi
, the interactive shell. Keep in mind that at this point, we’ve still not started the osquery daemon. And that’s the beauty of osquery - you can run queries using osqueryi
even if the daemon is not active, while still using the configuration file we built to configure the environment.
To launch osquery
with a configuration file, type:
- sudo osqueryi --config_path /etc/osquery/osquery.conf --verbose
Note: Passing osqueryi
and osqueryd
the verbose option is a good practice as it lets you see any errors or warnings that might indicate issues with osquery. And ordinarily, osqueryi
can be run without root privileges, but if you’re calling it while specifying the daemon’s configuration file, you must run it as root.
Let’s start with the basic security checks and work our way up from there. For example, who else other than you is logged into the system now? Find out with this query:
- select * from logged_in_users ;
The output should look like this:
Output+-----------+----------+-------+------------------+------------+------+
| type | user | tty | host | time | pid |
+-----------+----------+-------+------------------+------------+------+
| boot_time | reboot | ~ | 4.4.0-57-generic | 1483580419 | 0 |
| runlevel | runlevel | ~ | 4.4.0-57-generic | 1483580426 | 53 |
| login | LOGIN | ttyS0 | | 1483580429 | 1546 |
| login | LOGIN | tty1 | | 1483580429 | 1549 |
| user | root | pts/0 | 11.11.11.11 | 1483580584 | 1752 |
| user | sammy | pts/1 | 11.11.11.11 | 1483580770 | 4057 |
+-----------+----------+-------+------------------+------------+------+
In this output, there are two real user accounts logged into the machine, and they’re both from the same IP address. That IP address should be a known IP address. If it’s not, you should investigate where that login came from.
The previous query tells you who’s logged in now, but what about previous logins? You can find out by querying the last table, like this:
- select * from last ;
The output indicated nothing out of the ordinary, so no other person has logged into the machine recently:
Output+----------+-------+------+------+------------+------------------+
| username | tty | pid | type | time | host |
+----------+-------+------+------+------------+------------------+
| reboot | ~ | 0 | 2 | 1483580419 | 4.4.0-57-generic |
| runlevel | ~ | 53 | 1 | 1483580426 | 4.4.0-57-generic |
| | ttyS0 | 1546 | 5 | 1483580429 | |
| LOGIN | ttyS0 | 1546 | 6 | 1483580429 | |
| | tty1 | 1549 | 5 | 1483580429 | |
| LOGIN | tty1 | 1549 | 6 | 1483580429 | |
| root | pts/0 | 1752 | 7 | 1483580584 | 11.11.11.11 |
| sammy | pts/1 | 4057 | 7 | 1483580770 | 11.11.11.11 |
+----------+-------+------+------+------------+------------------+
Is the firewall configured and activated? Is the firewall still running? If in doubt, run this query to find out:
- select * from iptables ;
If there’s no output, then it means the IPTables firewall has not been configured. For an Internet-facing server that’s not a good thing, so you better configure the firewall.
You can run the previous command, modified to filter on particular columns, like this:
- select chain, policy, src_ip, dst_ip from iptables ;
That query should give an output like the following. Look for any unusual source and destination IP addresses that you did not configure:
Output+---------+--------+---------+-----------+
| chain | policy | src_ip | dst_ip |
+---------+--------+---------+-----------+
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 127.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| INPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| FORWARD | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| FORWARD | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| OUTPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
| OUTPUT | ACCEPT | 0.0.0.0 | 0.0.0.0 |
+---------+--------+---------+-----------+
What type of jobs are scheduled in crontab? Did you schedule them? This query will help you find malware that have been scheduled to run at specific intervals:
- select command, path from crontab ;
And the output should take this form. Any command in there that looks suspicious demands further investigation:
Output+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+
| command | path |
+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+
| root cd / && run-parts --report /etc/cron.hourly | /etc/crontab |
| root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily ) | /etc/crontab |
| root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly ) | /etc/crontab |
| root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly ) | /etc/crontab |
| root if [ -x /usr/share/mdadm/checkarray ] && [ $(date +\%d) -le 7 ]; then /usr/share/mdadm/checkarray --cron --all --idle --quiet; fi | /etc/cron.d/mdadm |
| root test -x /etc/cron.daily/popularity-contest && /etc/cron.daily/popularity-contest --crond | /etc/cron.d/popularity-contest |
+----------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+
Are there files on the system that are setuid-enabled? There are quite a few on any Ubuntu 16.04 server, but which ones are they, and are there any that are not supposed to be on the system? Answers to these question will help you detect backdoored binaries. Run this query periodically and compare its results against older results so that you can keep an eye on any additions. That query is:
- select * from suid_bin ;
A partial output from this query might look like this:
Output+-------------------------------+----------+-----------+-------------+
| path | username | groupname | permissions |
+-------------------------------+----------+-----------+-------------+
| /bin/ping6 | root | root | S |
| /bin/su | root | root | S |
| /bin/mount | root | root | S |
| /bin/umount | root | root | S |
| /bin/fusermount | root | root | S |
| /bin/ntfs-3g | root | root | S |
| /bin/ping | root | root | S |
| /sbin/mount.ntfs-3g | root | root | S |
| /sbin/mount.ntfs | root | root | S |
| /sbin/unix_chkpwd | root | shadow | G |
| /sbin/pam_extrausers_chkpwd | root | shadow | G |
| /usr/bin/chage | root | shadow | G |
| /usr/bin/locate | root | mlocate | G |
| /usr/bin/chfn | root | root | S |
| /usr/bin/chsh | root | root | S |
| /usr/bin/newuidmap | root | root | S |
| /usr/bin/write | root | tty | G |
| /usr/bin/mlocate | root | mlocate | G |
| /usr/bin/at | daemon | daemon | SG |
| /usr/bin/sg | root | root | S |
To view a list of loaded kernel modules, run this query:
- select name, used_by, status from kernel_modules where status="Live" ;
This is another query you’ll want to run periodically and compare its output against older results to see if anything’s changed.
Yet another method that will help you find backdoors on the server is to run a query that lists all the listening ports. To do that, execute this query:
- select * from listening_ports ;
On a fresh server with only SSH running on port 22
, the output would look like this:
Output+-------+------+----------+--------+---------+
| pid | port | protocol | family | address |
+-------+------+----------+--------+---------+
| 1686 | 22 | 6 | 2 | 0.0.0.0 |
| 1686 | 22 | 6 | 10 | :: |
| 25356 | 0 | 0 | 0 | |
+-------+------+----------+--------+---------+
On your server, if the output only includes ports you know the server should be listening on, there’s nothing to worry about. But if there were other ports open, you’ll want to investigate what those ports are.
To look at file activity on the server, run this query:
- select target_path, action, uid from file_events ;
The output shows all recent file activity on the server, along with the user id responsible for the activity.
Output+---------------------------+---------+------+
| target_path | action | uid |
+---------------------------+---------+------+
| /home/sammy/..bashrc.swp | CREATED | 1000 |
| /home/sammy/..bashrc.swp | UPDATED | 1000 |
| /home/sammy/..bashrc.swp | UPDATED | 1000 |
| /home/sammy/.bashrc | UPDATED | 1000 |
| /home/sammy/..bashrc.swp | DELETED | 1000 |
| /home/sammy/..bashrc.swp | CREATED | 1000 |
| /home/sammy/..bashrc.swp | UPDATED | 1000 |
| /home/sammy/..bashrc.swp | UPDATED | 1000 |
| /home/sammy/.bashrc | UPDATED | 1000 |
| /home/sammy/.bashrc | UPDATED | 1000 |
| /home/sammy/.bashrc | UPDATED | 1000 |
| /home/sammy/..bashrc.swp | DELETED | |
| /etc/test_file.txt | DELETED | |
| /home/sammy/.bash_history | UPDATED | 1000 |
| /home/sammy/.bash_history | UPDATED | 1000 |
| /etc/secret_file.md | CREATED | 0 |
| /etc/secret_file.md | UPDATED | 0 |
| /etc/secret_file.md | UPDATED | 0 |
+---------------------------+---------+------+
There are many queries like those that you can run on your server to get an idea of possible security issues.
If you’re unsure about a table’s schema, use the following command to find out:
- .schema name-of-table
And you can the list the available tables with:
- .tables
There are many more examples in the packs that ship with osquery, and many are designed to run periodically by osqueryd
. In the next section, you’ll learn how to start the daemon to run those queries.
osqueryd
, the daemon, allows osquery to run queries at set intervals. Those queries include the ones you configured in Step 4, the ones in the packs we specified in that step, and in the FIM pack we configured in Step 5. If you haven’t studied the them yet, now is a good time to take a look at the contents of /usr/share/osquery/packs
.
Results generated by osqueryd
are written to a file called osqueryd.results.log
in the /var/log/osquery
directory. Out of the box, that file does not exist. It only gets created when the daemon is started and starts generating results.
You can start osqueryd
using either systemctl
or osqueryctl
. Both accomplish the same thing, so it doesn’t matter which one you use. osqueryd
will check for the existence of a configuration file when it starts, and alert you if it doesn’t find one. It will remain running without a configuration file, although it won’t do anything useful.
But since you have already set up a configuration file, all you need to do here is start the daemon:
- sudo systemctl start osqueryd
Or you can type:
- sudo osqueryctl start
In a few minutes after starting the daemon, the size of /var/log/osquery/osqueryd.results.log
should increase. You can see that happening by typing and repeating the next command:
- ls -lh /var/log/osquery/osqueryd.results.log
The increasing size of the file indicates that the results of the scheduled queries are being written to disk. Unfortunately, osquery does not have an alerting facility like OSSEC, so you can’t see the results of scheduled queries unless you view the results file. You can do that with the tail
command, which will continuously stream the last 10 lines of that file to your screen:
- sudo tail -f /var/log/osquery/osqueryd.results.log
Press CTRL+C
to stop tailing the log.
Longer-term, you would want to ship the query results log to an external analysis platform that you can work with. Viable open source options include Doorman, Zentral, and ElasticSearch.
osquery is a powerful tool, useful for running one-off and scheduled queries using a familiar SQL syntax. osqueryi
is the osquery component for writing one-off queries, while osqueryd
is for scheduling queries. To make sense of the results of scheduled queries, you need to ship them off to an external log analysis platform. You can find more information about osquery at https://osquery.io/.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
Thank you for the great tutorial… Its very helpful for those who is looking about the security and want to track every move on server… And it become one of my “must have” tool…
Hi,
I want to know How I can get the output of osquery in file after adding in packs or if I run any query from kolide so how I can get his output in file on command prompt.