topic Re: 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. in 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 — Wed, 03 Jul 2019 15:28:07 GMT

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.

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

sandeepmakkena — Wed, 03 Jul 2019 18:28:09 GMT

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

This should work.

Re: 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 — Wed, 03 Jul 2019 18:44:37 GMT

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.

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

sandeepmakkena — Wed, 03 Jul 2019 18:53:25 GMT

Try this.

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

woodcock — Thu, 04 Jul 2019 00:20:09 GMT

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

Re: 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 — Tue, 09 Jul 2019 16:56:30 GMT

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

Re: 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 — Tue, 09 Jul 2019 16:56:37 GMT

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

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

woodcock — Thu, 11 Jul 2019 05:51:03 GMT

How did it go?

Re: 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 — Thu, 11 Jul 2019 19:42:11 GMT

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.