Splunk Search
Highlighted

Latest Event by _indextime

Communicator

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.

Tags (2)
0 Karma
Highlighted

Re: Latest Event by _indextime

Champion

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.

0 Karma
Highlighted

Re: Latest Event by _indextime

Communicator

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 transid=123 with different status and same _time

index=test transid=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.

0 Karma
Highlighted

Re: Latest Event by _indextime

Champion

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")

0 Karma
Highlighted

Re: Latest Event by _indextime

SplunkTrust
SplunkTrust

What is the use case for needing to know which nearly-simultaneous event was technically first or last?

0 Karma
Highlighted

Re: Latest Event by _indextime

Communicator

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

0 Karma
Highlighted

Re: Latest Event by _indextime

SplunkTrust
SplunkTrust

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