Osquery uses basic SQL commands to leverage a relational data-model to describe a device.

osquery

Hi, I'm currently using " *listening_ports* " table and " *process_open_sockets* " table with some joins with processes and related tables. The issue is that there's a hit and miss scenario where a connection might be open/closed between the query intervals and it may not represent actual data. To improve this, I'm thinking of using " *socket_events* " table instead with appropriate joins with process_events table etc. to replace both the queries/tables as stated above.  Will it be sufficient or would I still be missing some data here ? Any suggestions are welcome. Thanks :slightly_smiling_face:

Joining two '_events' tables might be problematic - for example a socket event occurred but for a process that already started (so it might not exist in process_events table). I think you should join socket_event with processes table. However, you might still miss some data if the process started and terminated in between the scheduled interval.
Also note, the socket_events table is currently a linux only table, so if you use any other OS, this is something you should consider.

ok thanks, will try all these scenarios. We do have windows host as well, will check how can I improve data for win platform.

<@U8S3QAKD4> I think combining process_events, processes and socket_event should cover the scenario

do you see still see any data loss with above combination?

Also, if you are capturing the events and storing it in some database, then I think events gathered from process_events and socket_events will save you from data loss. Key part is capturing the events and storing some place safe (i.e database etc)

<@UADSPRGKV> If the events are stored and you manage them, it is definitely possible to work with both events table, but if I understand correctly, it means you will have to implement the join yourself. As I see it, joining two events table in one query and scheduling it in osqueryd directly will not be sufficient. If I understand it wrong, please explain where is my misunderstanding.