How to filter search results by lookup tables based on matching the fields and arithmatic conditions

[isamrat]

I want to filter my search results based on lookup table. But the road block here is that I want not only to match few fields from the lookup table but also I need to match some field value based on arithmetic operators like ">" , "<" etc.

Say, I have the below table as output of a search:

The Lookup table will look like below:

So, the filtered result result will look like:

Location  Company  Unit Production
 UK      IBM            56

In general the filter will be "(Location="UK" AND Company="IBM" AND Unit_Production>50) OR (Location="US" AND Company="Google" AND Unit_Production<70)"

Please help me to resolve this through splunk Lookup table.

[elliotproebstel · Accepted solution]

I'll assume that second lookup table is called my_lookup. You could meet your goals with a search structured like this:

base search that creates the first table
| search
 [| inputlookup my_lookup
  | eval search="Unit_Production".Operator.Unit_Production
  | fields Location Company search
  | format
  | rex mode=sed field=search "s/ \"([^ ]+)\" / \1 /"]

The eval line is creating a field called search, which Splunk will preserve without the field name when it passes through the format command. You can read more about that here: https://docs.splunk.com/Documentation/Splunk/7.0.2/Search/Changetheformatofsubsearchresults
The final rex command is to get rid of the quotes around "Unit_Production>50", because Splunk will treat the quoted version as a string search (looking in your logs for the literal string sequence "Unit_Production>50" rather than events with the field Unit_Production with a value greater than 50.