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

osquery

Howdy folks, I'm new to osquery and wondering what might be the fastest/easiest way to query for cmdline, path, and parent_path. I'm currently using:

process_events": {
      "query": "SELECT * FROM process_events;",

But this doesn't give me parent_path. Thanks for any help you can provide :slightly_smiling_face:

Replying as I'm interested and haven't seen a great / consistent way to do this with process_events.

You can try adding info from a subquery of process_events, `WHERE pid=p.parent` (p = current process) but I've found it is not super reliable for a bunch of reasons and pretty inconsistent at best

well, you have the pid but not parent cmdline and you can do a hacky and not altogether reliable workaround to get parent_cmdline

example:

```    SELECT
      pe.path,
      pe.cmdline,
      pe.cwd,
      pe.gid,
      pe.egid,
      pe.uid,
      pe.euid,
      pe.pid,
      pe.parent,
      pe.time,
      (select coalesce((SELECT cmdline FROM process_events AS parent_cmdline0 WHERE pid=pe.parent), (SELECT cmdline FROM processes AS parent_cmdline1 WHERE pid=pe.parent))) AS parent_cmdline
    FROM
      process_events AS pe;```

I think you can do it with a join, though I don't know if it would be more performant. Not sure if I'll have the time to work through the sql

```with raw as (select
pid,
parent,
path
from processes)
select
  p.pid,
  p.parent,
  p.path as cpath,
 pp.path as ppath
from raw as p
left join raw as pp ON p.parent = pp.pid
;```
You may not need the temp table there either, but I wasn’t sure if you’d get multiple hits to the process table.

This probably won’t work as well from events, since there may be weird windowing in events. I think it’s probably a bunch weirder. Not really sure.