Splunk Search

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

akidua
Explorer

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

customIDltime
wj1678118565.572
bi8m1678089668.915
nri1678060951.505


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

customIDrtime
bi8m1678118477.707
a2su1678118456.775
ceo1678118425.484
nri1678089748.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)

Labels (4)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

akidua
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @akidua ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...