Find indexes that got events for the first time in the selected time range out of a list of all indexes

[tkrshn]

Hi folks,

I have been trying to create a query that would list index name and earliest event from a list of indexes that started getting events only during the selected time range.

First I'd populate the list of indexes using a query like so 

index=_internal source=/opt/splunk/var/log/splunk/cloud_monitoring_console.log* TERM(logResults:splunk-ingestion)
| rename data.* as * 
| fields idx 

 
I want to find out which of the indexes out of this list started to index events for the first time only in the, say, last one month.

I tried joining this query over idx like so where `tstats` would give me the earliest event timestamp in the last 6 months (a good approximation of whether that index ever got data before the last one month).

index=_internal source=/opt/splunk/var/log/splunk/cloud_monitoring_console.log* TERM(logResults:splunk-ingestion)
| rename data.* as * 
| fields idx 
| rename idx as index
| join index [ | tstats earliest(_time) as earliest_event where earliest=-6mon latest=now index=* by index | table index earliest_event]

 But this is only giving me correct results when I specify an index name in the base query. For some reason, it doesn't give me proper results for all indexes.

I tried the `map` command as well passing index dynamically but the performance of that query isn't ideal as there are 100s of indexes.

I also tried other commands like append but none would give the outcome as expected.

I think that there is an obvious solution here that's somehow eluding me.

Appreciate any help around this.

[ITWhisperer]

Try something like this

| tstats earliest(_time) as earliest_event where earliest=-6mon latest=now [search index=_internal source=/opt/splunk/var/log/splunk/cloud_monitoring_console.log* TERM(logResults:splunk-ingestion)
| rename data.* as * 
| fields idx 
| rename idx as index] by index

[tkrshn]

Sorry about the late update.

This gives me earliest events' _time for all the selected indexes. I still have to filter out those that have been created in my selected time range which seems doable as below. But for some reason running this isn't giving be the answer I want. 

Just like a join wouldn't work for  index=*  as opposed to an individual index. I can't explain what's happening.

| tstats min(_time) as earliest_event where earliest=-6mon latest=now [search index=_internal source=/opt/splunk/var/log/splunk/cloud_monitoring_console.log* TERM(logResults:splunk-ingestion) earliest=-30d latest=now
| rename data.* as *
| fields idx
| rename idx as index] by index
| eval cutoff = relative_time(earliest_event,"-30d")
| where earliest_event>cutoff