Hi vrmandadi,
This was a tricky one, but I got there finally 😉
Your problem is that your needed values are within one event and therefore the SPL command to get the result is a bit tricky, because it contains zipping those multivalued fields and splitting them again base on the field ENDPOINT_LOG{}.EML_LAST_UPDATE_TS because this is unique for the information.
So, here is the command which will work on the provided event example indexed as _json sourcetype:
source="foo.json" host="indexer" sourcetype="_json"
| eval my_id = 'ENDPOINT_LOG{}.EML_ID'
| eval request= 'ENDPOINT_LOG{}.EML_REQUEST_TIME'
| eval response = 'ENDPOINT_LOG{}.EML_RESPONSE_TIME'
| eval update_time = 'ENDPOINT_LOG{}.EML_LAST_UPDATE_TS'
| table update_time my_id request response | sort - update_time
| eval zipped = mvzip(update_time, my_id, "###") | eval zipped = mvzip(zipped, request, "###") | eval zipped = mvzip(zipped, response, "###")
| mvexpand zipped | makemv delim="###" zipped
| eval update_time = mvindex(zipped, 0) | eval my_id = mvindex(zipped, 1) | eval request = mvindex(zipped, 2) | eval response = mvindex(zipped, 3)
| fields - zipped
| eval average_response_time = strptime(response, "%Y-%m-%d %H:%M:%S.%3N") - strptime(request, "%Y-%m-%d%H:%M:%S.%3N")
What happens here is the following:
source="foo.json" host="indexer" sourcetype="_json"
The base search to get the event
| eval my_id = 'ENDPOINT_LOG{}.EML_ID'
| eval request= 'ENDPOINT_LOG{}.EML_REQUEST_TIME'
| eval response = 'ENDPOINT_LOG{}.EML_RESPONSE_TIME'
| eval update_time = 'ENDPOINT_LOG{}.EML_LAST_UPDATE_TS'
Just some eval's to make the field names more usable
| table update_time my_id request response | sort - update_time
A little table added, not need at all but helps to show the information
| eval zipped = mvzip(update_time, my_id, "###") | eval zipped = mvzip(zipped, request, "###") | eval zipped = mvzip(zipped, response, "###")
zip all the multivalued fields together into one new filed called zipped
| mvexpand zipped | makemv delim="###" zipped
expand it and set the delimiter to be ###
| eval update_time = mvindex(zipped, 0) | eval my_id = mvindex(zipped, 1) | eval request = mvindex(zipped, 2) | eval response = mvindex(zipped, 3)
get the values based on position in the zipped field into new single value fields
| fields - zipped
remove the zipped field and
| eval average_response_time = strptime(response, "%Y-%m-%d %H:%M:%S.%3N") - strptime(request, "%Y-%m-%d%H:%M:%S.%3N")
do some maths to get the response time for each transaction - tata 🙂 the result looks like this
Hope this make sense and is useful ...
cheers, MuS
Hi vrmandadi,
This was a tricky one, but I got there finally 😉
Your problem is that your needed values are within one event and therefore the SPL command to get the result is a bit tricky, because it contains zipping those multivalued fields and splitting them again base on the field ENDPOINT_LOG{}.EML_LAST_UPDATE_TS because this is unique for the information.
So, here is the command which will work on the provided event example indexed as _json sourcetype:
source="foo.json" host="indexer" sourcetype="_json"
| eval my_id = 'ENDPOINT_LOG{}.EML_ID'
| eval request= 'ENDPOINT_LOG{}.EML_REQUEST_TIME'
| eval response = 'ENDPOINT_LOG{}.EML_RESPONSE_TIME'
| eval update_time = 'ENDPOINT_LOG{}.EML_LAST_UPDATE_TS'
| table update_time my_id request response | sort - update_time
| eval zipped = mvzip(update_time, my_id, "###") | eval zipped = mvzip(zipped, request, "###") | eval zipped = mvzip(zipped, response, "###")
| mvexpand zipped | makemv delim="###" zipped
| eval update_time = mvindex(zipped, 0) | eval my_id = mvindex(zipped, 1) | eval request = mvindex(zipped, 2) | eval response = mvindex(zipped, 3)
| fields - zipped
| eval average_response_time = strptime(response, "%Y-%m-%d %H:%M:%S.%3N") - strptime(request, "%Y-%m-%d%H:%M:%S.%3N")
What happens here is the following:
source="foo.json" host="indexer" sourcetype="_json"
The base search to get the event
| eval my_id = 'ENDPOINT_LOG{}.EML_ID'
| eval request= 'ENDPOINT_LOG{}.EML_REQUEST_TIME'
| eval response = 'ENDPOINT_LOG{}.EML_RESPONSE_TIME'
| eval update_time = 'ENDPOINT_LOG{}.EML_LAST_UPDATE_TS'
Just some eval's to make the field names more usable
| table update_time my_id request response | sort - update_time
A little table added, not need at all but helps to show the information
| eval zipped = mvzip(update_time, my_id, "###") | eval zipped = mvzip(zipped, request, "###") | eval zipped = mvzip(zipped, response, "###")
zip all the multivalued fields together into one new filed called zipped
| mvexpand zipped | makemv delim="###" zipped
expand it and set the delimiter to be ###
| eval update_time = mvindex(zipped, 0) | eval my_id = mvindex(zipped, 1) | eval request = mvindex(zipped, 2) | eval response = mvindex(zipped, 3)
get the values based on position in the zipped field into new single value fields
| fields - zipped
remove the zipped field and
| eval average_response_time = strptime(response, "%Y-%m-%d %H:%M:%S.%3N") - strptime(request, "%Y-%m-%d%H:%M:%S.%3N")
do some maths to get the response time for each transaction - tata 🙂 the result looks like this
Hope this make sense and is useful ...
cheers, MuS
