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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...