Splunk Search

Search for items not matching values from a lookup

Explorer

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]
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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

View solution in original post

Explorer

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?

0 Karma

SplunkTrust
SplunkTrust

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" )...)
0 Karma

Explorer

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?

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma