Splunk Search

How Calculate the difference between two time fields?

vrmandadi
Builder

alt textHello 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

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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.

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

vrmandadi
Builder

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

vrmandadi
Builder

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

0 Karma

vrmandadi
Builder

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

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...