topic Re: Compare two search results and list difference and matches in Splunk Search

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

ikenahim7 — Sat, 30 Jul 2022 22:21:11 GMT

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.

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

Re: Compare two search results and list difference and matches

martinpu — Sat, 30 Jul 2022 21:45:24 GMT

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.

Re: Compare two search results and list difference and matches

ikenahim7 — Sat, 30 Jul 2022 21:59:54 GMT

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.

Re: Compare two search results and list difference and matches

yuanliu — Sun, 31 Jul 2022 04:09:16 GMT

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

Re: Compare two search results and list difference and matches

ikenahim7 — Sun, 31 Jul 2022 09:51:42 GMT

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 🙂

Re: Compare two search results and list difference and matches

martinpu — Sun, 31 Jul 2022 11:31:01 GMT

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

Re: Compare two search results and list difference and matches

yuanliu — Mon, 01 Aug 2022 01:06:54 GMT

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

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

ikenahim7 — Mon, 01 Aug 2022 06:23:16 GMT

Thanks a lot, query looks as expected 🙂