Can you help me figure out why our semi-dynamic lookup is not working?

[goken]

Hi Splunk Users,

My main search to find DHCP Discover logs is as below:

index=bluecat  (Mac_Address)  "DHCPDISCOVER"
| table _time message_option mac_address

What I am trying to achieve is I have a dashboard, so when I search the Username, it gives me all sorts of information about the Username.

My token for the username is $clientip$

I need to look up the Mac_Address from another separate CSV table called bbtable.csv and in this table, it includes the username and mac_address details.

When I run the lookup separately as below:

inputlookup bbtable.csv   | search "Username" = AVCxxxxxxxxxxx  | fields "MAC"   | dedup "MAC"

it returns the required mac_address value I need.

When I combine the sub-search with my main search as below it fails with the message "No results found. Try expanding the time range." 😞

index=bluecat [inputlookup bbtable.csv   | search "Username" = AVCxxxxxxxxxxx  | fields "MAC"   | dedup "MAC"]

| table _time message_option mac_address

Please help

Thank you in advance

[woodcock · Accepted solution]

If you add format to your subsearch and run it as a search like this:

|inputlookup bbtable.csv | search "Username" = AVCxxxxxxxxxxx | fields "MAC" | dedup "MAC" | format

You will see that it is creating something like ( ( MAC="1:2:3:4:5:6" ) OR ... OR ( MAC="5:6:7:8:9:0" ) ) which is probably not what you would like because the fieldname in your bluecoat logs is not MAC but something else. So do a | rename MAC as <other field name> or use this to do a fieldless search instead:

|inputlookup bbtable.csv | search "Username" = AVCxxxxxxxxxxx | fields "MAC" | dedup "MAC" | format "(" "" "" "" "OR" ")" | rex field=search mode=sed "s/MAC=//g"