unable to list the unmatched values compared with lookup file

[rboya_splunk]

I am trying to identify the values that are in the logs not matching with content in the lookup file. But i am not getting the results.

Here is the sample log

192.168.198.92 - - [22/Dec/2002:23:08:37 -0400] "GET / HTTP/1.1" 200 6394 www.yahoo.com  "xab|xac|za1"
192.168.198.92 - - [22/Dec/2002:23:08:38 -0400] "GET /images/logo.gif HTTP/1.1" 200 807 www.yahoo.com  "None"
192.168.198.92 - - [22/Dec/2002:23:08:37 -0400] "GET / HTTP/1.1" 200 6394 www.yahoo.com  "xab|xac|za1"
192.168.72.177 - - [22/Dec/2002:23:32:14 -0400] "GET /news/Tshirts.html HTTP/1.1" 200 3500 www.yahoo.com  "yif"
192.168.72.177 - - [22/Dec/2002:23:32:14 -0400] "GET /news/Jeans.html HTTP/1.1" 200 3500 www.yahoo.com  "zab|yif|ba1|ba1"
192.168.72.177 - - [22/Dec/2002:23:32:14 -0400] "GET /news/Polos.html HTTP/1.1" 200 3500 www.yahoo.com  "zab|yif"

the last value of the log( "xab|xac|za1") is stored as signature field in splunk. which says multiple signatures matched the requests. For few requests only one signature might have triggered.
I would like to compare the signatures in the logs with the list of signatures in the lookup table.
example lookup:

lookup table signature.csv and it contains these values:
signature_lookup
xab
yab
xac
zac
zal
yif
zab
bal

I have tried multiped queries for splitting and checking for those signatures in lookup file and if it not matched then only that result should display.
But i am getting both matched and unmatched content as query result. Don't know where i am doing the mistake.

index=* source type=* NOT(signature="None")
|makemv delim = "|" signature
|mvexpand signature
|lookup signature.csv signature_lookup
|search signature!=signature_lookup
|table signature | dedup signature

Also tried below query but no luck...

index=* sourcetype=* NOT(signature="None")
|eval sign_split=mvindex(split(signature,"|"),0)
|lookup signature.csv signature_lookup as sign_split
|table signature | dedup signature

Can some one help me in resolving this

[ITWhisperer]

After mvexpand signature, try adding this:

| append
    [| inputlookup signature.csv 
    | eval incsv=1
    | rename signature_lookup as signature
    | fields signature incsv]
| eventstats values(incsv) as incsv by signature
| where isnull(incsv)

[rboya_splunk]

@ITWhisperer , I am getting  both  matched  unmatched  signature values as result when compared with lookuptable,

 Is there any way i can get only the signature values that are not present in lookup file.

[ITWhisperer]

Can you share your search query which is giving you incorrect results? Also, please share some example events which are being incorrectly retained.

[PickleRick]

Firstly, you'd probably want a condition of

signature!="None"

instead of

NOT signature="None"

These are two different conditions - empty signature field matches the second one but doesn't match the first one.

In general, try adding pipeline steps one after another so you can see when it's not going the way you think it should.

In your case it's probably this one:

|search signature!=signature_lookup

 The search command does a simple search but cannot - like you want it to - compare dynamic values.

You need the where command

| where signature!=signature_lookup

 You should however first try trimming your search to right after the lookup and check whether the values are really what you want them to be.

Oh, and don't table the result early. And you might also try stats values instead of dedup if that's what you want to achieve.