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
... | stats latest(_raw) latest(temp) latest(sky) by City
Remove whatever you don't want.
... | stats latest(_raw) latest(temp) latest(sky) by City
Remove whatever you don't want.
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 😉
Additionally, dedup
goes by event order while latest()
goes by _time
order.
Thanks for the clarification. It that case I want to use stats
with last
since I want the last event (most recently indexed).
Note, latest()
goes by the _time
value extracted from the event, not by what was last indexed. Usually _time is what you want though.
Thanks! What is the difference between your suggestion and what I was able to get working:
| dedup City
| table City, Date, Temp, Sky
?
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.
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.
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