How to display iplocation info for all values in the IP address field belonging to a specific category in Splunk?

[umichguy]

So my search query gives me the IP addresses pertaining to a user field in the following manner:

index=abc | stats values(src_ip) by username | where username!="xyz" | iplocation values(ipaddress)

But the problem is that if a username has more than 1 IP address associated with it, it is skipped over in iplocation, that is, we have no resolution for IP to location mapping in those instances. For example:

username values(ipaddress) city country
abcxyz 123.456.78.90 amazingcity amazingcountry
xyzabc 122.333.444.56
234.456.333.444

As you can see, the second user has multiple IP addresses associated with it and so iplocation just skips over it. How can I modify this query so that it resolves IP address for users with multiple IP addresses. Additionally, I would like to sort the results such that the users with the most IP addresses associated with them show up first.

[jtacy · Accepted solution]

Well, eventstats will let you keep track of how many unique IPs are seen per username so you can sort on that:

index=abc username!="xyz" | eventstats dc(src_ip) AS ip_count by username | dedup username,src_ip | table username,src_ip,ip_count | sort -ip_count,-username | iplocation src_ip

The problem is that you're not going to get the nice cell merging effect that stats values() gives you; mvcombine can sometimes help out here but I'm not sure it will work in this case. This comment might help if you really only want to see the same username once in your results: https://answers.splunk.com/answers/25102/question-regarding-grouping-of-results-into-a-table.html#co...