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.
Just place NOT
before the subsearch:
index=my_index field1=abc field2=def NOT [ |inputlookup filename.csv | fields field3 ]
Just place NOT
before the subsearch:
index=my_index field1=abc field2=def NOT [ |inputlookup filename.csv | fields field3 ]
nryabykh,
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.
~Ed
Mmm, do you have lookup filename.csv
with field field3
? My subsearch is only an example, you should use your filename and fieldname.
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.
Any ideas?
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 ])
nryabykh,
Nope. I added the parenthesis and it still did not work. I got the same number of returns with some of the data that should have been filtered.
~Ed
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).
nryabykh,
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.
~Ed
All right, I'm glad we cleared this up 🙂