Hi Experts
What is the best way to get first and last event by _indextime. I want to group by events based on transaction Id and get the latest status based on indextime not on _time.
Please provide your inputs.
Okay, _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 _indextime
to _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 last()
...
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
What is the use case for needing to know which nearly-simultaneous event was technically first or last?
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 min
/max
on _indextime
or using earliest
/latest
on the grouping field.
Using functions min
/max
functions will definitely give you the first and last values for _indextime
but that will not be performant.
Using earliest
/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 min
/max
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")