Splunk Search

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

Log_wrangler
Builder

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

Tags (2)
0 Karma
1 Solution

skoelpin
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
Revered Legend

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
Builder

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.

0 Karma

aokur_splunk
Splunk Employee
Splunk Employee

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)
0 Karma

skoelpin
SplunkTrust
SplunkTrust

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

View solution in original post

Log_wrangler
Builder

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

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.