topic Re: How do you find the earliest event in an index by sourcetype and source? in Splunk Search

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

Log_wrangler — Thu, 19 Apr 2018 01:02:20 GMT

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

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

skoelpin — Thu, 19 Apr 2018 01:55:30 GMT

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")

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

somesoni2 — Thu, 19 Apr 2018 04:51:11 GMT

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)

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

Log_wrangler — Thu, 19 Apr 2018 14:02:18 GMT

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.

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

Log_wrangler — Thu, 19 Apr 2018 14:03:31 GMT

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

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

aokur_splunk — Tue, 21 Jan 2020 18:17:40 GMT

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)

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

cyvi01 — Tue, 15 Nov 2022 10:50:00 GMT

| 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.