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

umichguy — Thu, 23 Jun 2016 21:09:56 GMT

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.

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

jtacy — Thu, 23 Jun 2016 23:36:04 GMT

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#comment-25144

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

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

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