Showing the latest time per single unique value in multiple fields

[davidworsnop]

Hello, my question is linked to the below answer.
https://answers.splunk.com/answers/222406/search-to-group-by-country-city-having-count-sorte.html

I'm trying to produce a table that shows
1. City - IP Address - Last seen - Total attacks per IP Address

So far I have:
* | iplocation src | eval City=if(isnull(City) OR City="", "Unknown City", City) |stats values(src) as "IP Address", latest(_time) as "Last seen", count(src) as "Total Count" by City |convert timeformat="%d/%m/20%y %H:%M:%S" ctime("Last seen")

Problem is that this only shows one lastest(_time) per City, not the latest time each individual IP address was seen id there is more than one IP address per City.

Thanks very much in adavnce

[martin_mueller · Accepted solution]

Have you considered splitting by City IP?

Then you'll get one row per unique City-IP-combination, and thereby one latest time per combination.

[martin_mueller]

Add this to the end:

... | streamstats window=1 current=f last(City) as last_City | eval City = case(NOT City=last_City, City) | fields - last_City

That'll only keep the first occurrence per City, and drop identical ones afterwards.

[davidworsnop]

Fantastic! Thank you so much. 🐵

[davidworsnop]

Good idea! Thanks very much for the speedy response!

So I'm closer but now I have multiple values of the same City name in my first column and although its sorted alphabetically and therefore the duplicate values are grouped, it would look a bit tidier if the City name only appeared once.

*| iplocation src allfields=true | eval City=if(isnull(City) OR City="", "Unknown City", City) | rename src as "IP Address"| stats latest(_time) as "Last seen", count("IP Address") as "Total Count attacks per City" by City "IP Address" | convert timeformat="%d/%m/20%y %H:%M:%S" ctime("Last seen")