topic Combine two searches left outer style in Splunk Search

Combine two searches left outer style

altink — Tue, 29 Sep 2020 14:42:56 GMT

Hello

I have an index which gets data of manual IT system scans with the following structure (simplified for example):
SCAN_ID - this is an unique name of the scan performed
VLN_ID - item of the scan performed - there are 254 such. Unique (within every Scan_Id)
VLN_CTRL_FIND - result of the scanned item, 0-correct/1-finding
- will be mentioned later!

Not all items are scanned, some may be disabled, so not all scans have 254 events!

I have loaded three scans, with the number of rows as below:

20170705_131430_OraScan 252

20170705_135210_OraScan 252

20170703_233304_OraScan 254

The first two scans are missing VLN_IDs 7 and 251 - thus the total is -2 from the last one which was a "full" scan

What I need to achieve is a search that combines two scans - Scan Run vs Scan Baseline.
All rows of Scan Run must be displayed, even when Baseline has less.

So I tried the following command to compare:
Scan Run - 20170703_233304_OraScan
with
Scan Baseline - 20170705_135210_OraScan

index=omega_ds_idx_01 SCAN_ID="20170703_233304_OraScan"
| join type=outer
[ SEARCH 
index=omega_ds_idx_01 SCAN_ID="20170705_135210_OraScan"
| rename VLN_ID as VLN_ID2, SCAN_ID as SCAN_ID2, VLN_CTRL_FIND as VLN_CTRL_FIND2 
]
| where VLN_ID=VLN_ID2
| table _time, VLN_ID, VLN_ID2, SCAN_ID, SCAN_ID2, VLN_CTRL_FIND, VLN_CTRL_FIND2

Problem 1:
the command outputted 121 rows! I was expecting 254 (or least 252) !

Problem 2:
I switched in search the Baseline 20170705_135210_OraScan to 20170705_131430_OraScan - a previous scan.
This time the result was 252 rows!
Both Baseline Scans 20170705_135210_OraScan and 20170705_131430_OraScan have the same number of rows - 252!
And their VLN_IDs are identically the same within them and also with those of the unchanged Scan Run 20170703_233304_OraScan
So why this inconsistency on the results ?

In general, what I am trying to achieve is something simple like:

select a.Vln_id, a.Vln_Name, ...,  
a.Vln_Ctrl_Find_Run, b.Vln_Ctrl_Find_Run

from <search_run> a, <search_baseline> b

where a.Vln_Id = b.Vln_Id(+)

I have not found a way so far to achieve the above,
please advise

at your disposal for further info
best regards
Altin

Re: Combine two searches left outer style

DalJeanis — Wed, 05 Jul 2017 18:45:27 GMT

This is one of the many cases in splunk when a join can (and should) be avoided altogether.

Try this...

 index=omega_ds_idx_01 (SCAN_ID="20170703_233304_OraScan" OR SCAN_ID="20170705_135210_OraScan")
| table SCAN_ID VLN_ID VLN_CTRL_FIND 
| eventstats min(SCAN_ID) as SCAN1, max(SCAN_ID) as SCAN2 
| eval VLN_CTRL_FIND1=if(SCAN_ID=SCAN1,VLN_CTRL_FIND,null())
| eval VLN_CTRL_FIND2=if(SCAN_ID=SCAN2,VLN_CTRL_FIND,null())
| fields - VLN_CTRL_FIND SCAN_ID
| stats values(*) as * by VLN_ID

Re: Combine two searches left outer style

altink — Tue, 29 Sep 2020 14:43:08 GMT

Tried again with

index=omega_ds_idx_01 SCAN_ID="20170703_233304_OraScan" 
| join VLN_ID 
[ SEARCH 
index=omega_ds_idx_01 SCAN_ID="20170705_135210_OraScan"
| rename SCAN_ID as SCAN_ID2, VLN_CTRL_FIND as VLN_CTRL_FIND2 
]
| table _time, VLN_ID, VLN_ID2, SCAN_ID, SCAN_ID2, VLN_CTRL_FIND, VLN_CTRL_FIND2

This time I got 252 rows instead of the previous 121.

However I need to have the full 254 from the 20170703_233304_OraScan and not only what matches (VLN_ID)
to the other.

please advise
Altin

Re: Combine two searches left outer style

altink — Wed, 05 Jul 2017 19:51:56 GMT

Thank you very much Mr. DalJeanis

I tried your command, but it returned 506 events (double of 254).
Something must be not right.

Re: Combine two searches left outer style

altink — Wed, 05 Jul 2017 19:56:09 GMT

sorry I missed the 506 for row number.
the command is OK.

Re: Combine two searches left outer style

altink — Wed, 05 Jul 2017 20:03:49 GMT

thank you very much

the command you gave me works and I have the expected results.

However (to my knowledge) it is a bit complicated.

I can have the same result with:

index=omega_ds_idx_01 SCAN_ID="20170703_233304_OraScan" 
| join VLN_ID type=outer 
[ SEARCH 
index=omega_ds_idx_01 SCAN_ID="20170705_135210_OraScan"
| rename SCAN_ID as SCAN_ID2, VLN_CTRL_FIND as VLN_CTRL_FIND2
]
| table _time, VLN_ID, SCAN_ID, SCAN_ID2, VLN_CTRL_FIND, VLN_CTRL_FIND2

Is the above approach OK ? I have read the Join syntax, but other than limitations on [SEARCH], found no reason not to use it.
I would like to continue with the approach above - if it it technically correct

please advise

thanks and regards
Altin