How do I compare two tables based on a logic?

[ikenahim7]

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 🙂

[yuanliu]

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

[morethanyell]

Just substitute the `makeresults` with your actual query. 

| makeresults 
| eval _raw = "type1,platform1,target1
X,WIN,path/cpp
X,None,path/c
X,LINUX,path/py" 
| multikv forceheader=1 
| table type1 platform1 target1 
| join type=inner left=L right=R where L.platform1=R.platform2 L.target1=R.target2 
    [| makeresults 
    | eval _raw = "type2,platform2,target2
Z,WIN,path/cpp
Z,LINUX,path/cpp" 
    | multikv forceheader=1 
    | table type2 platform2 target2 ]
| stats c

[ikenahim7]

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