Splunk Search

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

andrewtrobec
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

jkat54
SplunkTrust
SplunkTrust

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

Remove whatever you don't want.

View solution in original post

jkat54
SplunkTrust
SplunkTrust

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

Remove whatever you don't want.

View solution in original post

jkat54
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

martin_mueller
SplunkTrust
SplunkTrust

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

andrewtrobec
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

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

andrewtrobec
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

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

andrewtrobec
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

gcusello
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
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!