This question is a follow-up to one I've submitted previously, "Search if a field is in the results of a subsearch". If my search looks like this now;
index=my_index field1=abc field2=def field3!=aaa field3!=bbb field3!=ccc field3!=ddd field3!=eee field3!=fff
Is there a way to use an inputlookup table search to rewrite the search? I've tried using NOT in front of the inputlookup search, but that didn't work.
index=my_index field1=abc field2=def [ search NOT | inputlookup <filename.csv> | fields <my_field3> | return 100 field3]
I also tried the Splunk IN() function, but that didn't work either. It appears that the data returned from a inputlookup is in a "OR"ed format where I need an "AND" format, and I can't figure out how to do that.
Any help will be appreciated.
This doesn't seem to work for me. I altered the search as follows;
index=my_index field1=abc field2=def field3=* field3!=aaa field3!=bbb field3!=ccc field3!=ddd field3!=eee field3!=fff
And then ran the search. The "Selected Fields" showed that the specific field3 entries in the search were filtered out. I then ran this search;
index=my_index field1=abc field2=def field3=* NOT [ |inputlookup filename.csv | fields field3 ]
And the "Selected Fields" entry showed that nothing was filtered out.
Understood. But it appears that the "NOT" section of the search is only keying on the first entry in the lookup table. As an example, I altered the search to look like what I have below;
index=my_index field1=abc field2=def field3!=aaa
The results did not contain any events where field3 equaled "aaa". Then I altered it again with the search below;
index=my_index field1=abc field2=def NOT [ |inputlookup filename.csv | fields field3 ]
I got the exact same results from both - no events with "aaa" but I could see events with "bbb", "ccc", etc.
The "field3" column in the "Filename.csv" file looks like this;
field3 aaa bbb ccc ddd eee fff
Like I said, it appears that the inputlookup is seeing the first entry and then stops.
It seems subsearch should be rounded with brackets. I thought Splunk does this automatically, but I likely mistaked.
index=my_index field1=abc field2=def NOT ([ |inputlookup filename.csv | fields field3 ])
I've run out of ideas, sorry 😞
I tried to implement this, and it seems it works like a charm (link to screenshot: https://photos.app.goo.gl/Oo4WSGJLrbAAyHXk2 ).
I don't know, maybe it depends on version of Splunk (I use 7.0.2).
OMG! I found my problem. ~Operator Error~ After banging my head against a wall on this issue I took a good look at it. In the CSV file I had this;
field3, """aaa""", """bbb""", """ccc""", """ddd""", """eee""", """fff""",
When I loaded the lookup, the actual data the search was trying to match to had double quotes in the string, (It was trying to match "aaa", not just aaa.). I removed all the quotes in the CSV file and everything worked.
Sorry I drug you into this one but thanks a ton for your help. I appreciate it.