Adding context to your Sensu checks with Osquery

Intro to Osquery

Osquery is fantastic bit of magic that gives you a SQL query interface for collecting system information from disparate subsystems across multiple supported platforms. In a nutshell, Osquery represents the system as a series of database tables, each representing different subsystems. Osquery’s approach of using SQL SELECT statements to investigate the state of a system makes for an excellent way to quickly add a significant amount of context into a Sensu check — possibly replacing the need to call complicated glue scripting to extract information from across different subsystems. Keep reading to learn how to integrate the power of OSquery into your Sensu checks.

Interactive queries

Osquery comes with a daemon process called osqueryd and an interactive cmdline utility called osqueryi. I’ll focus on the osqueryi CLI tool in this blog post, as it’s currently the best way to integrate Osquery results into your Sensu checks. Osqueryi alone doesn’t support everything Osquery can do with the osqueryd service running and configured to capture system events into queriable tables, but it’s still very powerful on its own.

So let’s take a look at what that osqueryi tool can actually do with some really quick examples you can run, without the osqueryd daemon running. If you want to query the running process tree, you can access the process table by giving osqueryi this SQL statement:

osqueryi "SELECT pid,name,uid,total_size,user_time,system_time FROM processes ORDER BY total_size DESC LIMIT 5;"

The result is a very familiar ASCII representation for the SQL records:

+------+--------------+-----+------------+-----------+------------+
| pid  | name         | uid | total_size | user_time | system_time 
+------+--------------+-----+------------+-----------+------------+
| 8077 | sensu-server | 994 | 782808000  | 6648      | 1846 
| 8114 | sensu-client | 994 | 769488000  | 3975      | 856 
| 536  | polkitd      | 999 | 638120000  | 110       | 45 
| 825  | beam         | 995 | 615184000  | 202845    | 166816 
| 821  | tuned        | 0   | 562392000  | 19813     | 2144 
+------+--------------+-----+------------+-----------+------------+

Want that processes query to output as JSON? Just add the --json argument:

osqueryi --json "SELECT pid,name, uid,  total_size, user_time, system_time FROM processes ORDER BY total_size DESC LIMIT 5;"

Here’s the output:

[ 
  { 
    "name": "sensu-server","pid": "8077", 
    "system_time": "1853","total_size": "782808000", 
    "uid": "994","user_time": "6675" 
  }, 
  { 
    "name": "sensu-client","pid": "8114", 
    "system_time": "860","total_size": "769488000", 
    "uid": "994","user_time": "3992" 
  }, 
  { 
    "name": "polkitd","pid": "536", 
    "system_time": "45","total_size": "638120000", 
    "uid": "999","user_time": "110" 
  }, 
  { 
    "name": "beam","pid": "825", 
    "system_time": "166862","total_size": "616208000", 
    "uid": "995","user_time": "202903" 
  }, 
  { 
    "name": "tuned","pid": "821", 
    "system_time": "2144","total_size": "562392000", 
    "uid": "0","user_time": "19820" 
  } 
]

The real power comes when using SQL to ask more complicated questions by using joins on the tables. Instead of reaching for a series of different commandline tools, glued together using an interpreter language, you can write compact, high-level SQL expressions like this:

sudo osqueryi --json "SELECT DISTINCT process.name, listening.port, listening.address, listening,path, process.pid, process.cmdline, process.cwd, process.root FROM processes AS process JOIN listening_ports AS listening ON process.pid = listening.pid;"

That’s a SQL expression for listing process information associated with known bound listening ports. On Linux, sudo is required, as access to some of the information — like the listening port process id — is restricted. Without Osquery, I would most likely build my own shell script glue to parse the output of multiple tools like netstat, ps, pwdx, and lsof. Osquery’s SQL syntax is a huge time saver and cuts down on the amount of brittle glue code I have to maintain.

Integrating with Sensu

What does this have to do with Sensu? These queries are great mechanisms to add heaps of context into your Sensu checks via check hooks. When used as a check hook, Osquery results give a useful system snapshot to pass along as part of a check result.

Here’s a toy Osquery check hook for the check-file-exists.rb command provided from sensu-plugins-filesystem-checks:

{ 
  "checks": { 
  "osquery_test_check": { 
    "command": "check-file-exists.rb", 
    "subscribers": ["osquery"], 
    "interval": 30, 
      "hooks": { 
        "non-zero": { 
          "command": "osqueryi --json \"SELECT * from mounts where path=':::db.disk.mount|/:::';\" ; osqueryi --json \"select * from listening_ports;\" ", 
          "timeout": 10 
        } 
      } 
    } 
  } 
}

To have check-file-exists.rb return a non-zero status and cause the hook command to run, create the file /tmp/WARNING. Remove that file to return back to zero return status.

That hook command may look pretty complicated with two different SQL statements run back to back. How is this easier than running a series of shell commands chained together? Admittedly, it’s not that much easier. You do get the small benefit of JSON-like output but you’re still chaining shell commands together. When I need to run multiple queries, I find it best to define a dedicated query pack, which would make it as easy as calling osqueryi --pack packname.

Query packs

Out-of-the box Osquery comes with some interesting pre-defined query packs, including: hardware-monitoring, incident-response, and it-compliance packs (just to name a few). You can schedule these packs to run at certain times using osqueryd and have the results shipped to a supported log aggregator. You can also run them interactively with osqueryi inside your Sensu check hooks, which may be more advantageous than osqueryd scheduling in some situations, as you can capture extensive system state context in a just-in-time manner when a check fails. Though, I’d love to talk to someone about building an osqueryd integration to send output into the Sensu event pipeline for queries scheduled and executed by osqueryd. Hey Osquery peeps! If this sounds interesting to you, find me on the Sensu Community Slack or poke me on Twitter so we can have a conversation.

Let’s take the it-compliance pack as an example — on a Linux system Osquery installs it at /user/share/osquery/packs/it-compliance.conf. This pack defines 32 different queries, for all sorts of things:

jq '.[] | keys' /usr/share/osquery/packs/it-compliance.conf

Some of these queries are platform specific — using platform-specific Osquery tables — but that’s fine. Osquery will run the query pack and quickly return a nil result for queries that don’t apply to the current system. That way you can groom a single query pack definition for a particular purpose, and run that query pack on all your clients.

Note: before using a query pack, you’ll need to enable it in the Osquery config file by listing it in the packs hash; by default the config file is located at /etc/osquery/osquery.conf.

Let’s run that it-compliance pack interactively and save the output to a file (it’s a lot of output):

time osqueryi --pack it-compliance > /tmp/it-compliance.out

real 0m0.816s
user 0m0.784s
sys 0m0.030s

wc -l /tmp/it-compliance.out
575 /tmp/it-compliance.out

That query pack took just under a second to run and had 575 lines of output on my Linux system. In this case, most of the output is from the rpm_packages query in the pack, providing a listing of the installed rpm packages.

Sensu check hooks revisited

We can update that previous check-hook and have it run the it-compliance query pack:

{
  "checks": {
    "osquery_test_pack": {
      "command": "check-file-exists.rb",
      "subscribers": ["osquery"],
      "interval": 30,
      "hooks": {
        "non-zero": {
          "command": "osqueryi --json --pack it-compliance",
          "timeout": 10
        }
      }
    }
  }
}

The use of the query pack makes the check hook command much easier to write. And more importantly, you can use a single query pack for all your Osquery supported platforms including Windows, OS X, and Linux — no more having to complicate the logic of your bespoke check hook scripts for slight difference between the BSD or GNU commandline tools for OS X and Linux clients.

Using Osquery as a Sensu check

But wait there’s more! Why not use osqueryi as a check command? Osquery is a very powerful way to ask questions about the system — to use osqueryi as a check command, all we need to do is process the answers and make a judgement concerning system status. Here’s a toy example of what I’m thinking, using osqueryi piped into jq to process the JSON query output to test for success or failure:

{
  "checks": {
  "osquery_flexible_check": {
    "command": "osqueryi --json:::osquery_check.osqueryi|’’::: | jq -e :::osquery_check.jq|’length > 0’::: ",
    "subscribers": ["osquery"]
    "interval": 30,
    "hooks": {
      "non-zero": {
        "command": "osqueryi --json --pack it-compliance",
        "timeout": 10
      }
    }
  }
}

This is a very expressive and flexible check construct, relying on check token substitution to make it possible to define the specific osqueryi and jq arguments as client metadata attributes. The fallback arguments result in a zero-length JSON array that jq then processes to a false result and a non-zero return status. Here’s an example snippet from the sensu-client log generated when the check is run with the fallback arguments:

{
  "timestamp":"2018-07-11T21:27:50.998592+0000",
  "level":"info",
  "message":"publishing check result",
  "payload":{
    "client":"new-test-client",
    "check":{
      "command":"osqueryi --json :::osquery.osqueryi|’’::: | jq -e :::osquery.jq|‘length > 0’::: ",
      "name":"osquery_flexible_check",
      "issued":1531344470,
      "subscribers":["osquery"],
      "interval":30,
      "executed":1531344470,
      "duration":0.04,
      "output":"false\n",
      "status":1
    }
  }
}

I hope this post gave you a useful introduction into what you can do with Sensu + Osquery, and got you thinking a bit about how you can integrate Osquery into your own Sensu checks. If you’re interested in working with me to develop this Osquery check command idea further — into a fully fleshed out Sensu check plugin gem, using ruby logic to replace the call out to jq to process the query output — give me a ping on the Sensu Community Slack.