I recently stumbled across osquery which allows you to query your Linux, and OS X, servers for various bits of information. It's very similar in concept to WQL for those in the Windows world.

Here's my quick getting started guide for CentOS 6.X...

First download and install the latest rpm for your distro. You might want to check osquery downloads for the latest release.

wget https://osquery-packages.s3.amazonaws.com/centos6/osquery-1.8.2.rpm
sudo rpm -ivh osquery-1.8.2.rpm

You'll now have the following three executables in your path

  • osqueryctl - bash script to manage the daemon.
  • osqueryd - the daemon.
  • osqueryi - command-line client to interactively run osquery queries, view tales (namespaces) and so on.

Take a look at the example config...

cat /usr/share/osquery/osquery.example.conf

The daemon won't start without a config file so be sure to create one first. This config file does a few thigns but will also periodcially run some queries and log them to a file. This is useful for sticking data into ELK or splunk.

cat << EOF > /etc/osquery/osquery.conf
{
"options": {
"config_plugin": "filesystem",
"logger_plugin": "filesystem",
"logger_path": "/var/log/osquery",
"pidfile": "/var/osquery/osquery.pidfile",
"events_expiry": "3600",
"database_path": "/var/osquery/osquery.db",
"verbose": "true",
"worker_threads": "2",
"enable_monitor": "true"
},
// Define a schedule of queries:
"schedule": {
// This is a simple example query that outputs basic system information.
"system_info": {
// The exact query to run.
"query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
// The interval in seconds to run this query, not an exact interval.
"interval": 3600
}
},
// Decorators are normal queries that append data to every query.
"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"

}
}
EOF

Try to start the daemon...

osqueryctl start

If you encounter this issue

/usr/bin/osqueryctl: line 52: [: missing `]'

vi /usr/bin/osqueryctl

Change line 52 from this....

if [ ! -e "$INIT_SCRIPT_PATH" && ! -f "$SERVICE_SCRIPT_PATH" ]; then

to this

if [ ! -e "$INIT_SCRIPT_PATH" ] && [ ! -f "$SERVICE_SCRIPT_PATH" ]; then

You should no be able to start without complaint...

osqueryctl start

Some logfiles should appear here...

ls -lh /var/log/osquery/

Use the osquery client to explore (this is akin to the mysql client). Data is presented just like a traditional sql database...

osqueryi
osquery>.help
osquery> select * from logged_in_users;
osquery> select * from time;
osquery> select * from os_version;
osquery> select * from rpm_packages;
osquery> select * from shell_history;

We can view the "schema" of certain tables like so...

osquery> .schema processes

CREATE TABLE processes(`pid` BIGINT PRIMARY KEY, `name` TEXT, `path` TEXT, `cmdline` TEXT, `state` TEXT, `cwd` TEXT, `root` TEXT, `uid` BIGINT, `gid` BIGINT, `euid` BIGINT, `egid` BIGINT, `suid` BIGINT, `sgid` BIGINT, `on_disk` INTEGER, `wired_size` BIGINT, `resident_size` BIGINT, `phys_footprint` BIGINT, `user_time` BIGINT, `system_time` BIGINT, `start_time` BIGINT, `parent` BIGINT, `pgroup` BIGINT, `nice` INTEGER) WITHOUT ROWID;

We can use familar sql operators...

SELECT * FROM file WHERE path LIKE '/etc/%';

+-----------------------------------+------------------------+------------------------------+--------+-----+-----+------+--------+--------+------------+------------+------------+------------+-------+------------+-----------+
| path | directory | filename | inode | uid | gid | mode | device | size | block_size | atime | mtime | ctime | btime | hard_links | type |
+-----------------------------------+------------------------+------------------------------+--------+-----+-----+------+--------+--------+------------+------------+------------+------------+-------+------------+-----------+
| /etc/ConsoleKit/ | /etc/ConsoleKit | . | 398999 | 0 | 0 | 0755 | 0 | 4096 | 4096 | 1467708722 | 1414762792 | 1414762792 | 0 | 5 | directory |
| /etc/DIR_COLORS | /etc | DIR_COLORS | 398851 | 0 | 0 | 0644 | 0 | 4439 | 4096 | 1475149457 | 1405522956 | 1414762782 | 0 | 1 | regular |
| /etc/DIR_COLORS.256color | /etc | DIR_COLORS.256color | 398852 | 0 | 0 | 0644 | 0 | 5139 | 4096 | 1405522956 | 1405522956 | 1414762782 | 0 | 1 | regular |
| /etc/DIR_COLORS.lightbgcolor | /etc | DIR_COLORS.lightbgcolor | 398853 | 0 | 0 | 0644 | 0 | 4113 | 4096 | 1405522956 | 1405522956 | 1414762782 | 0 | 1 | regular |
| /etc/NetworkManager/ | /etc/NetworkManager | . | 400911 | 0 | 0 | 0755 | 0 | 4096 | 4096 | 1467708722 | 1422299768 | 1436772557 | 0 | 5 | directory |

We can even join onto other tables. This query shows Linux users and their associated groups...

osquery> SELECT u.username,
g.groupname
FROM users u
INNER JOIN user_groups ug
ON u.uid = ug.uid
INNER JOIN groups g
ON g.gid = ug.gid;

+---------------+---------------+
| username | groupname |
+---------------+---------------+
| root | root |
| bin | bin |
| bin | daemon |
| bin | sys |
| daemon | daemon |
| daemon | bin |
| daemon | adm |
| daemon | lp |

This query shows some details about processes listening on ports...

osquery> SELECT p.pid, p.name, p.state, u.username, lp.*
FROM processes p
INNER JOIN listening_ports lp
ON lp.pid = p.pid
INNER JOIN users u
ON u.uid = p.uid;

+-------+---------------+-------+-----------+-------+-------+----------+--------+--------------------------+
| pid | name | state | username | pid | port | protocol | family | address |
+-------+---------------+-------+-----------+-------+-------+----------+--------+--------------------------+
| 1318 | rpcbind | S | rpc | 1318 | 111 | 6 | 2 | 0.0.0.0 |
| 1318 | rpcbind | S | rpc | 1318 | 111 | 6 | 10 | :: |
| 1318 | rpcbind | S | rpc | 1318 | 111 | 17 | 2 | 0.0.0.0 |
| 1318 | rpcbind | S | rpc | 1318 | 645 | 17 | 2 | 0.0.0.0 |
| 1318 | rpcbind | S | rpc | 1318 | 111 | 17 | 10 | :: |
| 1318 | rpcbind | S | rpc | 1318 | 645 | 17 | 10 | :: |

Show the files, and who owns them, installed by an rpm package...

osquery> SELECT p.name, p.version, pf.path, pf.username, pf.groupname
FROM rpm_packages p
INNER JOIN rpm_package_files pf
ON p.name = pf.package
WHERE p.name = 'MariaDB-server';

+----------------+---------+---------------------------------------------------------+----------+-----------+
| name | version | path | username | groupname |
+----------------+---------+---------------------------------------------------------+----------+-----------
+ | MariaDB-server | 10.0.20 | /etc/init.d/mysql | root | root | | MariaDB-server | 10.0.20 | /etc/logrotate.d/mysql | root | root | | MariaDB-server | 10.0.20 | /etc/my.cnf.d | root | root | | MariaDB-server | 10.0.20 | /etc/my.cnf.d/server.cnf | root | root | | MariaDB-server | 10.0.20 | /etc/my.cnf.d/tokudb.cnf | root | root | | MariaDB-server | 10.0.20 | /usr/bin/aria\_chk | root | root |

```
osquery> .exit
```

You can see the data collected by the deamon on schedule here...

cat /var/log/osquery/osqueryd.results.log