Can you help me with a subsearch across two indexes?

[smoig]

Hi Folks,

I'm using Splunk version 4.0 (with App verion 6.6.1) and I'm pretty new to Splunk — I've been using it for about 2 years. This is the first time I've had to dive into a subsearch across 2 indexes for a desired result. Essentially, we have a network registration log and a DHCP log that are both in separate indexes. I need to match a (successful) mac address registration to a leased IP address. Things get funky because the registration index has a mac address formatting with no colons, but the DHCP index does have colons in the formatting:

Registration log (this log tags this as "mac_address"): XXXXXXXXXXXX
DHCP log (this log tags it as "mac"): XX:XX:XX:XX:XX:XX

So in order for the search to work, I'm pretty sure an eval is needed to change the formatting to search the DHCP log. The goal of this search is to match a mac address ("indexA") and return an IP address ("indexB"). There's also a vendor plugin we use that tells us who the manufacturer is, based on the mac address.

Here is my attempt:

index=indexA sourcetype=Syslog service_name="registered" | dedup mac_address | fields + mac_address | eval mac=replace(mac_address, "(\w{2})(\w{2})(\w{2})(\w{2})(\w{2})(\w{2})", "\1:\2:\3:\4:\5:\6") | append [search index=indexB (mac) "DHCPACK"]  | lookup macvendor mac | stats count by mac,vendor,ip

This search is pretty close to working, but I'm suspecting that there's something in the eval (or maybe where it's placed in the search string) that is preventing the search to give me the result I'm looking for. Looking for any help/advice/corrections.

Thanks for your time, folks.

[tom_frotscher · Accepted solution]

Hi,

you can use your first search as a filter to your second search. Splunk will automatically return the results for the first search as field=value1 OR field=value2 OR ...

So you can rearange your search like this:

index=indexB [index=indexA sourcetype=Syslog service_name="registered" | dedup mac_address | fields + mac_address | eval mac=replace(mac_address, "(\w{2})(\w{2})(\w{2})(\w{2})(\w{2})(\w{2})", "\1:\2:\3:\4:\5:\6") | table mac] "DHCPACK" | lookup macvendor mac | stats count by mac,vendor,ip

So your second index will only be searched for the mac adresses matching the result of the subsearch. You can see what splunk is doing with the subsearch if you take a look at the job manager after the search has finished.

Greetings,

Tom

[smoig]

Tom - thanks so much for your answer - it got me exactly what I was looking for. I had a feeling that the issue was the placement of the subsearch. Thanks again.