Can the eval case function be used in conjunction with lookup tables?

[aaloisi]

Hello,
I am fairly new to Splunk and was wondering if the eval case function could be used in conjunction with lookup tables. Here is my current problem (if there are other solutions I am open to suggestions)

I have 2 message types (100 and 200) each having a separate set of debug codes associated with them. So I am using lookup tables to expand the fields based on the message type and its corresponding field definitions. As far as I can tell, I cannot do this:

| lookup msg100_debug_codes.csv Code100 as DebugCode
| lookup msg200_debug_codes.csv Code200 as DebugCode

The second lookup table overrides what is in the first table. Also, the list of debug codes for each message type have overlapping numbers, which is why I cannot use one master lookup table since there could be 2 of the same (key, value) pairs. This is what lead me to the case statement. Can I use Case to direct which lookup table to use? I am not sure if this is possible. Thank you in advance and if I can clarify any details please let me know.

[woodcock · Accepted solution]

Like this:

... | lookup msg100_debug_codes.csv Code100 AS DebugCode OUTPUT commonField AS commonFIeld100
| lookup msg200_debug_codes.csv Code200 AS DebugCode OUTPUT commonFIeld AS commonField200
| eval commonField = coalesce(commonField100, commonField200)

[aaloisi]

If I understand this correctly @woodcock, your solution would allow me to display all of the common fields in one table but being decoded using the correct lookup tables?

[woodcock]

Correct. Because otherwise, only the LAST one would match and any that did not match in the last lookup would null out previous matches. This way you capture every match and condense them back to a single value at the end.

[anwarmian]

This part is very important
"any that did not match in the last lookup would null out previous matches."

[manjunathmeti]

You can use lookup command with OUTPUT:

 | lookup msg100_debug_codes.csv Code100 AS DebugCode OUTPUT field1 AS field100_1, field2 AS field100_2
 | lookup msg200_debug_codes.csv Code200 AS DebugCode OUTPUT field1 AS field200_1, field2 AS field200_2

[sumanssah]

Not sure if I understood correctly if you want to join both files to 1, please try this

| lookup msg100_debug_codes.csv | rename  Code100 as DebugCode
 | append 
[| lookup msg200_debug_codes.csv 
| rename Code200 as DebugCode]

[aaloisi]

Let me explain a bit more:

So I have message 100 which lets say has 10 debug codes associated with it. I am using the lookup table to add fields to my query to give the debug codes context. For example message 100, debug code 10 means the light is off. I am doing the same thing with message 200 and I am using 2 different lookup tables to "decode" the context of the debug messages.

Where I am running into an issue is when try to include both 100 and 200 messages in my query. When I try to use successive lookup tables in my query (like in my original question) the second lookup table removed the context from the first. So any context decoded from message 100 are overridden but the message 200 lookup table.