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

[Hemnaath]

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.

[wrangler2x]

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.

[woodcock]

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 · Accepted solution]

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.

[DalJeanis]

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.

[lakromani]

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.

[somesoni2]

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]

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

[somesoni2]

Try the updated answer.