splunk search lookup help

[MVK1]

Hello,

I have a splunk query returning my search results

index="demo1" source="demo2"
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}"
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:"
| search test_field_name=test_field_name_1
| table _raw id_num
| reverse
| filldown id_num

From above table  _raw may have *fail_msg1* or *fail_msg2*

I have created a lookup file sample.csv with the following content

Product,Feature,FailureMsg
ABC,DEF,fail_msg1
ABC,DEF,fail_msg2

I want to search if FailureMsg field (fail_msg1 OR fail_msg2) is found in _raw of my splunk query search results and return only those matching lines. If they (fail_msg1 OR fail_msg2) are not found, return nothing

Could you please share how to write lookup or inputlookup for fetching these results? If those 

[yuanliu · Accepted solution]

---

I want to search if FailureMsg field (fail_msg1 OR fail_msg2) is found in _raw of my splunk query search results and return only those matching lines. If they (fail_msg1 OR fail_msg2) are not found, return nothing

I think this sentence is confusing everybody:-). Is it correct to say that

1. FailureMsg already exists in raw event search, and
2. you only want events matching one of FailureMsg values in your lookup?

If the above are true, you have a simple formula

index="demo1" source="demo2"
    [inputlookup sample.csv
    | fields FailureMsg]

Put back into your sample code and incorporating the correction from @isoutamo, you get

index="demo1" source="demo2"
    [inputlookup timelookup.csv
    | fields FailureMsg]
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}"
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:"
| search test_field_name="test_field_name_1"
| table _raw id_num
| reverse
| filldown id_num

[MVK1]

@yuanliu Thank you for your reply . The following block works for me when run independently .

index="demo1" source="demo2"
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}"
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:"
| search test_field_name="test_field_name_1"
| table _raw id_num
| reverse
| filldown id_num

and this query works

| inputlookup sample.csv | fields FailureMsg

but this block does not work for me 

index="demo1" source="demo2"
    [inputlookup sample.csv
    | fields FailureMsg]

 Tried this block as well, it did not work for me 

index="demo1" source="demo2"
    [ | inputlookup sample.csv
    | fields FailureMsg ]

Since above query did not work, entire block you suggested did not work as well 

index="demo1" source="demo2"
    [inputlookup sample.csv
    | fields FailureMsg]
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}"
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:"
| search test_field_name=test_field_name_1
| table _raw id_num
| reverse
| filldown id_num

This query works for me when I search for fail_msg1 or fail_msg2

index="demo1" source="demo2" ("fail_msg1" OR "fail_msg2")

any idea how to search this using inputlookup or lookup?

[yuanliu]

First, please do not use phrases like "does not work" because it conveys little information in the best scenario.  There are many ways a search "does not work".  There could be an error message.  There could be no error, and no output.  There could be output, but not what you expected. And so on and so on.

I assume that what you meant was that the search gave no output.  The problem, then, is that your raw events do NOT have a field named FailureMsg as your OP implied. (I tried to clarify in my previous response.) The fact that index="demo1" source="demo2" ("fail_msg1" OR "fail_msg2") returns results only means that the terms "fail_msg1", "fail_msg2" exist in some events; you need to be explicit about what fields are available at search time.

If you do not have a suitable field name in raw events to limit the search, subsearch can still be used to match straight terms by using a pseudo keyword search.

index="demo1" source="demo2"
    [inputlookup sample.csv
    | fields FailureMsg
    | rename FailureMsg AS search
    | format]
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}"
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:"
| search test_field_name=test_field_name_1
| table _raw id_num
| reverse
| filldown id_num

[MVK1]

@yuanliu Thank you for your response again. Apologies for my wording if it created any confusion. I will be more careful going forward. You're right, I meant my search did not return any results in my context.  This query returned my matching search results events . I noticed that id_num field in the search results was blank as I was using filldown to populate id_num fields

index="demo1" source="demo2" 
[inputlookup sample.csv | fields FailureMsg | rename FailureMsg AS search | format] 
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}" 
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:" 
| search test_field_name="test_field_name_1"
| table _raw id_num 
| reverse 
| filldown id_num

I moved lookup at the end after filldown and I see id_num field as well in search results table

index="demo1" source="demo2" 
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}" 
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:" 
| search test_field_name="test_field_name_1"
| table _raw id_num 
| reverse 
| filldown id_num
[inputlookup sample.csv | fields FailureMsg | rename FailureMsg AS search | format] 

[yuanliu]

Let's not confound different matters.  The original problem has nothing to do with id_num, filldown, or any other subject.  No other data characteristics were described.  The only information about data is filter ( fail_msg1 OR fail_msg2).  Let's focus on this and raise a separate question about id_num.

The big question about the search is: Does this pick the correct events?

index="demo1" source="demo2"
    [inputlookup sample.csv
    | fields FailureMsg
    | rename FailureMsg AS search
    | format]

To help you answer this, edit your sample.csv to ONLY include fail_msg1 and fail_msg2.    Use this lookup to run the search in a fixed interval, e.g., earliest=-1d@d latest=-0d@d.  Then, run the other search in the same fixed interval:

index="demo1" source="demo2" ("fail_msg1" OR "fail_msg2")

Do you get the same events?  In fact, run a third test in the same interval (as long as you run all searches within the same @Day).

index="demo1" source="demo2"
    [makeresults format=csv data="FailureMsg
fail_msg1
fail_msg2"
    | rename FailureMsg AS search
    | format]

If you get the same events from all three, and your id_num is blank, you should look at the events themselves to find why your regex won't work.  In other words.  Because the inputlookup subsearch has no way to influence any operation after events are returned.

We can discuss further if ("fail_msg1" OR "fail_msg2") gives drastically different events from the other two.  In that case, you will need to show raw events returned from each and explain what differences are between two groups of events. (Anonymize as necessary.)

Here is a look at why I am suggesting these tests.  Just take the kernel of those two subsearches without index search:

| inputlookup sample.csv
| fields FailureMsg
| rename FailureMsg AS search
| format

and

| makeresults format=csv data="FailureMsg
fail_msg1
fail_msg2"
| rename FailureMsg AS search
| format

Both will give you

search

( ( fail_msg1 ) OR ( fail_msg2 ) )

This is why I am confident that the subsearches are identical to ("fail_msg1" OR "fail_msg2").

[MVK1]

@yuanliu Thanks again for your detailed explanation. Apologies, I should have asked id_num as a follow-up question and not related to this main question.  Instead of using filldown to populate id_num, I extracted id_num and included as part of fields for every payload upload to Splunk. I have updated to the following query and it worked

index="demo1" source="demo2"
    [inputlookup sample.csv
    | fields FailureMsg
    | rename FailureMsg AS search
    | format ]
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:"
| search test_field_name="test_field_name_1"
| table _raw id_num

Thanks again for your detailed analysis and guidance in helping solve this. 

[MVK1]

@yuanliu apologies my bad - moving inputlookup at the end is returning all results (NOT just search results)

index="demo1" source="demo2" 
| rex field=_raw "id_num \{ data: (?P<id_num>\d+) \}" 
| rex field=_raw "test_field_name=(?P<test_field_name>.+)]:" 
| search test_field_name="test_field_name_1"
| table _raw id_num 
| reverse 
| filldown id_num
[inputlookup sample.csv | fields FailureMsg | rename FailureMsg AS search | format] 

 Could you please help ?

[gcusello]

Hi @MVK1 ,

you can create your lookup using the Splunk Lookup Editor App (https://splunkbase.splunk.com/app/1724).

Then you have to create your lookup definition [Settings > Lookups > Lookup Definitions > Create New Definition]; in this job put attention to the other properties, if you don't want that the lookup is case sensitive.

Then you can manually populate this lookup using the Lookup Editor or schedule a search to extract the FailureMsgs and store in the lookup using the outputlookup command (https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Outputlookup).

Only one question: in your lookup you whould have product and Feature, but I don't see these information in the sample you shared, so, how would you have these information?

Ciao.

Giuseppe

[isoutamo]

Hi
You should use “ with your field value name. Otherwise splunk think that your value is field name.
r. Ismo

[MVK1]

Thank you I have added double quotes in my lookup for FailureMsg field.

Could you please help on how we can write lookup query to search for FailureMsg in _raw ?

[isoutamo]

I mean that you should use " in this search

| search test_field_name="test_field_name_1"

[MVK1]

Another update:

my csv lookup in this example has only 2 rows, but it could have many more.

Also I am not planning to use other fields Product, Feature but just need FailureMsg

[MVK1]

Hi @meetmshah I have added sample _raw events from original query

[test_field_name=test_field_name_1]: Hello This is event0   no_failure_msg some other message0 id_num { data: 000 }}

[test_field_name=test_field_name_1]: Hello This is event1   fail_msg1 some other message1 id_num { data: 111 }}

[test_field_name=test_field_name_1]: Hello This is event2   fail_msg2 some other message2 id_num { data: 999 }} 

[test_field_name=test_field_name_1]: Hello This is event3   no_failure_msg some other message3 id_num { data: 222 }}

 From these events I want to return these 2 events where fail_msg1 or fail_msg2 are present

[test_field_name=test_field_name_1]: Hello This is event1   fail_msg1 some other message1 id_num { data: 111 }}

[test_field_name=test_field_name_1]: Hello This is event2   fail_msg2 some other message2 id_num { data: 999 }} 

[meetmshah]

Hello @MVK1 Can you please share some sample event or an example along with expected results to understand the query better.