https://github.com/osquery/osquery logo
Join Slack
Powered by
hey :wave: on Windows, I get a JOIN query working ...
# general
z
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?
Here is the query in question, trying to correlate new connection and new process by PID in 
windows_events
table 👉
Copy code
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)
t
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
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.
Below is a simplified osquery config file, has anyone used JOIN in 
schedule
successfully?
Copy code
{
 "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"
  }    
}
Open in Slack
PreviousNext
Powered by