Splunk Search

Match records based on a WHERE condition defined in a lookup table

edoardo_vicendo
Communicator

Hello,

Suppose I have raw records like this:

 

user=blabla,org_L1=12345,org_L2=777,department=7890
user=testtt,org_L1=34567,org_L2=999,department=8910
...

 

And I would like to extract the records based on the rules defined in a lookup:

 

where_condition,role
org_L1=12345 AND org_L2=777,superuser
org_L1=34567 OR org_L2=999,normaluser

 

Is it feasible in some way to introduce into the SPL statement a "where condition" based on the where_condition field defined in the lookup?

searchmatch command could be used but it does not accept fields, it only accepts strings.

Thanks a lot,

Edoardo

Labels (2)
0 Karma

ITWhisperer
Ultra Champion

It is not clear what you want to do with the lookup table in terms of matching records, but if it is to be able to select, say, superusers, based on the criteria in the lookup table, you could use the lookup table to populate a dropdown, where the value is the where_condition and the label is role, then you can use the token from the dropdown as the where clause for a search to populate a panel.

    <input type="dropdown" token="condition">
      <label>field1</label>
      <fieldForLabel>role</fieldForLabel>
      <fieldForValue>where_condition</fieldForValue>
      <search>
        <query>| makeresults
| eval _raw="where_condition,role
org_L1=12345 AND org_L2=777,superuser
org_L1=34567 OR org_L2=999,normaluser"
| multikv forceheader=1
| fields - _* linecount</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
    </input>

Replace the query with inputlookup of your lookup table

    <panel depends="$condition$">
      <table>
        <search>
          <query>| makeresults
| eval _raw="user=blabla,org_L1=12345,org_L2=777,department=7890
user=testtt,org_L1=34567,org_L2=999,department=8910"
| multikv noheader=t 
| extract
| fields - _* linecount Column_*
| where $condition$</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>

Replace the search with your search and add the where clause specifying the token.

I put the depends attribute on the panel so it only shows if a selection from the dropdown is made. An alternative is that you include "where " as a prefix in the dropdown and then just have the token at the end of the panel search so the filter only gets applied when something is selected from the dropdown.

0 Karma

maciep
Champion

not sure if you need to actually use where for your scenario, but if the fields are already extracted, then a subsearch might be better/easier?

 

index=<your_index> [|inputlookup <your_lookup> | eval search=where_condition]

 

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!