topic How Calculate the difference between two time fields? in Splunk Search

How Calculate the difference between two time fields?

vrmandadi — Tue, 29 Sep 2020 09:10:06 GMT

Hello all,

I am trying to calculate the difference between two time fields.Below is the query which I ran to get the output .i have done mvexpand on three fields ENDPOINT_LOG{}.EML_REQUEST_TIME,ENDPOINT_LOG{}.EML_RESPONSE_TIME,ENDPOINT_LOG{}.EML_REQ_CONN_URI since there are two fields in a single event.

index=test | mvexpand ENDPOINT_LOG{}.EML_REQUEST_TIME|mvexpand ENDPOINT_LOG{}.EML_RESPONSE_TIME |mvexpand ENDPOINT_LOG{}.EML_REQ_CONN_URI | eval req_time=strptime("(ENDPOINT_LOG{}.EML_REQUEST_TIME)","%Y-%m-%d %H:%M:%S.%3N") | eval res_time=strptime("(ENDPOINT_LOG{}.EML_RESPONSE_TIME)","%Y-%m-%d %H:%M:%S.%3N") | eval TimeDiff=res_time-req_time |table TimeDiff, ENDPOINT_LOG{}.EML_REQUEST_TIME,ENDPOINT_LOG{}.EML_RESPONSE_TIME,ENDPOINT_LOG{}.EML_REQ_CONN_URI

output

ENDPOINT_LOG{}.EML_REQUEST_TIME ENDPOINT_LOG{}.EML_RESPONSE_TIME ENDPOINT_LOG{}.EML_REQ_CONN_URI time_diff
2016-01-19 15:44:00.749 +00:00 2016-01-19 15:44:02.366 +00:00 connection:/s4970qalv/DCD_DB2

2016-01-19 15:44:00.749 +00:00 2016-01-19 15:44:02.366 +00:00 connection:/s4970qalv/DCD_PB1
2016-01-19 15:44:00.749 +00:00 2016-01-19 15:44:02.366 +00:00 connection:/s4970qalv/DCD_DB2
2016-01-19 15:44:00.749 +00:00 2016-01-19 15:44:02.366 +00:00 connection:/s4970qalv/DCD_PB1
2016-01-19 15:44:01.163 +00:00 2016-01-19 15:44:02.366 +00:00 connection:/s4970qalv/DCD_DB2

I want to calculate the time difference of ENDPOINT_LOG{}.EML_RESPONSE_TIME from
ENDPOINT_LOG{}.EML_REQUEST_TIME and store in time_diff

but when I ran the query the time_diff is having all null ,the eval command is not executing and storing result in time_diff

Can anyone please help in this

Re: How Calculate the difference between two time fields?

martin_mueller — Tue, 22 Mar 2016 21:47:10 GMT

In order to use field names with special characters such as a dot in eval you have to enclose them in single quotes:

... | eval req_time = strptime('ENDPOINT_LOG{}.EML_REQUEST_TIME', "%Y-%m-%d %H:%M:%S.%3N") | ...

Check that each time field is filled with an epoch timestamp before proceeding.

Re: How Calculate the difference between two time fields?

vrmandadi — Tue, 22 Mar 2016 22:13:04 GMT

Thank You so much martin_muller.

One small question ,I got the timediff for each and every request and response but i want to calculate the average of timediff

I ran the query

|stats avg(timediff) which is giving me a wrong value

Re: How Calculate the difference between two time fields?

martin_mueller — Tue, 22 Mar 2016 22:29:12 GMT

That would be the correct approach, in what way is the returned value wrong?

Keep in mind to stick with a field name, I've seen time_diff, timediff, and TimeDiff in your posts.

Re: How Calculate the difference between two time fields?

vrmandadi — Tue, 22 Mar 2016 23:08:50 GMT

ya,I used TimeDiff,but I think since I used three mvexpand the events are duplicated and strangely some fields have TimeDiff with negative values ,but when looked into that particular event the difference between request and response time is not negative.I dont know how splunk populated new request and response times

Re: How Calculate the difference between two time fields?

martin_mueller — Tue, 22 Mar 2016 23:19:28 GMT

If you have two arrays of values and expand each array into individual events, you will then calculate an n*m matrix of differences, not the probably intended a1-b1, a2-b2, ....

I'd guess you need to mvexpand once on ENDPOINT_LOG{} to keep matching pairs together, but without knowing the data that's just that - a guess.

Re: How Calculate the difference between two time fields?

vrmandadi — Tue, 22 Mar 2016 23:38:05 GMT

I have updated an image along with the query,so that you have a better idea of the query,I want the timediff for each uri for its corresponding request and reponse time