Query Assets

Zercurity uses Facebook’s Osquery to provide the ability for Users to remotely interrogate Assets using SQL.

You can ask almost any question you like, structured as an SQL query. The results of the query are returned in a table. Take the following example:

SELECT pid, name FROM processes ORDER BY pid DESC LIMIT 5;

The query will return all the current running processes on each asset with their respective process id (pid) and process name. Ordered by their process id and limited to the first 5 results.

+-------+----------------------+
| pid   | name                 |
+-------+----------------------+
| 21629 | ReportCrash          |
| 21597 | syncdefaultsd        |
| 21580 | osqueryi             |
| 21511 | Google Chrome Helper |
| 21430 | Google Chrome Helper |
+-------+----------------------+

You can find more information about Osquery’s table Asset Query Schema 3.3.0 here. There you a complete list of all the table definitions that Osquery supports.

For some examples and inspiration of the type of queries, you can run check out some of the examples we’ve provided down below Osquery examples. Or you can check out the Osquery packs guide here.

Scheduled queries view

Scheduled queries can be run at a given interval. This can be quite useful for example, checking that configuration of an asset hasn’t changed.

_images/osquery_scheduled.png
  • Code Once clicked will show you a preview of the running query.
  • Name The name of the query.
  • Results The total number of results that have been returned.
  • Interval The time in seconds between each run of the query.
  • Active? Shows whether the query is running or not. The query can be disabled and resumed at any time just by clicking this button.
  • Created The date of when the query was created.
  • Updated The date of when the query was last updated. This may be due to the active state being changed or the queries name.
  • Last event The date and time of the last event we saw for this query. This field is updated in real-time.
  • Actions These are changes that can be applied to the query.
    • CLONE Clones they query including its current state. Note that the results of the cloned query are not copied over.
    • DELETE Removes the query. Please note that queries are not deleted immediately. However, the results are. Queries will remain within the system for 30 days and are then deleted thereafter.

Create scheduled query

New scheduled queries can be created using the Create Scheduled Query button. Which will display the following popup dialogue.

Note

That the interval time in seconds is how many seconds the daemon itself has been running before the scheduled query will be executed. If the system is suspended or put to sleep the progression of time “freezes” and resumes when the system comes back online. For example a scheduled query with an interval of 84600, or 24 hours, running on a laptop system could take a few days before the query executes if the system is suspended at night

The scheduled query shown below will return a list of all the running process on each asset every 5 minutes or 300 seconds.

_images/osquery_scheduled_create.png

Live queries view

_images/osquery_live.png
  • Code Once clicked will show you a preview of the running query.
  • Name The name of the query.
  • Results The total number of results that have been returned.
  • Active? Shows whether the query is running or not. The query can be disabled and resumed at any time just by clicking this button.
  • Created The date of when the query was created.
  • Updated The date of when the query was last updated. This may be due to the active state being changed or the queries name.
  • Last event The date and time of the last event we saw for this query. This field is updated in real-time.
  • Actions These are changes that can be applied to the query.
    • CLONE Clones they query including its current state. Note that the results of the cloned query are not copied over.
    • DELETE Removes the query. Please note that queries are not deleted immediately. However, the results are. Queries will remain within the system for 30 days and are then deleted thereafter.

Workbench view

This is quite a useful feature. The Zercurity query workbench will allow you to construct and test queries before running the live against deployed Assets.

_images/osquery_workbench.png

Logs view

Shows you any returned results from your deployed Assets. This view is useful for helping to debug running queries.

_images/osquery_logs.png
  • Severity Log severity level. There are three severity levels; INFO, WARNING, ERROR and FATAL.

    • INFO Info messages are not logged.
    • WARNING Warning messages returned from the asset. Usually due to a miss-configuration or syntax error within the query.
    • ERROR Error messages are for severe errors.
    • FATAL Will result in the shutdown of the Osquery agent. This is a critical failure.
  • Asset The name of the asset the log message originated from.

  • Query The query that generated the error. If there is no query defined in the field then the query was a system query.

  • Message A description of the logged event.

  • Timestamp The date and time that the event occurred.

Query results

Once you’ve clicked on a running query you’ll get the following view to display the results returned by the query.

_images/osquery_results.png

Osquery examples

Below we’ve provided a list of some cool queries of what Osquery can do. You can copy and paste there directly into your Zercurity workbench to try out on your running Assets.

You can find more information about Osquery’s schema Asset Query Schema 3.3.0 here.

Get hashes of running binaries

This query returns a list of the running processes on a given system. It also provides a SHA256 of the running binary.

SELECT h.sha256, p.pid, p.name, p.parent AS ppid, p2.name AS parent,
  u.username, (time.unix_time-(uptime.total_seconds-p.start_time)) AS execution_time,
  p.path, p.state, u.uid_signed AS uid
FROM processes AS p, uptime, time
 INNER JOIN processes AS p2 ON ppid = p2.pid
 INNER JOIN hash AS h ON h.path = p.path
 INNER JOIN users AS u ON u.uid = p.uid
 WHERE h.sha256 <> '' ORDER BY execution_time ASC;

Get open process sockets

This query returns a list of the open remote connections by a process running on the given host.

SELECT
  s.pid,
  p.name,
  local_address,
  remote_address,
  family,
  protocol,
  local_port,
  remote_port
FROM
  process_open_sockets AS s
  JOIN processes AS p ON s.pid = p.pid
WHERE
  remote_port NOT IN (0, 80, 443)
  AND remote_address NOT IN ("127.0.0.1")
  AND family = 2;

Mac OSX Firewall enabled

This query checks whether that for a given Mac OSX host the firewall is enabled. It does this by checking whether the globalstate key in com.apple.alf.plist has the value 1.

SELECT
  *
FROM
  plist
WHERE
  path = '/Library/Preferences/com.apple.alf.plist'
  AND KEY = 'globalstate'
  AND value <> '1';