Why is lookup not showing results?

[TolTest]

Hey all,

Does anyone know why this isn't working (I'm a new Splunk user)? I'm trying to show the errorMessageFilter, errorCode and errorAlertValue from the CSV file in the table.

CSV file:

errorMessage,errorMessageFilter,errorCode,errorAlertValue
Test1,Value1,1,10
Test2,Value2,2,10
Test3,Value3,3,100
Test4,Value4,5,100
Test5,Value5,5,100

And this is my query so far:

index = index1
cf_app_name = app1
[ | inputlookup critical_errors.csv | table errorMessageFilter | rename errorMessageFilter as msg | format ] AND NOT NULL
| lookup critical_errors.csv errorMessageFilter OUTPUT errorCode, errorAlertValue
| eval time = strftime(timestamp/1000000000,"%a %b %d, %Y, %T")
| table time, cf_space_name, cf_app_name, msg, errorCode, errorAlertValue

Everything in the table results shows fine, with the exception of errorCode and errorAlertValue. These 2 are empty. 

[ITWhisperer]

Try changing this line

| lookup critical_errors.csv errorMessageFilter as msg OUTPUT errorCode, errorAlertValue

[TolTest]

errorCode and errorAlertValue are both still empty.

[ITWhisperer]

So the filter [| inputlookup ...] works giving events with msg field with exactly the same values as in the errorMessageFilter column of the csv, yet the lookup with the msg field against the errorMessageFilter finds no matches?

Please can you give a real (although anonymised) example of an event and the line from the csv demonstrating this?

[TolTest]

CSV File:

errorMessage,errorMessageFilter,errorCode,errorAlertValue
Test1,Value1,1,10
Test2,Value2,2,10
Test3,Value3,3,100
Test4,Value4,5,100
Test5,Value5,5,100

Query:

index = index1
cf_app_name = app1
[ | inputlookup critical_errors.csv | table errorMessageFilter | rename errorMessageFilter as msg | format ] AND NOT NULL
| lookup critical_errors.csv errorMessageFilter AS msg OUTPUT errorCode, errorAlertValue
| eval time = strftime(timestamp/1000000000,"%a %b %d, %Y, %T")
| table time, cf_space_name, cf_app_name, msg, errorCode, errorAlertValue

Result event:

time                        | cf_space_name | cf_app_name | msg    | errorCode | errorAlertValue
Wed Jun 14, 2023, 18:01:38  | space1        | app1        | Value1 |           |

I hope this is what you meant.

[ITWhisperer]

Since you seem unwilling to provide real data, here is a runanywhere example showing it working. Note that it uses outputlookup to set up critical_errors.csv so you may want to use a different name!

| makeresults
| fields - _time
| eval _raw="errorMessage,errorMessageFilter,errorCode,errorAlertValue
Test1,Value1,1,10
Test2,Value2,2,10
Test3,Value3,3,100
Test4,Value4,5,100
Test5,Value5,5,100"
| multikv forceheader=1 
| table errorMessage,errorMessageFilter,errorCode,errorAlertValue
| outputlookup critical_errors.csv
| fields - errorMessage,errorMessageFilter,errorCode,errorAlertValue
| head 1
| eval _raw="time                        | cf_space_name | cf_app_name | msg
Wed Jun 14, 2023, 18:01:38  | space1        | app1        | Value1"
| multikv forceheader=1
| table time cf_space_name cf_app_name msg
| search 
    [| inputlookup critical_errors.csv 
    | rename errorMessageFilter as msg
    | table msg
    | format]
| lookup critical_errors.csv errorMessageFilter as msg OUTPUT errorCode,errorAlertValue

[TolTest]

I'm still trying to figure all this out. But I did notice something.

If I use the following query:

| inputlookup critical_errors.csv | table errorMessageFilter | rename errorMessageFilter as msg
| lookup critical_errors.csv errorMessageFilter as msg OUTPUT errorCode
| table msg errorCode

It works fine. It shows me the msg and corresponding errorCode as it should. 
However, whenever I add a | format to the same query:

| inputlookup critical_errors.csv | table errorMessageFilter | rename errorMessageFilter as msg | format
| lookup critical_errors.csv errorMessageFilter as msg OUTPUT errorCode
| table msg errorCode

 It no longer works. Could it be because of the | format maybe?

[ITWhisperer]

You only need format if you are using the subsearch as a filter to a search command, and even then, it isn't always required (it depends on your data and what you are trying to do with the subsearch).

The format parameters allow you to mess with the delimiters, around the values, around the field/value pairs, between the field/value pairs from the same line, and between the values from different lines, etc.

Try removing it.

[TolTest]

I use it to filter out my results, yes. And removing it from the original query seems to make no difference. 😞 I'm so clueless..

[ITWhisperer]

Perhaps it is time to share your actual events and csv because I have demonstrated the technique working with dummy data?

[ITWhisperer · Accepted solution]

For wildcard lookups, you need to use advanced options and set WILDCARD matching for the lookup field