Joining two searches on fields that do not have a common field

[leune]

I am trying to join two searches using the join command and the documentation is clear on how to do that when the two searches have a common field. Unfortunately, I don't have two fields that are called the same, but I would like to join on them. Both fields contain IP addresses, but one is the source IP and one is the destination IP, and we have created custom extraction rules to name them as such (ip_src and ip_dst)

In SQL this would be easy:

How can I reproduce something like this? Corresponding SQL syntax:

SELECT table2.mac_dst
FROM table1, table2
WHERE table1.fieldA = table2.fieldB
AND table1.fieldA = 10.73.0.0/16

So, I figured that if I use eval to rename the field in the first search, it should match the corresponding field in the second search when using a join. Each query runs fine by itself, but joining them fails. Example:

Query 1: retrieve IPS alerts
host=ips ip_src=10.73.0.0/16

Query 2: for each returned IP address, consult the DHCP logs to find the MAC address
host=dhcp process=dhcpd "via eth0"

I have verified that the appropriate field extraction rules work correctly.

Now, my problem is that in the dhcp logs, the IP address is extracted as ip_dst; in the IPS logs it is ip_src

the query
host=ips ip_src=10.73.0.0/16 |dedup ip_src |eval ip_dst=ip_src | join ip_dst [search host=dhcp process=dhcpd "via eth0"]

returns 0 results.. When running manually to test, the first query does return values, which are indeed present in the output of the second query.

I could always start changing the field extraction rules, but that's besides the point 😉

[leune]

After wrapping my head around the problem a bit more, I realized that I was going the wrong way. The correct way to solve this was by using subsearches

host=dhcp process=dhcpd [search host=ips ip=10.73.0.0/16 |dedup ip_src |eval ip_dst=ip_src |fields + ip_dst]

[yannK]

if the field exist on the 2 searches, and as the same name it should work.
so if this is not the case maybe the fields formats are not exactly identical.
check for extra white spaces, you an remove them with | eval myfield=trim(myotherfield)