topic Re: unable to list the unmatched values compared with lookup file in Splunk Search

unable to list the unmatched values compared with lookup file

rboya_splunk — Thu, 03 Feb 2022 09:04:32 GMT

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

Can some one help me in resolving this

Re: unable to list the unmatched values compared with lookup file

PickleRick — Thu, 03 Feb 2022 09:53:55 GMT

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.

Re: unable to list the unmatched values compared with lookup file

ITWhisperer — Thu, 03 Feb 2022 10:09:54 GMT

After mvexpand signature, try adding this:

Re: unable to list the unmatched values compared with lookup file

rboya_splunk — Thu, 03 Feb 2022 10:44:45 GMT

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

Re: unable to list the unmatched values compared with lookup file

ITWhisperer — Thu, 03 Feb 2022 10:54:58 GMT

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