Splunk Search

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

umichguy
Explorer

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.

0 Karma
1 Solution

jtacy
Builder

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...

View solution in original post

jtacy
Builder

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...

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...