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
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.
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.
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.
Fantastic! Thank you so much. 🐵
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")