I am looking to do some correlation (particularly on macOS) between

es_process_events

and

process_events

and

processes

and I am scratching my head a bit about how to get the right matches considering PIDs can be reused: 1. Ideal case would be all 3 tables expose "id" (viewable in the JSON for es_process_events if you look at an

exec

action in Mac Monitor by Red Canary, for instance) which is a UUID that pretty much will never be reused in the course of the life of the universe and join on that. 2. Less ideal would be

pid

and

pidversion

which could be used to make a composite key to join on (with ANDs) -

process_events

lacks this and bsm probably never picks it up.

processes

lacks

pid_version

but has

upid

(which

es_process_events

lacks) which is not a UUID but an incremented 64 bit number 3. Possibly less ideal would be a

LEFT JOIN

starting with

es_process_events

and then limiting my time, so if the event happens and the query happens for it soon enough, grab static data from processes... not a big fan. Joins against socket_events are more problematic because there is just pid, no pid version

UUID

or

upid.

This is not a complaint at all, osquery rocks. And I can already get close enough queries with just joining on pid as long as I caveat my results sufficiently

First, what kind of use case has you joining between

es_process_events

and

process_events

and

processes

? I'm curious about the use case driving that.

I'll try to expand upon that today. Thanks for taking interest

Second, osquery tends to be a pretty light API translation layer. So if the underlying OS has something like a

id

it would be reasonable for osquery to expose it, but maintaining our own

id

sounds error prone, and possible memory intensive. I don't know these APIs very well, so I'm not sure what's exposed. On macOS

socket_events

is coming out of the older BSM system, while

es_process_events

is coming from the newer ES system. So the underlying OS may not have a consistent identifier beyond pid, you'd need to poke around the source code. Just from the name of it, it sounds like

pidversion

is meant to fill this gap, but that doesn't mean it's in the older APIs

Yeah, the

id

uuid in es_process_events does come from the OS. And yeah I am not asking for you to make things that don’t exist. Will expand when I get to wok

I'm not sure this is a sql question exactly, it feels more like the overall system design

No no right right. I think I meant more your question about what I was trying to do made me realize I had to un-newb a bit

Yah -- "what are you trying to do" is a system design level question 🙂