Splunk Search

How to find the oldest log indexed in the indexer instances ?

Hemnaath
Motivator

Hi All, Currently we are running out of space in our indexer instance and we wanted to remove the oldest data that is in our indexer and stored more than 1 year from the indexer instances. When we tried the below search, to get the oldest data that got indexed in the indexer instances but it is taking too long time to get the result when time frame is set to ALL time

index=* | stats first(_time) as latest  last(_time) as earliest by index | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(earliest) ctime(latest)

Kindly let me know if there is a better search, which can get the oldest data that are being indexed and stored in indexer instance more than a year.

thanks in advance.

0 Karma
1 Solution

Hemnaath
Motivator

Thanks Somesoni2, we could run the update query in the splunk indexer instance web portal. But we
had another method to find out the oldest indexed data that is still in the indexer instance from

splunk web portal -- > settings --> data inputs --> indexes --> index name --> Earliest event and Latest event will tell you the oldest data and latest data that are their in the index instance.

View solution in original post

0 Karma

wrangler2x
Motivator

For a particular host:

| tstats min(_time) as earliest max(_time) as latest WHERE index=winevent_dc_index host=HIT-DC03.hsg.wangotango.edu by host
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(earliest) ctime(latest)

Where 'winevent_dc_index' is the index you intend (or use * for all non-internal) and 'HIT-DC03.hsg.wangotango.edu' is the hostname you are looking for. Use "All Time" on the time-picker.

0 Karma

woodcock
Esteemed Legend

I am taking you literally that you need it broken down for ALL data BY EACH indexER:

 | tstats min(_time) as latest max(_time) AS earliest WHERE index=* OR index=_* BY index splunk_server | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(earliest) ctime(latest)

You probably don't need to include the _* indices so then this:

 | tstats min(_time) as latest max(_time) AS earliest WHERE index=* BY index splunk_server | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(earliest) ctime(latest)

If you don't need it broken down by each indexer then:

 | tstats min(_time) as latest max(_time) AS earliest WHERE index=* BY index | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(earliest) ctime(latest)

Hemnaath
Motivator

Thanks Somesoni2, we could run the update query in the splunk indexer instance web portal. But we
had another method to find out the oldest indexed data that is still in the indexer instance from

splunk web portal -- > settings --> data inputs --> indexes --> index name --> Earliest event and Latest event will tell you the oldest data and latest data that are their in the index instance.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Heh. Yep, no query needed that way.

Please promote your comment to an answer and accept it so that people who find this question will know the easy way. Also, please upvote somesoni2's useful and helpful answer as well.

0 Karma

lakromani
Builder

On later system its:
splunk web portal -> setttings -> indexes

But if you are on a search head with indexes stored on other server (index server) you will not see any data there.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Use tstats instead.
Updated

| tstats min(_time) as latest max(_time) as earliest WHERE index=* by index | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(earliest) ctime(latest)

Hemnaath
Motivator

Hi Somesoni2, I am getting an error while executing the above mentioned query

error details : Error in 'TsidxStats': _time aggregations are not yet supported except for min/max

we are using the splunk 6.0.3 version

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try the updated answer.

0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...