How do you find the earliest event in an index by sourcetype and source?

[Log_wrangler]

Hi

I have index = A sourcetype = A and source = /tmp/A.app.log

I want to find the earliest event (date and time) for the above.

Please advise how to write this query.

Thank you

[somesoni2]

Try the tstats command with appropriate time range (try avoid using 'All times', choose a time range large enough that you know there would be some events for that index/sourcetype/source combination).

For specific index/sourcetype/source combination

| tstats max(_time) as lastReportedOn by index=A sourcetype=A source=/tmp/A.app.log by index sourcetype source | convert ctime(lastReportedOn)

For all sourcetype/source combinations in an index

| tstats max(_time) as lastReportedOn by index=A  by index sourcetype source | convert ctime(lastReportedOn)

[Log_wrangler]

Thank you for the reply, for some reason |tstats is not working in my environment, I have used it before in other environments / deployments and it worked. Metasearch does work.

[aokur_splunk]

slight typo on the query - this tstats search is faster than the metadata one if correctly typed:

   | tstats max(_time) as lastReportedOn where index=A sourcetype=A source=/tmp/A.app.log by index sourcetype source | convert ctime(lastReportedOn)

[skoelpin · Accepted solution]

Try this.. Set it to all-time. It uses the tsidx files for searching so it will be quick

| metasearch index = A sourcetype=A AND source="/tmp/A.app.log"
| stats earliest(_time) AS Earliest_Time
|  eval Earliest_Time=strftime(Earliest_Time,"%Y-%m-%d %H:%M:%S")

[cyvi01]

| stats earliest(_time) AS Earliest_Time
|  eval Earliest_Time=strftime(Earliest_Time,"%Y-%m-%d %H:%M:%S")

can be replaced by a simple 

| stats earliest(_time) AS _time

Splunk formats _time by default which allows you to avoid having to reformat the display of another field dedicated to time display.

[Log_wrangler]

Thank you for the reply, your metasearch is giving me results.