Title
#general
z

Zhen

06/11/2020, 10:04 PM
hey đź‘‹ on Windows, I get a JOIN query working well in
osqueryi
interactive mode, but the same query won’t work in Osquery config
schedule
, neither did see any error in the
osquery\log
folder, any insights are greatly appreciated?
10:06 PM
Here is the query in question, trying to correlate new connection and new process by PID in
windows_events
table 👉
select * from windows_events LEFT JOIN (select data as data2 from windows_events where eventid=4688) ON printf('0x%x', json_extract(data, '$.EventData.ProcessID'))=json_extract(data2, '$.EventData.NewProcessId') where eventid in (5156,5157)
theopolis

theopolis

06/12/2020, 3:10 PM
Does the osquery service receive any windows events? I recommend simplifying the query to just the window events table and go from there. I doubt the query is the problem, the problem is most likely the daemon’s configuration with respect to windows events logging.
z

Zhen

06/22/2020, 5:40 PM
Thanks @theopolis, this entire query works as expected in osqueryi (ie. receives data in windows_events, also returned expected joined data). Also, simplified statement works fine in the config (ie. a sub-query without JOIN
select data as data2 from windows_events where eventid=4688)
). For reference, I have other queries under
schedule
works fine. I wonder if there is additional way to get verbose diagnose info out of osquery daemon?
osquery\log
didn’t record any error log.
5:43 PM
Below is a simplified osquery config file, has anyone used JOIN in
schedule
successfully?
{
 "options": {
        "host_identifier": "xxxxxx",
        "schedule_splay_percent": 10,
        "logger_plugin": "tls",
        "aws_region": "xxxxx",
        "tls_hostname": "xxxxxx",
        "logger_tls_endpoint": "xxxxxx",
        "enroll_secret_path": "xxxxxxxxx",
        "enroll_tls_endpoint": "xxxxxx",
        "disable_enrollment": "false",
        "logger_path": "C:\\Program Files\\osquery\\log",
        "disable_logging": "false",
        "database_path": "C:\\Program Files\\osquery\\osquery.db",
        "verbose": "true",
        "worker_threads": "2",
        "disable_events": "false",
	"windows_event_channels": "Security,Microsoft-Windows-PowerShell/Operational,Windows PowerShell,Microsoft-Windows-Windows Defender/Operational,System"
  },
  "decorators": {
    "load": [
      "SELECT hardware_serial FROM system_info;",
      "SELECT uuid AS host_uuid FROM system_info;"
    ]
  },
  "schedule":{
     ... ...,

    "new_connection_events": {
      "query": "select * from windows_events LEFT JOIN (select data as data2 from windows_events where eventid=4688) ON printf('0x%x', json_extract(data, '$.EventData.ProcessID'))=json_extract(data2, '$.EventData.NewProcessId') where eventid in (5156,5157) ;",
      "interval": 60
    },

    ... ...
  },
  "packs": {
    "windows-hardening": "C:\\Program Files\\osquery\\packs\\windows-hardening.conf",
    "windows-attacks": "C:\\Program Files\\osquery\\packs\\windows-attacks.conf"
  }    
}