topic Re: How to find the oldest log indexed in the indexer instances ? in Splunk Search

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

Hemnaath — Mon, 20 Mar 2017 15:17:12 GMT

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.

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

somesoni2 — Mon, 20 Mar 2017 15:20:05 GMT

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)

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

Hemnaath — Mon, 20 Mar 2017 15:29:39 GMT

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

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

somesoni2 — Mon, 20 Mar 2017 15:33:48 GMT

Try the updated answer.

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

Hemnaath — Mon, 20 Mar 2017 16:11:26 GMT

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.

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

DalJeanis — Mon, 20 Mar 2017 16:22:15 GMT

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.

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

woodcock — Mon, 20 Mar 2017 16:28:14 GMT

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)

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

wrangler2x — Tue, 29 Sep 2020 17:31:31 GMT

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.

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

lakromani — Thu, 05 Sep 2019 06:48:11 GMT

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.