integration with osquery

[shomodj]

It would be really cool to add resource for https://osquery.io/

for example you write a query that should return an expected result

in this case osquery can add a lot of features to goss

[aelsabbahy]

First time hearing about osquery, I'll do some research on it and update you.

[aelsabbahy]

While I'm reading up on osquery, can you provide an example goss.yaml file for how you envision this?

[shomodj]

Will write some example and yaml templates bit later today.

[shomodj]

Let's assume 3 use cases

simple use case

simple SQL query that will return one row and one column.

goss add osquery "select username from users where username = 'root'"

by shelling out to osqueryi this command will look like this:

osqueryi "select username from users where username = 'root'" --json

and result will look like this:

[
  {"username":"root"}
]

goss yaml will then look like this:

---
osquery:
  "select username from users where username = 'root'":
    result:
      - username: root

a bit more complex use case

SQL query that will return multiple rows with one column

goss add osquery "select username from users where uid > 999"

osquery result will look like this:

[
  {"username":"nobody"},
  {"username":"shomodj"}
]

and goss yaml will look like this:

  "select username from users where uid > 999":
    result:
      - username: nobody
      - username: shomodj

note that this query could also have a possible variation where order of results is important

for example by default result order is not import and by appending -o to goss add osquery could result in this yaml:

  "select username from users where uid > 999":
    ordered: true
    result:
      - username: nobody
      - username: shomodj

which should fail if result from osquery is not ordered in the same way as data in goss yaml

advanced use case

SQL query that returns multiple rows with multiple columns

example:

goss add query "select uid,gid, username, shell from users where uid > 999"

which will result in a return from osquery like this:

[
  {"gid":"65534","shell":"\/usr\/sbin\/nologin","uid":"65534","username":"nobody"},
  {"gid":"1000","shell":"\/bin\/bash","uid":"1000","username":"shomodj"}
]

and goss yaml:

  "select uid,gid, username, shell from users where uid > 999":
    result:
      - uid: 65534
        gid: 65534
        username: nobody
        shell: /usr/sbin/nologin
      - uid: 1000
        gid: 1000
        username: shomodj
        shell: /bin/bash

Also I would not fail a test if query returns more columns then specified in the yaml, but would fail a test if query returns more rows than specified in yaml.

Makes sense?

[aelsabbahy]

Makes a lot of sense. I watched a couple of youtube videos on osquery over the weekend.

This seems like it would be a cool addition to goss. That said, can you explain why someone would use goss + osquery vs just osquery? Or is it just a reporting tool and nothing more?

[shomodj]

Well goss by its own is the most simple, fastest and most productive server testing tool IMHO.

And it should stay like that.

if you think about it osquery is abstraction like package resource is to deb/rpm/... in this case it covers more than one resource...

but for most people core goss could be fine, but if someone is missing a resource then by installing osquery, goss gets a ton of features and only one more dependency.

quote from osquery docs "osquery is an operating system instrumentation framework"

I don't think osquery has tests, server mode has reporting and scheduler for queries, but its core idea is to provide a SQL way of inspecting machines information.

If you look it from a osquery's user perspective, goss then adds features to osquery as in providing nagios,nagios_verbose,rspecish,tap compliant test framework support.

[aelsabbahy]

Makes a lot of sense. Yes, osquery support looks like it would be a major enhancement. It also might make it possible to support osx, Windows with goss.

This is a great idea. Thanks!!

[shomodj]

a better complex real example:

select users.uid, username, key from users join authorized_keys on users.uid == authorized_keys.uid

will return all users on the system that have password-less ssh access to the system

this is an real life example that will be harder to do in goss, it's possible but you will need to write multiple tests that are not "logically" connected

by using goss/osquery in this example will result in more understandable test and less code

[aelsabbahy]

Is there a resource I can take a look at for complex query examples, like the one you provided?

I see query packs, but those seem like trivial queries.

[shomodj]

I don't know of any resources for complex queries, some examples are scattered in the docs.

If you add integration I'll write you a load of complex examples :)

[automaticgiant]

what would integration do that command + exit/stderr/stdout + https://osquery.readthedocs.io/en/stable/introduction/using-osqueryi/ would not? maybe provide a less verbose (non-SQL) interface to specific tables when sql is too much?

[estahn]

Additionally, it may be worth to be able to specify a list of JSON selectors that need to pass, e.g.

"select username from users where uid > 999":
    result:
      - username: nobody
      - username: shomodj
   selector:
     - . | length == 2
     - .[0].username == "nobody"

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.