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 😉
... View more