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

[andrewtrobec]

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

[jkat54 · Accepted solution]

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

Remove whatever you don't want.

[jkat54]

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 😉

[martin_mueller]

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

[andrewtrobec]

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

[martin_mueller]

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]

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

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

?

[richgalloway]

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.

[andrewtrobec]

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.

[gcusello]

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