Osquery

Osquery provides easy access to operating system logs via SQL queries as it exposes operating system data in a relational data model.

According to Osquery’s documentation, osquery "does not implement log forwarding internally." In fact, "the act of forwarding logs and analyzing logs is mostly left as an exercise for the reader." NXLog Agent can be integrated with osquery to forward logs when deployed on Windows, MacOS, Linux, and FreeBSD.

Using Osquery

Osquery utilizes SQL queries to retrieve information.

Example 1. Using Osquery

The following simple SELECT statement lists process information:

SELECT pid, name, path FROM processes;

Table 1. Sample query result on Linux
pid	name	path
1	bash	/usr/bin/bash
162	nxlog	/opt/nxlog/bin/nxlog
178	osquery	/usr/bin/osqueryd
22	bash	/usr/bin/bash
37	vim	/usr/bin/vim

Table 2. Sample query result on Windows
pid	name	path
0	[System Process]
4	System
244	smss.exe	C:\Windows\System32\smss.exe
324	csrss.exe	C:\Windows\System32\csrss.exe
404	csrss.exe	C:\Windows\System32\csrss.exe
412	wininit.exe	C:\Windows\System32\wininit.exe
596	svchost.exe	C:\Windows\System32\svchost.exe

For more information about osquery commands, see the osqueryi (shell) and SQL Introduction sections on the osquery website.

Configuring Osquery

The osqueryd daemon allows scheduling queries and provides two types of logging:

differential — logs changes in the system between the previous and the current query executions.
snapshot — logs the data set obtained in a certain point in time.

For more information on installing osquery, see the Getting Started section on the osquery website.

Osquery can be configured via the osquery.conf file using a JSON format. This file should be located under the following paths:

Linux: /etc/osquery/
Windows: C:\Program Files\osquery\
FreeBSD: /usr/local/etc/
MacOS: /private/var/osquery/

Example 2. Configuring Osquery for the Differential Mode

The following configuration is an example of a differential logging configuration. The schedule object contains the nested processes object, which contains two fields:

query — This key specifies the SQL statement. In this case, it selects all entries from the processes table.
interval — This key contains the number of seconds after which the statement is executed again. In this example, the query is executed every 10 seconds.

osquery.conf

{
  "schedule": {
    "processes": {
      "query": "SELECT pid, name, path FROM processes;",
      "interval": 10
    }
  }
}

Example 3. Configuring Osquery for the Snapshot Mode

The following configuration is an example of the snapshot logging configuration.

The processes object contains the additional snapshot key, which is a boolean flag to enable the snapshot logging mode.

osquery.conf

{
  "schedule": {
    "processes": {
      "query": "SELECT pid, name, path FROM processes;",
      "interval": 10,
      "snapshot": true
    }
  }
}

For more information, see the Configuration section on the osquery website.

Log samples

Osquery creates status logs of its own execution for both differential and snapshot logging.

Execution logs are stored in the following files:

osqueryd.INFO,
osqueryd.WARNING,
osqueryd.ERROR.

By default, all osquery log files are available under the following paths:

On Unix-like systems: /var/log/osquery/
On Windows: C:\Program Files\osquery\log\

Example 4. Execution logs

Below are the samples of the execution logs from Ubuntu and Windows.

osqueryd.INFO on Ubuntu

Log file created at: 2019/11/25 10:07:54
Running on machine: ubuntu
Log line format: [IWEF]mmdd hh:mm:ss.uuuuuu threadid file:line] msg
I1125 10:07:54.233732 28060 events.cpp:863] Event publisher not enabled: auditeventpublisher: Publisher disabled via configuration
I1125 10:07:54.233835 28060 events.cpp:863] Event publisher not enabled: syslog: Publisher disabled via configuration

osqueryd.INFO on Windows

Log file created at: 2019/11/28 10:57:00
Running on machine: WIN-SFULD4GOF4H
Log line format: [IWEF]mmdd hh:mm:ss.uuuuuu threadid file:line] msg
I1128 10:57:00.979398  3908 scheduler.cpp:105] Executing scheduled query processes: SELECT pid, name, path FROM processes;
E1128 10:57:01.009029  3908 processes.cpp:312] Failed to lookup path information for process 4
E1128 10:57:01.024600  3908 processes.cpp:332] Failed to get cwd for 4 with 31
I1128 10:58:01.649113  3908 scheduler.cpp:105] Executing scheduled query processes: SELECT pid, name, path FROM processes;
E1128 10:58:01.681404  3908 processes.cpp:312] Failed to lookup path information for process 4
E1128 10:58:01.712568  3908 processes.cpp:332] Failed to get cwd for 4 with 31

The osqueryd.results.log file stores differential log entries.

Example 5. Differential logs

Below are the samples of the differential logs from Ubuntu and Windows.

osqueryd.results.log on Ubuntu

{"name":"users","hostIdentifier":"ubuntu","calendarTime":"Mon Nov 25 09:11:40 2019 UTC","unixTime":1574673100,"epoch":0,"counter":0,"logNumericsAsNumbers":false,"columns":{"directory":"/","uid":"111","username":"kernoops"},"action":"removed"}
{"name":"users","hostIdentifier":"ubuntu","calendarTime":"Mon Nov 25 09:11:40 2019 UTC","unixTime":1574673100,"epoch":0,"counter":0,"logNumericsAsNumbers":false,"columns":{"directory":"/bin","uid":"2","username":"bin"},"action":"removed"}

osqueryd.results.log on Windows

{"name":"processes","hostIdentifier":"WIN-SFULD4GOF4H","calendarTime":"Fri Nov 29 18:18:00 2019 UTC","unixTime":1575051480,"epoch":0,"counter":23,"logNumericsAsNumbers":false,"columns":{"name":"conhost.exe","path":"C:\\Windows\\System32\\conhost.exe","pid":"2936"},"action":"removed"}
{"name":"processes","hostIdentifier":"WIN-SFULD4GOF4H","calendarTime":"Fri Nov 29 18:18:00 2019 UTC","unixTime":1575051480,"epoch":0,"counter":23,"logNumericsAsNumbers":false,"columns":{"name":"dllhost.exe","path":"C:\\Windows\\System32\\dllhost.exe","pid":"3784"},"action":"removed"}

The osqueryd.snapshots.log file stores snapshot logs.

Example 6. Snapshot logs

Below are the samples of the snapshot logs from Ubuntu and Windows.

osqueryd.snapshots.log on Ubuntu

{"snapshot":[{"name":"gsd-rfkill","path":"/usr/lib/gnome-settings-daemon/gsd-rfkill","pid":"944"},{"name":"gsd-screensaver","path":"/usr/lib/gnome-settings-daemon/gsd-screensaver-proxy","pid":"947"},{"name":"gsd-sharing","path":"/usr/lib/gnome-settings-daemon/gsd-sharing","pid":"949"},{"name":"gsd-smartcard","path":"/usr/lib/gnome-settings-daemon/gsd-smartcard","pid":"955"},{"name":"gsd-sound","path":"/usr/lib/gnome-settings-daemon/gsd-sound","pid":"962"},{"name":"gsd-wacom","path":"/usr/lib/gnome-settings-daemon/gsd-wacom","pid":"965"},{"name":"kstrp","path":"","pid":"98"}],"action":"snapshot","name":"users","hostIdentifier":"ubuntu","calendarTime":"Mon Nov 25 09:14:25 2019 UTC","unixTime":1574673265,"epoch":0,"counter":0,"logNumericsAsNumbers":false}

osqueryd.snapshots.log on Windows

{"snapshot":[{"name":"[System Process]","path":"","pid":"0"},{"name":"System","path":"","pid":"4"},{"name":"smss.exe","path":"C:\\Windows\\System32\\smss.exe","pid":"244"},{"name":"csrss.exe","path":"C:\\Windows\\System32\\csrss.exe","pid":"328"},{"name":"wininit.exe","path":"C:\\Windows\\System32\\wininit.exe","pid":"408"},{"name":"winlogon.exe","path":"C:\\Windows\\System32\\winlogon.exe","pid":"452"},{"name":"services.exe","path":"C:\\Windows\\System32\\services.exe","pid":"512"},{"name":"RuntimeBroker.exe","path":"C:\\Windows\\System32\\RuntimeBroker.exe","pid":"2664"},{"name":"sihost.exe","path":"C:\\Windows\\System32\\sihost.exe","pid":"2700"},{"name":"svchost.exe","path":"C:\\Windows\\System32\\svchost.exe","pid":"2708"}],"action":"snapshot","name":"processes","hostIdentifier":"WIN-SFULD4GOF4H","calendarTime":"Fri Nov 29 18:13:04 2019 UTC","unixTime":1575051184,"epoch":0,"counter":0,"logNumericsAsNumbers":false}

For more information about the logging system of osquery, see the Logging section on the osquery website.

Configuring NXLog Agent

This section provides examples of how to configure NXLog Agent to integrate with osquery.

Example 7. Configuring NXLog Agent for Unix-like Systems

The following configuration uses the im_file module to read the osquery log entries and process them with the xm_json module.

nxlog.conf

<Extension _json>
    Module      xm_json
</Extension>

<Input osquery_diff>
    Module      im_file
    File        "/var/log/osquery/osqueryd.results.log"
    Exec        parse_json();
</Input>

<Input osquery_snap>
    Module      im_file
    File        "/var/log/osquery/osqueryd.snapshots.log"
    Exec        parse_json();
</Input>

Example 8. Configuring NXLog Agent for Windows

The following configuration uses the im_file module to read the osquery log entries and process them with the xm_json module.

nxlog.conf

<Extension _json>
    Module      xm_json
</Extension>

<Input osquery_diff>
    Module      im_file
    File        "C:\\Program Files\\osquery\\log\\osqueryd.results.log"
    Exec        parse_json();
</Input>

<Input osquery_snap>
    Module      im_file
    File        "C:\\Program Files\\osquery\\log\\osqueryd.snapshots.log"
    Exec        parse_json();
</Input>

Example 9. Forwarding Osquery logs

Using an appropriate output module, NXLog Agent can be configured to forward osquery logs to a remote system. As an example, the om_tcp module is used.

nxlog.conf

<Output snap_out>
    Module      om_tcp
    Host        192.168.1.1
    Port        1515
</Output>

Disclaimer

While we endeavor to keep the information in our guides up to date and correct, NXLog makes no representations or warranties of any kind, express or implied about the completeness, accuracy, reliability, suitability, or availability of the content represented here. We update our screenshots and instructions on a best-effort basis.

Last revision: 11 January 2020