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]
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
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
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?
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" )...)
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?
Can I append the results from both the query? is its possible ?
If you're looking for sha256 entries unique in both index and lookup, use the second query and remove filter of from="lookup"
Can you explain me what you did? I am getting the results but also need to understand the query
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