Splunk Search

Create search query where events match lookup table 'fieldA' column (all have values) but exclude events that do not have a value in 'fieldB' column.

tinanicole21
New Member

Example Lookup Table entries:
fieldA fieldB
value value
value 'blank'
value value

Show events where 'fieldA' matches but exclude events where 'fieldB' is blank, within the lookup table. 'fieldB' does not exist in any of the events.

Test query (that does not work):
| lookup fieldA | search fieldB != ""

I believe the query above will match events that contain 'fieldA' but then searches the events where fieldB is blank within the event. Again, the events do not contain 'fieldB' and I'm not looking to append any other fields within the lookup table.

0 Karma

woodcock
Esteemed Legend

Like this:

index=<YouShouldAlwaysSpecifyAnIndex> AND sourcetype=<AndSourcetypeToo> AND
[|inputlookup <YourLookupNameHere>
| fillnull fieldA fieldB value="T3mpPl4c3h0ld3r"
| format
| rex field=search mode=sed "s/(\S+)=\"T3mpPl4c3h0ld3r\"/NOT \1=\"*\"/"]
0 Karma

tinanicole21
New Member

I will give it a shot and will report back with my results.

0 Karma

woodcock
Esteemed Legend

How did it go?

0 Karma

tinanicole21
New Member

Our team has not had a chance to try it yet, due to some other real-world issues. I'm pretty optimistic that this should work for them.

0 Karma

sandeepmakkena
Contributor

| inputlookup Lookup_Table
| table fieldA fieldB
| where NOT fieldB == " "

This should work.

0 Karma

tinanicole21
New Member

To clarify, here is the full search query example:

| lookup fieldA | search fieldB != ""

I'm looking to display only sourcetype events that contain an entry in fieldA and fieldB of the lookup table, and not the ones that contain an entry in fieldA where fieldB is blank. I'm not looking to display specific rows within the lookup table. Thanks for the suggestion though.

0 Karma

sandeepmakkena
Contributor

| inputlookup Lookup_Table
| eventstats values( fieldA) as tmp
| eval found=if(isnull(mvfind(tmp, fieldB)),0,1) | where found=1
| table fieldA fieldB
| where NOT fieldB == " "

Try this.

0 Karma

tinanicole21
New Member

I will give it a shot and will report back with my results.

0 Karma