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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...