Splunk Search

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

isamrat
Explorer

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:

alt text

The Lookup table will look like below:
alt text

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.

0 Karma
1 Solution

elliotproebstel
Champion

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.

View solution in original post

0 Karma

elliotproebstel
Champion

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.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...