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
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.
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.
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.
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
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.
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
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