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
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
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
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
sorry I missed the 506 for row number.
the command is OK.
Thank you very much Mr. DalJeanis
I tried your command, but it returned 506 events (double of 254).
Something must be not right.
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