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]

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.

[woodcock]

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=\"*\"/"]

[tinanicole21]

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

[woodcock]

How did it go?

[tinanicole21]

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.

[sandeepmakkena]

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

This should work.

[tinanicole21]

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.

[sandeepmakkena]

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

[tinanicole21]

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