How to replace values outputted from a stats with lookup table values?

[dritjon]

I've done a simple search like this:

index=fw_cisco | stats dc(dest_ip) as NrDestIp by src_ip

I have defined a lookup file (ip_lookup) which has two colums: IPHost and DNShost. How do I replace the values of src_ip with the corresponding values of the lookup table?

I tried this

index=fw_cisco | lookup ip_lookup IPHost as src_ip OUTPUT DNSHost as resolved_src | stats dc(dest_ip) as NrDestIp by src_ip, resolved_src

But it creates two columns, and also misses the values of src_ip that dont have a matching IPHost in the lookup table.

[ITWhisperer · Accepted solution]

index=fw_cisco 
| lookup ip_lookup IPHost as src_ip OUTPUT DNSHost as resolved_src 
| eval resolved_src=coalesce(resolved_src, src_ip)
| stats dc(dest_ip) as NrDestIp by src_ip, resolved_src