Splunk Search

How can I get the latest event by a specific field?

Builder

Hello,

I have the following event data:

City,Date,Temp,Sky
New York,2016-11-10,20,Clear
New York,2016-11-10-19,Cloudy
San Francisco,2016-11-20,20,Clear
San Francisco,2016-11-19,18,Rain
Rome,2016-11-20,11,Rain
Rome,2016-11-19,10,Partly Cloudy

What I would like to do is get the latest event for each city, so I have results:

City,Date,Temp,Sky
New York,2016-11-10,20,Clear
San Francisco,2016-11-20,20,Clear
Rome,2016-11-20,11,Rain

I have tried

stats first(City) by City

But this just gives me a list that I cannot use. Any help would be great!

Thank you!

Andrew

1 Solution

SplunkTrust
SplunkTrust

... | stats latest(_raw) latest(temp) latest(sky) by City

Remove whatever you don't want.

View solution in original post

SplunkTrust
SplunkTrust

... | stats latest(_raw) latest(temp) latest(sky) by City

Remove whatever you don't want.

View solution in original post

SplunkTrust
SplunkTrust

Also note that first and last can be manipulated using sort prior to the stats command and therefore they are not meant for use when you want the latest or earliest event. You really do want to insure your timeStamping is good and then use latest(). That's my final answer 😉

0 Karma

SplunkTrust
SplunkTrust

Additionally, dedup goes by event order while latest() goes by _time order.

Builder

Thanks for the clarification. It that case I want to use stats with last since I want the last event (most recently indexed).

0 Karma

SplunkTrust
SplunkTrust

Note, latest() goes by the _time value extracted from the event, not by what was last indexed. Usually _time is what you want though.

Builder

Thanks! What is the difference between your suggestion and what I was able to get working:

| dedup City
| table City, Date, Temp, Sky

?

0 Karma

SplunkTrust
SplunkTrust

The dedup command passes all fields to the next command whereas the stats command only passes the stats it calculates and the fields following the by keyword.

---
If this reply helps you, an upvote would be appreciated.

Builder

Thanks, that makes sense. In my case I don't want all fields to pass on, so I suppose it's more efficient to use the stats command.

0 Karma

SplunkTrust
SplunkTrust

Hi andrewtrobec,
you have to try with

stats latest(City) by City

http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/CommonStatsFunctions

Bye.
Giuseppe

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!