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)
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
Thanks @gcusello ..i used a modified version of this and it worked.
Hi @akidua ,
good for you, see next time!
Ciao and happy splunking
Giuseppe
P.S.: Karma Points are appreciated 😉
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