topic Re: calculate time difference between timestamps for two results on a same field in Getting Data In

calculate time difference between timestamps for two results on a same field

aramakrishnan — Mon, 23 Mar 2015 18:16:32 GMT

I have data that I have currently exported from splunk by running a search on splunk as follows. The time data (in the table below) was broken down and adjusted on excel.

serial_num="NTEST9B"  | eval sTime=strftime(_time,"%Y-%m-%d %H:%M:%S") |table serial_num host segment_id phase sTime| sort segment_id,host,phase

serial_num  host                          seg_id  phase        Date        time
NTEST9B  ip-typeA.slavenode.test.com   157   callresult   3/13/2015   5:06:55
NTEST9B  ip-typeA.slavenode.test.com   157   precall      3/13/2015   5:06:49
NTEST9B  ip-typeB.slavenode.test.com   158   callresult   3/13/2015   5:06:45
NTEST9B  ip-typeB.slavenode.test.com   158   precall      3/13/2015   5:06:41

For every device, there are segments that are run on a specific host, and I have extracted the time for precall and call result (both of which are values of the field "phase" on splunk) for each segment ID. Duration for processing time for each segment would be difference btw 'precall' and 'callresult', and they would always be from the same host.

I want to modify my search such that for a given device, I get the time difference between both values for my field "phase" (precall and callresult) for a specific segment ID from a given host in the same search. So basically, for host A and segment #157, the time difference would be timestamp (callresult) - timestamp (precall). The next step in this process would be to set an alert when the time difference exceeds a certain amount, and so I want splunk to be able to calculate time difference by itself.

Any help in this regard would be great!

Re: calculate time difference between timestamps for two results on a same field

acharlieh — Mon, 23 Mar 2015 18:56:44 GMT

So borrowing a trick from Kyle Smith's conf2014 talk... We could do something like the following:

serial_num="NTEST9B" | eval p_{phase} = _time | stats first(p_*) as * by serial_num,host,segment_id

This gives us results that has the callresult time and the precall time for every host and segment id for a given serial num.

The calculating the difference is as simple as

... the above search |  eval difference=callresult-precall

difference being of course value in seconds since _time is epoch time.

Re: calculate time difference between timestamps for two results on a same field

aramakrishnan — Mon, 23 Mar 2015 20:53:06 GMT

Thank you, @acharlieh. If I were to set a custom conditional alert on this to set off an alert anytime "difference" > 20, and the column that spits out the time difference is "difference" (it's not a field on Splunk), would using "eval difference>20" as the 'condition' work?

Re: calculate time difference between timestamps for two results on a same field

acharlieh — Mon, 28 Sep 2020 19:19:54 GMT

"difference" is not an extracted field, but the field should be in your results after the eval command. (which of course uses the fields created by the stats command which uses the fields from the previous eval command).
If it is not a field, then there is a problem (you can try | table * at the end of your search to force a tabular view and see what's set or not. I left off the formatting and sorting that you're doing in your original question, but it can be added in, just make sure to carry forward difference.)

Conditions of advanced conditional alerts are like splunk commands tacked on to the end of the search being run. You'd therefore want either "where difference > 20" or "search difference > 20" to alert where the events are greater than 20 seconds apart for a given serial_num, host, segment_id.

Re: calculate time difference between timestamps for two results on a same field

aramakrishnan — Fri, 27 Mar 2015 23:28:29 GMT

As a follow-up to this, I have a new situation where all of the fields from above are the same, except that "phase" is no longer a field. I instead have 2 eventtypes (callstart & callend), one of which will produce the 'precall' logs and the other the 'postcall' logs.

Would eval p_{phase} = _time work if I substitute "phase" with the eventtype?

I still need to perform the exact same function i.e., calculate the time difference between a specific segment ID & host in the callstart and callend eventtypes.

I'm assuming the same eval syntax wouldn't work but if you have suggestions for a modification, please let me know!

Re: calculate time difference between timestamps for two results on a same field

acharlieh — Sat, 28 Mar 2015 04:12:35 GMT

So, a bit of a difference between phase and eventtype is it's much more likely that eventtype could be multivalued than phase. (if that happens, then you wind up with all values as your new field. An alternative syntax therefore could be:

| eval p_precall=if(eventtype="onetype",_time,null()) |  eval p_callresult=if(eventtype="othertype",_time,null()) |