topic Re: How can I do a lookup where a field does not exist? in Splunk Search

How can I do a lookup where a field does not exist?

jdoll1 — Wed, 13 Dec 2017 20:12:40 GMT

I'm trying to create a search that will do a lookup against a control file, and show me events where the events meet criteria in the control file and return the "Summary" field of that file. The problem is that I have two criteria that are similar, but for one I expect a value (any value) in Field5, and the other I expect Field5 to not exist. How can I do this?

My base search

sourcetype=mysource
[ inputlookup mycsv.csv
| fields field1,field2,field3,field4,field5 ]
| lookup mycsv.csv field1
| table _time,summary

in my CSV i have the following sample data:
Summary Field1 Field2 Field3 Field4 Field5
Event1 foo1 foo2 foo3 foo4 *
Event2 foo1 foo2 foo3 foo4 (blank entry in the csv)

CSV contiains additional rows with other criteria for the fields

My results come back with the Summary field showing Event1 and Event2 for both events that match fields1-4, regardless of Field5..

if I do a search for
sourcetype=mysource field1=foo1 field2=foo2 field3=foo3 field4=foo4 NOT field5= *

sourcetype=mysource field1=foo1 field2=foo2 field3=foo3 field4=foo4 field5= *

I get the expected results individually - how can I combine this in my lookup file?

Re: How can I do a lookup where a field does not exist?

sbbadri — Wed, 13 Dec 2017 21:02:59 GMT

@jdoll1

try this,

sourcetype=mysource
[ inputlookup mycsv.csv
| fields field1,field2,field3,field4,field5 ]
| lookup mycsv.csv field1
| eval resultF5=if(field5!=" ", "field5 with value","field5 without value")
| table _time,summary, resultF5

Re: How can I do a lookup where a field does not exist?

jdoll1 — Wed, 13 Dec 2017 21:11:03 GMT

They all come back with "field5 without value". I tried both the positive and negative matches, tried using

isNull and = *
NOT = *

Re: How can I do a lookup where a field does not exist?

cmerriman — Wed, 13 Dec 2017 21:34:25 GMT

going off of @sbbadri 's solution:

sourcetype=mysource
[ inputlookup mycsv.csv
| table field1,field2,field3,field4,field5|format ]
| lookup mycsv.csv field1
| eval resultF5=if(isnotnull(field5), "field5 with value","field5 without value") 
| table _time,summary, resultF5

if you have any sample data from mysource and mycsv and what the expected output should be, that might be more helpful if the above doesn't work.

Re: How can I do a lookup where a field does not exist?

somesoni2 — Wed, 13 Dec 2017 22:22:27 GMT

Give this a try

sourcetype=mysource
 [ inputlookup mycsv.csv 
 | fields field1,field2,field3,field4,field5 
 | format | rex field=search mode=sed "s/(field4\=\"[^\"]+\"\s+)\)/\1 AND NOT field5=*)/g" ] 
| lookup mycsv.csv field1
| table _time,summary

When field5 is blank/null on 2nd rows, Splunk generates following condition from subsearch:

( ( field1="A1" AND field2="B1" AND field3="C1" AND field4="D1" AND field5="E1" ) OR ( field1="A2" AND field2="B2" AND field3="C2" AND field4="D2" ) )

Above search basically looks for missing field5 expression (after field4="xx" , you get closing bracket), and adds a AND field5=* there. so that the condition becomes:

( ( field1="A1" AND field2="B1" AND field3="C1" AND field4="D1" AND field5="E1" ) OR ( field1="A2" AND field2="B2" AND field3="C2" AND field4="D2" AND NOT field5=*) )

Re: How can I do a lookup where a field does not exist?

DalJeanis — Wed, 13 Dec 2017 22:36:48 GMT

Try this -

sourcetype=mysource
    [ inputlookup mycsv.csv 
    | table field1,field2,field3,field4 ]
| lookup mycsv.csv field1 field2 field3 field4 OUTPUT field5 as field5_Flag
| where (isnotnull(field5) AND isnotnull(field5_Flag))
    OR  (isnull(field5) AND (isnull(field5_Flag) OR field5_Flag=""))

That will select records that match the first four, and then test the fifth.

Personally, I would change field5 in the csv to a flag with "Some", "None" , or "Any"

sourcetype=mysource
    [ inputlookup mycsv.csv 
    | table field1,field2,field3,field4 ]
| lookup mycsv.csv field1 field2 field3 field4 OUTPUT field5_Flag
| where (isnotnull(field5) AND (field5_Flag="Some" OR field5_Flag="Any"))
    OR  (isnull(field5) AND (field5_Flag="None" OR field5_Flag="Any"))

Re: How can I do a lookup where a field does not exist?

jdoll1 — Thu, 14 Dec 2017 16:04:20 GMT

Doesn't seem to work. For some reason, i'm not getting the field5_flag in my resulting events, therefore the where is not working at all.

I've tried stripping it down to before the where, but the flag field just doesn't come back

Re: How can I do a lookup where a field does not exist?

jdoll1 — Thu, 14 Dec 2017 17:32:21 GMT

This search seems to just ignore all events where the first four match...and gives me nothing.

Re: How can I do a lookup where a field does not exist?

somesoni2 — Thu, 14 Dec 2017 17:41:19 GMT

Ok. Can you post the normalizedSearch value from the Inspect Job for it? (the subsearch would expand and seeing that will help fix the formatting).

Re: How can I do a lookup where a field does not exist?

jdoll1 — Thu, 14 Dec 2017 18:18:34 GMT

Sorry, the data is security data, so can't really provide samples. This didn't work exactly either, but I think i've got it now, based off this answer. I really don't like it, and i'm sure that there is a much better/cleaner way to do this.

I've changed around my format of the CSV a little, and have pulled the field5 out all together, and I also pulled the second "matching" line out. so my sample CSV looks like this now:

Summary Field1 Field2 Field3 Field4
Event1 foo1 foo2 foo3 foo4
Event2 bar1 bar2 bar3 bar4
etc

my search is:

sourcetype=mysource
    [ inputlookup mycsv.csv 
    | fields field1,field2,field3,field4 ] 
| eval field5Exists=if(isnotnull(field5),"TRUE","FALSE")
| lookup mycsv.csv field1
| eval Summary=if(like(field5Exists,"TRUE"),"Event That is no longer in CSV","Event1")
| table _time,Summary

Re: How can I do a lookup where a field does not exist?

jdoll1 — Mon, 18 Dec 2017 18:40:07 GMT

Update: I changed the eval that determines the TRUE/FALSE to a calculated field. That way I can just do this:

| eval Summary=if(like(expirationUpdated,"TRUE"),"Key Expiration Updated",Summary)

instead of needing two evals