How to get most recent event without scanning entire index?

[pr0n]

I have this search below, which is incredibly slow because it has to scan the entire index and run the eval on millions of records. There is no need however; I only need the most recent event per source, host pair. The append and join is purely to make sure I can easily spot cases where there are no events. Time picker doesn't really work as some sources have a much lower frequency than others, I'd really like to just select the most recent event for each source and host.

index="blah" host=asdf-* | eval delta=now()-_time |  
append [| metadata type=hosts | search host=asdf-* | eval join=1 | 
join max=0 join [| metadata type=sources | search source=asdf*.log| eval join=1] | fields + host,source] |
stats min(delta) as "seconds_since_last_event" by host source | where seconds_since_last_event>1800 OR isnull(seconds_since_last_event)

[woodcock · Accepted solution]

Here is a run-anywhere example which gets the latest audit event. Switch index and sourcetype values to suit your usecase:

index=_* AND sourcetype=audittrail AND [|tstats max(_time) AS earliest latest(_indextime) AS index_earliest WHERE index=_* AND sourcetype=audittrail
| eval latest = earliest + 1
| eval index_latest = index_earliest + 1
| format "(" "" "AND" "" "" ")"
| rex field=search mode=sed "s/\"//g s/index_/_index_/g"]
| head 1

[pr0n]

This is what I was looking for but your first line got me there, I'm not 100% clear on what the rest is trying to do tbh.

|tstats max(_time) as time WHERE index=blah by host sourcetype | eval seconds_ago = now()-time

[somesoni2]

How many number of host-source combination can exist in your Splunk instance? If it's not that large, give this a try (will still be slow but faster than your previous attempts)

 index="blah" host=asdf-* [| tstats max(_time) as _time WHERE  index="blah" host=asdf-* by host source | format ]