How to edit my search to correlate values with a single index and invert results?

[grimlock]

I have a list of pids, parent pids and hostnames that I am trying to reduce to pids without parent pids by hostname.

Example of source data set

PID       PPID     hostname
4         0        test1
445       4        test1
4442      445      test1
660       16       test1

A simplified version of the massive index that I have so far, the required output would result in the following dataset:

PID       PPID     hostname
4         0        test1
660       16       test1

I have tried the following searches:
index=process NOT | join pid as ppid [ index=process | search ppid ]
eval searches and conditionals
multisearch versions of the same

Any assistance would be greatly appreciated.

[somesoni2 · Accepted solution]

Give this a try (only keep the events where ppid is not available as pid for same host.

index=process | table pid ppid hostname | eventstats values(pid) as pids by hostname | where isnull(mvfind(pids,ppid))

[grimlock]

Works PERFECTLY! Thanks a lot.

To avoid being inundated with pids field I simply modified this search to:

index=process | table pid ppid hostname | eventstats values(pid) as pids by hostname | where isnull(mvfind(pids,ppid)) | fields - pids

[DalJeanis]

You want all records where the PPID does not appear as a PID

"the same search" NOT ["the same search" | rename PID as PPID | table PPID] |

[grimlock]

This answer is also functional but eventstats permits avoidance of subsearch. For small data sets this is a more efficient solution. I will have to be aware of overreaching my subsearch limit. If i had more points, I would award them here.