Obtain difference of a field between 2 searches

[benjillaz]

Hello Splunkers

I just started to use splunk and you know how it is to learn something new, you punch the keyboard lots of times haha.

Well I have 2 timestamps (besides a lot of other fields):
2016-03-10 04:16:19
2016-03-10 04:16:40

Each of them comes from a different search:
Search1:index="app_log" field4=333166 status="started"
Search2:index="app_log" field4=333166 status="completed"

So what I'm trying to do is to know how much time a process took in this case field4 is an identifier so the first search will provide me when it started with the corresponding timestamp and the second one will provide me when it finished so the time they took will be the difference between the timestamps.
What I'm having issues is to built this, how to perform the both searches and play around with them.

Can you please help me out

Regards

[lguinn2 · Accepted solution]

You don't really need two searches. Try this

index="app_log"  status="started" OR status="completed"
| eval starttime=if(status=="started",_time,null())
| eval endtime=if(status=="completed",_time,null())
| stats earliest(starttime) as starttime latest(endtime) as endtime by field4
| eval duration=endtime-starttime
| fieldformat Duration=tostring(duration,"duration")
| fieldformat starttime=strftime(starttime,"%x %X")
| fieldformat endtime=strftime(endtime,"%x %X")
| table field4 Duration starttime endtime

This is a little bit overkill, but I thought you might like the formatted results.

[benjillaz]

Thanks lguinn this is pretty helpful i was able to get what i wanted.
Now i want to get the maximum and minimum duration of the ID,s i'm doing the next but is retrieving all the fields, any ideas?

| eval maximum=max(duration)
| eval minimum=min(duration)
| where duration=maximum or duration=minimum
| table ID_Deal duration maximum minimum

[martin_mueller]

After the eval duration=... you can use stats like this:

... | stats max(duration) as maximum min(duration) as minimum

[martin_mueller]

What you have there is a transaction with a defined start, a defined end, and a transaction ID?

index=app_log field4=* (status=started OR status=completed) | transaction field4 startswith="status=started" endswith="status=completed"

Look for the duration field computed by the transaction command.

http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/transaction

[benjillaz]

I was able to make it work like you said Martin
transaction Deal_ID startswith="STARTED" endswith="COMPLETED" maxspan=60m
| eval END=strftime(_time+duration, "%m/%d/%Y %H:%M:%S")
| eval START=strftime(_time, "%m/%d/%Y %H:%M:%S") | table Deal_ID ,START,END

Thanks a lot Martin, appreciate it

[martin_mueller]

Great. Nice thing about transaction is that you have greater controls about starts, ends, spans, pauses, etc. - and you can even have non-unique IDs as long as they don't overlap.

[benjillaz]

Thanks for replying this, i tried but was not able to make it work, i'm still kind of new.