Deployment Architecture

How to get most recent event without scanning entire index?

pr0n
Explorer

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)
Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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
Explorer

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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 ]
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...