hey wave on Windows I get a JOIN query working well in `osqu

Question

hey wave 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

Zhen · Answer

Here is the query in question trying to correlate new connection and new process by PID in `windows events` table point right ```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 · Answer

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

Zhen · Answer

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

Zhen · Answer

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 ```