Splunk Search

How do I use a lookup table to resolve ips to their hosts, when some ip's have hosts and some don't, but I want to show both?

ResurgoSplunkKn
New Member

I am trying to make a pie chart with a breakdown of ip's that have been resolved to their hosts, if they have one, or to show just the ip if they don't, via the stats command and a lookup table. This is the gist of what kind of search I am running:

x | search y | stats count by z_ip

Current Result:

10.80.1.1    count
10.80.1.2   count
10.80.1.3   count
10.80.1.4   count
10.80.1.5   count

Lookup File:

ip, host
10.80.1.1,a
10.80.1.2,b
10.80.1.4,c
10.80.1.5,d

Result I want:

a                   count
b                   count
10.80.1.3   count
c                   count
d                   count

Thank you very much for your help!

0 Karma
1 Solution

elliotproebstel
Champion

I'd pair a lookup command with a coalesce like this:

your current search that ends with stats count by z_ip
| lookup your_lookup_file ip AS z_ip OUTPUT host
| eval host_id=coalesce(host, z_ip)
| stats count by host_id

View solution in original post

0 Karma

elliotproebstel
Champion

I'd pair a lookup command with a coalesce like this:

your current search that ends with stats count by z_ip
| lookup your_lookup_file ip AS z_ip OUTPUT host
| eval host_id=coalesce(host, z_ip)
| stats count by host_id
0 Karma

ResurgoSplunkKn
New Member

Thank you for your response. This does give me a result, but I only get the ip's included in the host file, not the one's that aren't. So in reference to my current example, I am getting a,b,c, and d, but not 10.80.1.3. In the case of my actual search I should be getting 16 results, but I am only getting 11, with the remaining 5 being ip's that aren't in my host file. Thanks again!

edit: For future users, from follow this answer my results gave me stats with a count of 1. To get the actual count, I had to remove 'stats count by z_ip' from my query and then I got the correct count.

0 Karma

elliotproebstel
Champion

That's odd. If you run just this part:

your current search that ends with stats count by z_ip
| lookup your_lookup_file ip AS z_ip OUTPUT host
| eval host_id=coalesce(host, z_ip)

What are you seeing there for host_id? Does every event have a value for it, and are some hostnames and others IP addresses?

0 Karma

ResurgoSplunkKn
New Member

I just figured it out, it was a small mistyping on my part. It worked! Thank you so much.

0 Karma

ResurgoSplunkKn
New Member

I celebrated too quickly, the count is now one for every single one of the stats as opposed to before where it had varying counts. I will try to troubleshoot this.

0 Karma

ResurgoSplunkKn
New Member

I found the solution was to remove stats count by z_ip from my query. I edited my comment for future users looking at this question.

0 Karma

elliotproebstel
Champion

Aha, yes. Glad that's working. You could probably get the same result by replacing the final line of my suggestion with: | stats sum(count) AS count by host_id

0 Karma

elliotproebstel
Champion

Great! I'm glad to hear it.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...