topic Re: How to calculate time difference between two different searches for a common field? in Splunk Search

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

akidua — Mon, 06 Mar 2023 18:16:47 GMT

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)

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

gcusello — Mon, 06 Mar 2023 17:46:31 GMT

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

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

akidua — Fri, 10 Mar 2023 16:06:27 GMT

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

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

gcusello — Sat, 11 Mar 2023 07:07:17 GMT

Hi @akidua ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉