How to calculate time difference between two different searches for a common field?

[akidua]

I have 2 different search queries and I want to calculate sum of differences between time of event 1 and event 2 (in hours) for a common field (customID)

Query 1:
index=xacin sourcetype="xaxd" "*Completed setting deactivation timer for*" OR "grace period" | rex "[cC]ustom:(?<customID>\w+)"| dedup customID| eval ltime=_time

customID	ltime
wj	1678118565.572
bi8m	1678089668.915
nri	1678060951.505

Query 2:
index=xacin sourcetype="xaxd" "*StatusHandler - Completed moving *" | rex "custom:(?<customID>\w+)"| dedup customID |eval rtime=_time

customID	rtime
bi8m	1678118477.707
a2su	1678118456.775
ceo	1678118425.484
nri	1678089748.844

Since bi8m and nri are common customID, I need to output : (1678118477.707-1678089668.915) + (1678089748.844 -1678060951.505) = 57606.131 

I tried to come up with the following query but clearly it's not working:

index=xacin sourcetype="xaxd" "*Completed setting deactivation timer for*" OR "grace period" | rex "[cC]ustom:(?<customID>\w+) "| dedup customID| eval ltime=_time | append [search index=xacin sourcetype="xaxd" "*StatusHandler - Completed moving *" | rex "custom:(?<customID>\w+)"| dedup customID| eval rtime=_time | stats count by customID | where count > 1 | eval time_diff=(rtime-ltime)| stats sum(time_diff)

[akidua]

Thanks @gcusello ..i used a modified version of this and it worked.

[gcusello]

Hi @akidua ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello · Accepted solution]

Hi @akidua,

you could try something like this:

index=xacin sourcetype="xaxd" ("*Completed setting deactivation timer for*" OR "grace period" OR "*StatusHandler - Completed moving *"
| rex "[cC]ustom:(?<customID>\w+)"
| rex "custom:(?<customID>\w+)"
| eval 
  rtime=if(searchmatch(like(("%StatusHandler - Completed moving %"),_time,""),
  ltime=if(searchmatch(like(("%StatusHandler - Completed moving %"),"",_time)
| stats earliest(rtime) AS rtime latest(ltime) AS latest count BY customID 
| where count > 1 
| eval time_diff=(rtime-ltime)
| stats sum(time_diff)

Ciao.

Giuseppe