How to compare two search results and list difference and matches?

[ikenahim7]

Hi guys im new to Splunk, 

Im trying to write a query to compare two search results and shows the differences and the matches, both search results are coming from the same index. 

I would like to have something like this, where {path-values}  hold the paths values and {countpath} holds the count.

Build-type   |  paths-count | matches-values    | diff-values             | matches-count | diff-count| 

gradle           | 20K                  | {path-values}         | {path-values}        | {countpath}         | {countpath} 

bazel             | 10K                  | {path-values}         | {path-values}       | {countpath}           | {countpath} 

my index is based on this json, where total event is a 30k (number of json posted to splunk)

{"source":"build","sourcetype":"json","event":{"type":"bazel","paths":["test3"]}}

my current query looks like:

index="build" type="bazel"
| stats values(paths{}) as paths | stats count(eval(paths)) AS totalbazelpaths
| mvexpand totalbazelpaths
| eval eventFound = 0
| join type=left run_id paths
[ index="build" type="gradle"
| stats values(paths{}) as paths | stats count(eval(paths)) AS totalgradlepaths
| mvexpand totalgradlepaths
| eval eventFound=1]
| eval percentage = round(totalbazelpaths/totalgradlepaths, 10)
| table totalgradlepaths totalbazelpaths percentage

any help how to achieve this? @yuanliu 

Thanks 

[ikenahim7]

Thanks a lot, query looks as expected 🙂

[martinpu]

Try this:

index="build" type="bazel" OR type="gradle"
| stats values(paths{}) as paths_values, dc(paths{}))  as distinct_paths_count c(paths{}))  as count_paths  by type


Not sure what you mean by matches.

[ikenahim7]

I meant by matches, paths that are common for both type gradle and bazel.

so the idea is to show number of paths for each type (bazel and gradle)

show the number of common paths between bazel and gradle  and show the actual paths value that are common 

shows the number of paths that aren’t common  and show the actual paths thats aren't common. 

[yuanliu]

Not sure if it is useful to show every column as illustrated, but it's certainly doable.  Sample code could be

index="build" (type="bazel" OR type="gradle")
| mvexpand paths{}
| eventstats dc(type) as typecount values(type) as types by paths{}
| eval matches_values = if(typecount>1, 'paths{}', null()), diff_values = if(typecount>1, null(), 'paths{}')
| stats dc(eval('paths{}')) as paths-count values(*_values) as *_values dc(*_values) as *-count by type

Using simulated data based on your example, you can get

_raw	type	paths-count	diff_values	matches_values	diff-count	matches-count
{"source":"build","sourcetype":"json","event":{"type":"bazel","paths":["test1", "test3"]}}	bazel	2	test1	test3	1	1
{"source":"build","sourcetype":"json","event":{"type":"gradle","paths":["test2", "test3"]}}	gradle	2	test2	test3	1	1

[ikenahim7]

The query works as expected thanks a lot. 

If I want to extend the query to ignore some paths based on a string value , how i can achieve that ?

assuming I have:

{"source":"build","sourcetype":"json","event":{"type":"bazel","paths":["test1.cpp", "test3.c"]}} 

{"source":"build","sourcetype":"json","event":{"type":"gradle","paths":["test2.cpp", "test3.py"]}}

Comparing this two I want to ignore path with extension ".cpp" from being used while comparing Gradle with Bazel ?, so the total account of Gradle path wont count paths with .cpp and also wont be list in diff_values ?

Thanks 🙂

[martinpu]

You could try the mvfilter command or use 
| where 'paths{}' !="*.cpp"
after the mvexpand

[yuanliu · Accepted solution]

Small correction: search command wildcard doesn't work in where command.  So, either | search paths{} !="*.cpp" or | where match('paths{}', "\.cpp$") after  mvexpand, e.g.,

index="build" (type="bazel" OR type="gradle")
| mvexpand paths{}
| search paths{} != *.cpp
| eventstats dc(type) as typecount values(type) as types by paths{}
| eval matches_values = if(typecount>1, 'paths{}', null()), diff_values = if(typecount>1, null(), 'paths{}')
| stats dc(eval('paths{}')) as paths-count values(*_values) as *_values dc(*_values) as *-count by type