topic Re: Lookup not showing results in Splunk Search

Why is lookup not showing results?

TolTest — Thu, 15 Jun 2023 23:00:37 GMT

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.

Re: Lookup not showing results

ITWhisperer — Thu, 15 Jun 2023 07:27:13 GMT

Try changing this line

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

Re: Lookup not showing results

TolTest — Thu, 15 Jun 2023 07:29:32 GMT

errorCode and errorAlertValue are both still empty.

Re: Lookup not showing results

ITWhisperer — Thu, 15 Jun 2023 07:41:07 GMT

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?

Re: Lookup not showing results

TolTest — Thu, 15 Jun 2023 07:51:44 GMT

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.

Re: Lookup not showing results

ITWhisperer — Thu, 15 Jun 2023 08:15:34 GMT

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!

Re: Lookup not showing results

TolTest — Thu, 15 Jun 2023 08:21:26 GMT

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?

Re: Lookup not showing results

ITWhisperer — Thu, 15 Jun 2023 08:33:11 GMT

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.

Re: Lookup not showing results

TolTest — Thu, 15 Jun 2023 08:43:04 GMT

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..

Re: Lookup not showing results

ITWhisperer — Thu, 15 Jun 2023 08:45:02 GMT

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

Re: Lookup not showing results

ITWhisperer — Thu, 15 Jun 2023 10:44:02 GMT

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