How do I query 2 sources and join results in a table

[Jonatani360]

Hello,

Say we have test01.csv and test02.csv as source files.

test01.csv is containing IP_Address and name fields
test02.csv is containing IP_Address and Name fields

I need to create a table with the following conditions: For each name events which are empty in test01.csv search the corresponding IP_Address (test01.csv) field, and search that IP_Address in test02.csv and select the Name field, which is always containing values. Then generate a table for all those empty name events and include their IPs and Name fields found.

The search I am performing is the following, but it does not work:

source="test01.csv" OR source="test02.csv" NOT name | transaction "IP Address" | table "IP Address", Name | stats by "IP Address", Name

[rjthibod · Accepted solution]

OK, I think I get it now.

Try this

source="test02.csv" [search source="test01.csv" | WHERE (isnull(Name) OR len(trim(Name))=0) | fields + "IP Address"] | stats by "IP Address", Name

[Jonatani360]

Great, this looks perfect. We are receiving around 80 events more than expected, but could be duplicated IPs... Will try to find out. But this query looks perfect, will let you know the result shortly.

Many thanks for your time and interest.

[rjthibod]

Glad it helped. Please accept my answer to indicate that it solved your problem.

[Jonatani360]

Yeah definitely, there is an exact match.

Thank you and regards

[Jonatani360]

Hi,

Modified query so splunk searches empty name fields in test01.csv only. Now results seem more accurate. However still its missing some existing empty field names... which are existing in test02.csv but not included in the results table

source="test01.csv" NOT "name=*" | join "IP Address" [search source="test02.csv"] | transaction "IP Address" | table "IP Address", Name | stats by "IP Address", Name

Thanks