Mapping syslog events with IP adresses through DHCP events

[Stefan_van_de_R]

Hi,

I'm indexing DHCP and Syslog events. To make it for the network administrators a lot easier when they have to know the physical location on a host based on the IP address, I want to make a mapping in Splunk. They both have a MAC Adress (src_mac) so it should be possible to extract the IP address out of the DHCP index.

The search query I made so far does an left join on the dhcp index but it returns a wrong IP address.

index=syslog | join type=left [search index=main sourcetype=dhcp_log src_ip != '' AND src_mac != '' earliest=-8h | sort -_time| fields + src_ip]

Can someone gives me some tips how to make it valid?

Thanks!
- Stefan

[ziegfried · Accepted solution]

You should join on a common field for both searches (the outer and the sub-search). Here's an example that would work if both searches provide the src_ip field. If the fields have different names you can either use a FIELDALIAS (in props.conf) or use eval or rename to normalize it.

index=syslog | join src_ip usetime=true ealier=true [ search index=main sourcetype=dhcp_log src_ip=* src_mac=* | fields _time src_ip src_mac ]

Additionally I added the usetime modifier for the join command as this probably makes sense for this kind of use-case.

[Stefan_van_de_R]

Thanks Ziegfried for your fast response!
With the query showed below Splunk makes a correct mapping altough I am going to try as well to use Lookups with CSV files to map the different events as the subsearch has a bad influence on the loading performance.

index=syslog | join src_mac usetime=true earlier=true [ search index=main sourcetype=dhcp_log src_ip= src_mac= | fields _time src_ip src_mac ]