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]

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!

[elliotproebstel · Accepted solution]

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

[ResurgoSplunkKn]

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.

[elliotproebstel]

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?

[ResurgoSplunkKn]

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

[ResurgoSplunkKn]

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.

[ResurgoSplunkKn]

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.

[elliotproebstel]

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

[elliotproebstel]

Great! I'm glad to hear it.