topic Re: How do I compare two tables based on a logic? in Splunk Search

How do I compare two tables based on a logic?

ikenahim7 — Tue, 02 Aug 2022 19:33:05 GMT

Hi,

I have two search queries which results in table as follow:

| search query1

| table type1 platform1 target1

type1	platform1	target1
X	WIN	path/cpp
X	None	path/c
X	LINUX	path/py

| search query2

| table type2 platform2 target2

type2	platform2	target2
Z	WIN	path/cpp
Z	LINUX	path/cpp

(Target are unique based on their full path)

How I can compare both tables . by making left join between both tables and comparing, such that :

-> join both tables where first query table is the lead when comparing against, left join I believe ?

-> Count as match only IF target from first query where platform = WIN , exists in second table where platform = WIN

-> Count as match only IF target from first query where platform = LINUX, exists in second table where platform = LINUX

-> Count as match only IF target from first query where platform = NONE, exists in second table for both platform = LINUX and platform = WIN else no match

Then list results in table with total matching target, total missing target, total target for type X , total target for type Z .

How I can reach this ?

Thanks 🙂

Re: How do I compare two tables based on a logic?

morethanyell — Tue, 02 Aug 2022 21:06:25 GMT

Just substitute the `makeresults` with your actual query.

Re: How do I compare two tables based on a logic?

yuanliu — Wed, 03 Aug 2022 05:43:12 GMT

Given how Splunk search usually work, I suspect that you do not have type1:type2, platform1:platform2, etc. from raw output. Instead, you probably have a type field that can be X or Z, a platform field that can be None, WIN, or LINUX, etc., depending on how search is done. Even if you have type1:type2, etc., you can probably make a search that turn them into a uniform set of fields. This way, you don't have to use low-performance commands like join.

When you have that, what you describe can mostly be delivered with stats commands. Here's a prototype

type IN (X, Z) ``` magical raw search ``` | stats values(type) as type by target platform | eval match = if(mvcount(type) > 1, "yes", null()) | stats values(*) as * by target | eval match = if(mvcount(platform) > 2, "yes", match) | eventstats dc(target) as total_match by match | stats dc(target) as targets_by_type values(total_match) as total_match by type | eventstats sum(targets_by_type) as total_targets

The first line is pseudo code for that magical raw search that will return type, platform, and target. Sample output could be

type	targets_by_type	total_match	total_targets
X	4	2	6
Z	2	2	6

Obviously if you really want total miss, just subtract total_match from total_targets.

The above output is derived from simulated input like the following

platform	target	type
WIN	path/cpp	X
None	path/c	X
LINUX	path/py	X
None	path/e	X
WIN	path/c	Z
Linux	path/c	Z
WIN	path/cpp	Z
LINUX	path/cpp	Z

Here is the code used to simulate data

| makeresults | eval _raw="type platform target X WIN path/cpp X None path/c X LINUX path/py X None path/e Z WIN path/c Z Linux path/c Z WIN path/cpp Z LINUX path/cpp" | multikv forceheader=1

Re: How do I compare two tables based on a logic?

ikenahim7 — Wed, 03 Aug 2022 06:32:54 GMT

This wont work, my actual values are move then 30K targets! the tables above was just to explain my logic how i want to compare both queries. each query returns around 12k targets