topic Re: How to get time between events during a search? in Splunk Search

How to get time between events during a search?

ayela — Tue, 26 Jun 2018 02:24:42 GMT

Hi everyone,

Recently I faced some issues when I try to do an advance search.
My problem : I need to create table that contains : id | duration

I search for the first appearance keyword and get the time. Example : Received 115sd65sa25sa. 115sd65sa25sa is my id and Received is my keyword let say the variable a = _time.
Now for each id I want to search when it was sent (I also have a keyword for that) Example : Sent115sd65sa25sa b=_time
So at the end I should have a table that contains the id and b-a

I tried to use different techniques like map and sub-search but it doesn't seem to work.

Thank you very much

Re: How to get time between events during a search?

renjith_nair — Tue, 26 Jun 2018 02:38:43 GMT

Hi @ayela ,
Do you have sample events for both received and sent?

Re: How to get time between events during a search?

amiftah — Tue, 26 Jun 2018 03:48:04 GMT

I used my imagination to try to figure out what you want to achieve, so here's a sample of query it may help you:

| makeresults
| eval id="Received", value="115sd65sa25sa", _time=relative_time(_time, "-2min")
| append
[| makeresults
| eval id="Sent", value="115sd65sa25sa"]
| stats list(id) as id,list(_time) as "time" by value 
| eval a = mvindex(time, 0) 
| eval b = mvindex(time, 1) 
| eval diff=b-a
| eval diff = tostring(diff, "duration")
| fields - id, time,a,b

Re: How to get time between events during a search?

ayela — Tue, 29 Sep 2020 20:12:06 GMT

index="c" sourcetype="flox:app" laas_env=dev laas_file="/var/tmp/test/logs/dev.log" "Source Message Received" "_TEST01*" | xmlkv | table userId, _time

the previous query works it gets all the userId and the initial time

now I want to do something like this :

for each userId
index="c" sourcetype="flox:app" laas_env=dev laas_file="/var/tmp/test/logs/dev.log" "sent" "_TEST01" userId | top limit=1 _time | table userId, ( _time2 - _time)

_time2 is the the time when it was sent
_time is the time we received the id
userId : value of the xml tag

Re: How to get time between events during a search?

ayela — Tue, 29 Sep 2020 20:12:08 GMT

Thank you for responding.

Sorry if it was not clear.

here's an example pf the query I try to do

index="c" sourcetype="flox:app" laas_env=dev laas_file="/var/tmp/test/logs/dev.log" "Source Message Received" "_TEST01*" | xmlkv | table userId, _time

the previous query works it gets all the userId(from the xml) and the initial time

now I want to do something like this :

for each userId
index="c" sourcetype="flox:app" laas_env=dev laas_file="/var/tmp/test/logs/dev.log" "sent" "_TEST01" userId | top limit=1 _time | table userId, ( _time2 - _time)

_time2 is the the time when it was sent
_time is the time we received the id
userId : value of the xml tag

Re: How to get time between events during a search?

renjith_nair — Wed, 27 Jun 2018 02:34:29 GMT

Hi @ayela ,

Try this and let me know what's missing

index="c" sourcetype="flox:app" laas_env=dev laas_file="/var/tmp/test/logs/dev.log" ("Source Message Received" OR "sent")
|eval startime=if(searchmatch("Source Message Received"),_time,null())
|eval endtime=if(searchmatch("sent"),_time,null())
|table _time,startime,endtime,userId
|where (startime!="" OR endtime!="")
|stats latest(startime) as start,latest(endtime) as end by userId
|eval time_diff=end-start
|table userId,time_diff

Re: How to get time between events during a search?

ayela — Wed, 27 Jun 2018 16:42:46 GMT

THANK YOU !!!!
you just misspelled starttime but thank you very much !!