Splunk Search

Latest Event by _indextime

vaibhavagg2006
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

DalJeanis
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

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

vaibhavagg2006
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

rjthibod
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

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

0 Karma

rjthibod
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
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...