topic How to force the sequence of search-time operations to perform calculated fields AFTER lookups ? in Splunk Search

How to force the sequence of search-time operations to perform calculated fields AFTER lookups ?

dhtran — Wed, 30 Sep 2020 04:56:01 GMT

Hello,

I try to figure out how to perform fields calculation based on rules coming from a lookup table.

This is my use case :
- I have event data coming in plain text format that are ingested into Splunk in "generic_single_line" format
- I have configured props.conf to extract fields using regular expression
- I have configured lookup table to enrich the event data (code -> label, etc..)

Now, there's a field that needs to be populated from values extracted from the source and by applying rule defined in the lookup table. Is it possible ?

Example, my lookup table looks like this :

code, type, key_fields
001, E, field1
002, E, field1 + field2
003, R, field1 + field3 + field4
...etc

I need to somehow created an output new field called "unique_key" which is the value or the concatenated values defined in the lookup table based on the code value.

Thanks in advance for your help.

Re: How to force the sequence of search-time operations to perform calculated fields AFTER lookups ?

DalJeanis — Fri, 10 Apr 2020 20:14:00 GMT

There is no way to change the order of operations exactly the way you asked. However, there are undoubtedly several ways to create the effect you are looking for.

1) Are there a limited number of potential fields involved?

2) Is the order of those fields consistent, when they are used?

If the answer to the above are both yes, then you could do this

LOOKUP LAYOUT

code  type usefield1 usefield2 usefield3 usefield4
  001   E     Y        N        N        N
  002   E     Y        Y        N        N    
  003   R     Y        N        Y        Y

your search here
| rename COMMENT as "add the lookup fields that say whether to use each field" 
| lookup mylookup.csv code OUTPUT usefield1 usefield2 usefield3 usefield4

| rename COMMENT as "create an empty field and then append field values from each chosen field" 
| eval newkey= ""
| foreach use* [ | eval newkey = mvappend(newkey,case(<<FIELD>>="Y",<<MATCHSTR>>))]

| rename COMMENT as "flatten the field and kill any spaces between" 
| mvcombine delim="" newkey

| rename COMMENT as "get rid of unneeded fields" 
| fields - use*

There are several other ways you could build it, but that would work pretty well.

The above code expect that for every field name, the relevant column of the lookup table will be use followed by the exact field name.

Re: How to force the sequence of search-time operations to perform calculated fields AFTER lookups ?

dhtran — Wed, 30 Sep 2020 04:57:02 GMT

Hi @DalJeanis

Thank you for your interesting answer, and sorry for my late feedback (it was holiday in France yesterday)

I realised by reading your solution that even it's really a smart one it doesn't provide the desired result because I wasn't clear in my explanation.

Allow me to add an example to illustrate what I want to achieve

Imagine the following event data
001EAAABBBCCCXXX
002EDDDEEEFFFYYY
003RGGGHHHKKKZZZ

Then, with extracting rules from props.conf I will have output fields of my event like this
code = 001,type = E, field1 = AAA, field2 = BBB, field3 = CCC, field 4 = XXX
code = 002,type = E, field1 = DDD, field2 = EEE, field3 = FFF, field 4 = YYY
code = 003,type = R, field1 = GGG, field2 = HHH, field3 = KKK, field 4 = ZZZ
and so on...

The desired result would be something like this
code = 001,type = E, field1 = AAA, field2 = BBB, field3 = CCC, field4 = XXX, unique_key=AAA
code = 002,type = E, field1 = DDD, field2 = EEE, field3 = FFF, field4 = YYY, unique_key=DDDEEE
code = 003,type = R, field1 = GGG, field2 = HHH, field3 = KKK, field4 = ZZZ, unique_key=GGGKKKZZZ
...etc

This is what I meant by "applying extracting rule defined in the lookup table"
code, type, key_fields
001, E, field1
002, E, field1 + field2
003, R, field1 + field3 + field4

Some additional details if it could help
- there is approx. 50 fields in total, but the ones which is potentially needed to form the "unique_key" don't exceed 10 (your first question above)
- the order has to be exactly the same as configured in props.conf (or in something else) (your 2nd question)
- values from the fields used to created "unique_key" could not be null or blank