How to return time of first event in an index?

[muebel]

How could you find the time for the first event in an index? I.E. the oldest event? Is there a way beyond "index=foo *", searching over all time, and then waiting a good while?

[btiggemann]

Hi,

I am now looking for the same thing like you did a long time ago.
I want to find out which hosts have been added to Splunk in 2016, just to do a documentation of our data onboarding process.
My idea was something like this:

| metadata type=sourcetypes |convert ctime(firstTime) AS input_time |convert ctime(lastTime) AS last_event_time |where firstTime>1451606400 AND firstTime<1483228800

Is the metadata affected by the retention policy of an index?
For example retention time is set to 180days so firstTime will not be older than 180d max?

I checked this, some sourcetypes have events that are older than 2015. But the oldest/earliest event of sourcetype=opsec is just 10 days ago but we did the data input at the beginning of 2016.
Are there some reasons why |metadata would not work always?

[David]

Per my comment on Lowell's answer, the metadata approach can be misleading because if you have a single host that is throwing out incorrect timestamps, you can be in a position where you think you have more retention than you really do. There are two more complete approaches to solve this problem.

You could search for this via dbinspect and leverage some flexible stats to figure out exactly what you want:

| dbinspect index=os 
| eval midpoint =(startEpoch+endEpoch)/2 
| eventstats perc5(midpoint) as approximate_earliest  
| eventstats values(eval(if(midpoint=approximate_earliest,startEpoch,null))) as start values(eval(if(midpoint=approximate_earliest,endEpoch,null))) as end   
| eval eventsEarlier=case(endEpoch<start,eventCount,startEpoch>end,0,1=1,min(1,max(0,(approximate_earliest-startEpoch)/(endEpoch - startEpoch))) * eventCount)  
| stats sum(eventCount) as TotalEvents sum(eventsEarlier) as EventsBeforeApproximateEarliest values(approximate_earliest) as approximate_earliest  
| eval PercentageOfEventsBeforeEarliest = round(EventsBeforeApproximateEarliest/TotalEvents * 100,2) | convert ctime(approximate_earliest)  
| eval EventsBeforeApproximateEarliest=round(EventsBeforeApproximateEarliest)  
| rename EventsBeforeApproximateEarliest as "Events Before Approximate Earliest" approximate_earliest as "Approximate Earliest" TotalEvents as "Total Events for Index" PercentageOfEventsBeforeEarliest as "% of Events before Approximate Earliest"  
| fields "Approximate Earliest" "Events*" "Total*" "*Events*"  
| eval Note = "This is based on a 5th Percentile. You may want to tweak to tenth in your enviornment.."

(Note that is a slightly silly search written before daily coffee.. but should meet needs).

But a better answer is to rely on tools that exist specifically to give you visibility into the age of your indexes, such as Fire Brigade.

[abhib89]

Below query can give you the latest even from any host. Can be manipulated further to identify from any index or source type as well.

index=index_name host=host_name sourcetype=st_name| eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") | table host, indextime | tail 1

[NageswariV]

Thanks for the query. In addition, you can give a note at the end for the fields to be filled in by the user, to be more helpful.

index=index_name host=host_name sourcetype=st_name| eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") | table host, indextime | tail 1

Note: Fill in "index_name, host_name and st_name" fields based on the requirement

[Lowell · Accepted solution]

You can get first/last information from your index like this using the metadata command, that is the fastest way to get this information:

| metadata index=foo type=hosts | stats max(lastTime), min(firstTime)

If you want to convert that into a more readable time format, try something like this:

| metadata index=foo type=hosts | stats max(lastTime) as lastTime, min(firstTime) as firstTime | convert ctime(*Time)

[dveuve_splunk]

Be aware that if you have one host sending data in with the wrong timestamp, that will show up here. Most of your data could be a week old, but that one host with NTP disabled and a time setting of 1.25 years ago will make you think you are meeting your data retention requirement.