Splunk Search

How to display the time field?

akarivaratharaj
Communicator

In one of the search queries, I am displaying the Latest and Oldest value of a field. Please refer the below sample query:

index=main source = xyz earliest=-6mon
| stats last(size) as "Latest", first(size) as "Old"

In the above query, I am considering the last 6 months of data and trying to get the latest & oldest value of the field 'size'. I would like to display these values with their respective date or timestamp.

Could anyone please help me on this.

0 Karma
1 Solution

datasearchninja
Communicator

Note that last() and first() are dependant on the order the events arrive at the stats command, which is trypically reverse time order, so first and last probably have the opposite meaning to what you expect. Use earliest() and latest()

So:

index=main source = xyz earliest=-6mon
| stats latest(size) as "Latest", latest(_time) as "LatestTimestamp", earliest(size) as "Old", earliest(_time) as "EarliestTimestamp"

You might also want to consider the time formatting of the epoc string after this:

| eval LatestTimestampPretty=strftime(LatestTimestamp, "%Y-%m-%d %H:%M%S") 
| eval EarliestTimestampPretty=strftime(EarliestTimestamp, "%Y-%m-%d %H:%M%S") 

View solution in original post

datasearchninja
Communicator

Note that last() and first() are dependant on the order the events arrive at the stats command, which is trypically reverse time order, so first and last probably have the opposite meaning to what you expect. Use earliest() and latest()

So:

index=main source = xyz earliest=-6mon
| stats latest(size) as "Latest", latest(_time) as "LatestTimestamp", earliest(size) as "Old", earliest(_time) as "EarliestTimestamp"

You might also want to consider the time formatting of the epoc string after this:

| eval LatestTimestampPretty=strftime(LatestTimestamp, "%Y-%m-%d %H:%M%S") 
| eval EarliestTimestampPretty=strftime(EarliestTimestamp, "%Y-%m-%d %H:%M%S") 

akarivaratharaj
Communicator

Hi Colin,

Thankyou so much for the response. It is working now.

0 Karma