02-22-2023 10:34 AM - edited 02-22-2023 10:35 AM
Hi SARowe_NZ,
If I understand your use-case correctly I think using a left join could work. A left join will include all results from the parent query plus any results from the join result where it intersects with the parent. In the example below I am using the _time field with various functions and operators to only join the results if the shutdown event occurs within one minute (in seconds) of the process stop event, as well as requiring the hostnames to match. The results have some process stop events joined with related shutdown events. Next, I just filter for any log rows where my field "related_shutdown_event" equals null, which leaves me with the process stop events that do not have a related shutdown event. Let me know if this works for you!
dataset = xdr_data
| filter event_type = ENUM.PROCESS and event_sub_type = ENUM.PROCESS_STOP and actor_process_image_name = "PanGPS.exe"
|join conflict_strategy = both type = left (dataset = xdr_data
| filter action_evtlog_event_id = 1074 and action_evtlog_message contains "has initiated the restart of computer"|fields agent_hostname, action_evtlog_description as related_shutdown_event) as resulting_records agent_hostname = resulting_records.agent_hostname and add(to_epoch(_time, "SECONDS"), 60) >= to_epoch(resulting_records._time) and subtract(to_epoch(_time, "SECONDS"), 60) <= to_epoch(resulting_records._time)
|filter related_shutdown_event = null
|dedup _time by desc agent_hostname
Regards,
Ben
