_indextime is a magic pixie-dust field that you can't display directly, you have to copy it to another field to show it. You can, however, use it to sort by.
If you don't need the
_time for anything, you can just assign
_time and proceed as if they were always that way.
| eval _time = _indextime | stats latest(status) as status by TransactionId
If there might be something significant in
_time, then you'll have to sort on
_indextime and use
your query that gets the transactions you want | fields transactionId status | eval IndexTime = _indextime | sort 0 transactionId IndexTime | stats first(IndexTime) as FirstIndexTime, last(IndexTime) as LastIndexTime, first(_time) as FirstEventTime, last(_time) as LastEventTime, last(status) as status by TransactionId
The events are not nearly simultaneous. The index time has a difference in hours.All the events for same transaction id are indexed by starttime, so _time is same but indextime is different with good gap in between
There are two ways I can think of: using
_indextime or using
latest on the grouping field.
max functions will definitely give you the first and last values for
_indextime but that will not be performant.
latest functions SHOULD give the first and last events as they were seen by Splunk, so I assume that effectively means by
_indextime. This would be much faster than using
You will need to share your search details If you need help with the search.
Does Earliest/latest work on index time if _time is same for all events. I am getting random results and not on based on indextime. I am using below search . There are 10 events for trans_id=123 with different status and same _time
index=test trans_id=123|eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S")|stats earliest(status) earliest(indextime) by trans_id
The above search is not giving the oldest event. It is picking random event out of 10 events. I have a distributed environment with more than 20 indexers.
Well, first note that
_indextime has a resolution of a second, so you will have issues if the events need a finer grained resolution.
What do you get if you do this. Consistent result?
index=test trans_id=123|stats earliest(status) min(_indextime) as indextime by trans_id | eval indextime=strftime(indextime,"%Y-%m-%d %H:%M:%S")