How to perform a join on a field from source1 to one or another field in source2 based on the source1 field value?

[splunkin11]

I'm trying to join information from a metadata search to a lookup file. It works using a subsearch such as this:

| metadata type=hosts index= | join type=left host [|inputlookup myfile.csv |rename fieldx as host]

My metadata host field may contain an IP or a name. My lookup file contains two fields - one with the IP and another with the hostname. Can I join the two files so that if the metadata host field is an IP it joins the lookup file based on the IP field and when the metadata host field contains a hostname it joins based on the hostname field?

I can do this by running two different searches - one join for IP and one join for hostname and combine the two results in a dashboard, but I was wondering if I could join the records based on either value of the host field in one search.

Any input is greatly appreciated!

[somesoni2 · Accepted solution]

Try like this. Assuming your lookup has fields host, IP, field1, field2... where field1, field2 are the fields that you want to return.

| metadata type=hosts index=* 
| lookup myfile.csv  hostfield as host OUTPUT field1 as field1_host field2 as field2_host 
| lookup myfile.csv  IPfield  as host OUTPUT field1 as field1_IP field2 as field2_IP
| eval field1=coalesce(field1_host, field1_IP) 
| eval field2=coalesce(field2_host, field2_IP) 
| table host field1 field2

[splunkin11]

Works great - thanks!

[somesoni2]

Another crude method (not recommended as joins are expensive and here it can be avoided, putting here just for reference).

| metadata type=hosts index=* | join type=left host [|inputlookup myfile.csv |eval host=mvappend(hostfield,IPfield) | mvexpand host ]

[cmerriman]

Could you try |join type=left host IP ?

[somesoni2]

This will not work as the metadata command result doesn't have both the fields (host and IP)