How to use values in lookup table not as fields but as search strings?

[christopheryu]

Using lookup table to search events but having some issues:

|inputlookup router_lookup | rename Router_Name as DEVICE_NAME Router_Interface as INTERFACE | fields DEVICE_NAME, INTERFACE | format

results to:

( ( DEVICE_NAME="ROUTERA" AND INTERFACE="xe-5/2/0" ) OR ( DEVICE_NAME="ROUTERB" AND INTERFACE="xe-9/3/1" ) OR ( DEVICE_NAME="ROUTERC" AND INTERFACE="xe-6/7/0" ) ... etc

However, I found out that DEVICE_NAME is not a defined field for all routers, so I tried doing this:

|inputlookup router_lookup | rename Router_Name as DEVICE_NAME Router_Interface as INTERFACE | eval Interface_Name=DEVICE_NAME." AND ".INTERFACE | fields Interface_Name | rename Interface_Name as query | format

results to:

( ("ROUTERA AND xe-5/2/0" ) OR ( "ROUTERB AND xe-9/3/1" ) OR ( "ROUTERC AND xe-6/7/0" ) ... etc

this is NOT the result I was looking for since they have quotation marks.

this is what I need:

( (ROUTERA AND xe-5/2/0 ) OR (ROUTERB AND xe-9/3/1 ) OR (ROUTERC AND xe-6/7/0) ... etc

thank you in advance!

[woodcock · Accepted solution]

Like this:

|inputlookup router_lookup | rename Router_Name as DEVICE_NAME Router_Interface as INTERFACE | eval Interface_Name=DEVICE_NAME." AND ".INTERFACE | fields Interface_Name | rename Interface_Name as query | format | rex field=search mode=sed "s/\"//g"

[christopheryu]

That worked, thank you!

[jkat54]

Try this with your second search:

| return $query | format

But then simplify, you've got renames and such tht are really unnecessary.

|inputlookup router_lookup
| eval query=Router_Name." AND ".Router_Interface
| fields query
| return $query
| format

[christopheryu]

Thank you for the response. Here is what I got using your suggestion:

( ("ROUTERA AND xe-5/2/0" ))

Returned only one item and did not remove the quotation marks. Removing the "| return $query" yields the same result as my second search.