Query Assets ============ Zercurity uses Facebook's `Osquery`_ to provide the ability for :doc:`/users` to remotely interrogate :doc:`/assets` using SQL. .. _Osquery: https://osquery.io/ 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: .. code-block:: sql 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. .. code-block:: sql +-------+----------------------+ | 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 :doc:`/osquery_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 :ref:`osquery-examples`. Or you can check out the Osquery packs `guide`_ here. .. _guide: https://github.com/facebook/osquery/tree/master/packs .. _rtd: https://osquery.readthedocs.io/en/stable/ 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. .. image:: _static/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. .. image:: _static/osquery_scheduled_create.png Live queries view ----------------- .. image:: _static/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 :doc:`/assets`. .. image:: _static/osquery_workbench.png Logs view --------- Shows you any returned results from your deployed :doc:`/assets`. This view is useful for helping to debug running queries. .. image:: _static/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. .. image:: _static/osquery_results.png .. _osquery-examples: 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 :doc:`/assets`. You can find more information about Osquery's schema :doc:`/osquery_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. .. code-block:: sql 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. .. code-block:: sql 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``. .. code-block:: sql SELECT * FROM plist WHERE path = '/Library/Preferences/com.apple.alf.plist' AND KEY = 'globalstate' AND value <> '1';