Search for items not matching values from a lookup

[ashishlal82]

I have this search which basically displays if there is a hash (sha256) value in the sourcetype= software field =sha256, but NOT in the lookup field as described below.

Question: how can I reverse it? is there a way where I can search the lookup field with sourcetype= software field =sha256?

Current search:

index=bigfix sourcetype=software NOT[|inputlookup Auth2_files.csv|table hash|rename hash as sha256]

[somesoni2 · Accepted solution]

You can try this

|inputlookup Auth2_files.csv|table hash|rename hash as sha256 | search NOT [search index=bigfix sourcetype=software | stats count by sha256 | table sha256 ]

OR

index=bigfix sourcetype=software | stats count by sha256 | table sha256 | eval from="index" | append  [|inputlookup Auth2_files.csv|table hash|rename hash as sha256 | eval from="lookup"] | stats values(from) as from by sha256 | where mvcount(from)=1 AND from="lookup" | table sha256

[ashishlal82]

Quick Question:

index=bigfix sourcetype=software NOT[|inputlookup Auth2_files.csv|table hash|rename hash as sha256].

In the above query, is the splunk comparing the field "sha256" from index to lookup? trying to understand how the comparision is done and how the query works?

[somesoni2]

This is an example of "subsearch result added as filter to base search". All the sha256 values returned from lookup will be added in the base search as a giant OR condition. The above search will be resolved as

index=bigfix sourcetype=software NOT ((sha256="valFromLookup1" ) OR (sha256="valFromLookup2" )...)

[ashishlal82]

I still trying to understand since the index has a sha256 with 256 hash values and the lookup has field hash with both sha256 and md5 and I would like to compare sha256 field in index with lookup field which is hash.
I wrote the above query and was wondering if its right?

[ashishlal82]

Can I append the results from both the query? is its possible ?

[somesoni2]

If you're looking for sha256 entries unique in both index and lookup, use the second query and remove filter of from="lookup"

[ashishlal82]

Can you explain me what you did? I am getting the results but also need to understand the query

[somesoni2]

Sure

index=bigfix sourcetype=software | stats count by sha256 | table sha256 | eval from="index"
       --takes sha256 entries from the index and added a field from="index"
|inputlookup Auth2_files.csv|table hash|rename hash as sha256 | eval from="lookup"
      -- takes sha256 entries from the lookup and added a field from="lookup"

| stats values(from) as from by sha256
      --this will provide from what all places a sha256 entry was found
           --Condition1: if a sha256 value is available in both index and lookup, then "from" field will have value (multivalued) as both "index" and  "lookup" and will have mvcount(from)=2
          - Condition2: if a sha256 value is only available in index, then "from" field will have value (single value) as "index" and mvcount(from)=1
           --Condition3: if a sha256 value is only available in lookup, then "from" field will have value (single value) as "lookup" and mvcount(from)=1

--Adding filter mvcount(from)=1 only will make the entries from condition 2 and 3