Title
#general
c

Carl

02/19/2020, 5:04 PM
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
s

seph

02/19/2020, 6:51 PM
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?
c

Carl

02/19/2020, 6:52 PM
hm, possible, the problem im having is im not sure what uniqe index to use to join them on
6:52 PM
pid = repeats, and matching on hostname or host id doesn't solve that issue
s

seph

02/19/2020, 6:52 PM
pid seems correct to me.
c

Carl

02/19/2020, 6:52 PM
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?
6:53 PM
i tried pid but again, due to pid recycling, it seems to not work
6:53 PM
and time doesn't work because a proc could be a live for a long time before it does "socket thing"
6:54 PM
or it might not, who knows
s

seph

02/19/2020, 6:54 PM
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.
c

Carl

02/19/2020, 6:54 PM
so, in my case im using Uptycs, it does that
6:54 PM
but the issue is that within my window, im lacking a uniqe identifier to join across
s

seph

02/19/2020, 6:55 PM
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/
6:55 PM
#uptycs may have suggestions.