Splunk Search

Check field against two lookup tables

mkarimi17
Path Finder

I have two lookup tables that may contain the hostname of an IP address

| lookup cmdb_ci_server_lookup ip_address as src output fqdn as orig_host
| lookup lansweeper_assets_lookup IPAddress as src output AssetName as orig_host

However, if AssetName in lansweeper_assets_lookup is null, the output of orig_host will be null as well. I know I can assign them different names and do an eval to combine but was hoping to get a more elegant way. any ideas?

0 Karma
1 Solution

mkarimi17
Path Finder

thanks but this is basically what I had and it's super long. outputnew did the trick!

View solution in original post

0 Karma

mkarimi17
Path Finder

thanks but this is basically what I had and it's super long. outputnew did the trick!

0 Karma

woodcock
Esteemed Legend

Click Accept on your answer and the UpVote anything else that was useful.

0 Karma

cmerriman
Super Champion

If you're just trying to bring in hostname and not combine the tables together, I'd use coalesce. I'd just do something like

|join src type=left [|inputlookup cmdb_ci_server_lookup|fields ip_address fqdn|rename ip_address as src] 
|join src type=left [|inputlookup lansweeper_assets_lookup |fields IPAddress AssetName|rename IPAddress as src]
|eval orig_host=coalesce(fqdn,AssetName)
0 Karma

somesoni2
Revered Legend

Which value takes precedence, once from lansweeper_assets_lookup OR one from cmdb_ci_server_lookup (assuming both returns value)?

0 Karma

mkarimi17
Path Finder

both should have the same data, so it doesn't really matter to me. but sometimes one is null and the other isn't

0 Karma

somesoni2
Revered Legend

Cool. So in your lookup command, you're using OUTPUT option to get orig_host, replace it with OUTPUTNEW.

If the OUTPUT clause is specified, the output lookup fields overwrite existing fields. If the OUTPUTNEW clause is specified, the lookup is not performed for events in which the output fields already exist.

So your queries will be like this

| lookup cmdb_ci_server_lookup ip_address as src outputnew fqdn as orig_host
| lookup lansweeper_assets_lookup IPAddress as src outputnew AssetName as orig_host

mkarimi17
Path Finder

ah! totally forgot about outputnew instead of output.

0 Karma

cmerriman
Super Champion

what exactly is the goal? to join cmdb_ci_server_lookup to lansweeper_assets_lookup by ip_address/IPAddress and fill the null values of AssetName/orig_host from lansweeper_assets_lookup?

0 Karma

mkarimi17
Path Finder

the goal is to get the hostname from either lookup table. not looking to combine them, although maybe I should just do that in a different search

0 Karma
Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...