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

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

cyvi01
Path Finder
| 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.



0 Karma

Log_wrangler
Builder

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

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...