trying to correlate socket events to process events, does anybody have a good column for doing corelation based on unique identifier in the following example query?

select
	socket_events.remote_port,
	socket_events.remote_address,
	process_events.cmdline,
	process_events.pid
from socket_events
join process_events on process_events.pid = socket_events.pid

What problem are you having? I suspect that the issue is not the identifier. but that the event tables are transient.

select * process_events

won’t return the same data twice. Maybe join against the process table instead?

hm, possible, the problem im having is im not sure what uniqe index to use to join them on

pid seems correct to me.

whats the diff between procs and proc_events again? procs is real time? like running a ps -A and proc events is logged proc actions right?

Yeah.

processes

is the current processes.

process_events

is closer to to an event stream. In a CEP system, you’d want windowing functions on the event streams, but I haven’t seen stuff like that for osquery.

so, in my case im using Uptycs, it does that

If you’re churning processes so fast that you’re hitting pid reuse. I suspect you’d do better to subscribe to the events and correlate them serverside/