topic Re: Join 3 sources without join in Splunk Search

How to join three sources without join?

Splunk_User2806 — Sun, 27 Nov 2022 16:13:17 GMT

Hi everyone,

I want to join 3 sources from the same inidex. The Problem is, that with join i lose Date because im over 50.000 results in the subsearch. So i try to get my table over the "normal" search.

Logic is like the picture: The source "NAS" is a reported fault on a specific Production-number (PRODNR). it includes the Productionnumber, the timestamp of the detection and a clear ID (SNSM - for every fault) with the Partcode of the fault part.

The "NAU" is the data of the processed/closed defect. Problem here is as you can see that the columns in the sources have the same names.

The MP is the number of the process Step.

so every source contains the PRODNR. The NAS and NAU contain the SNSM IDs.
So i want to join the NAU ans NAS by the "SNSM" IDs and see if they alsready passed the Progress step 6 and if a fault was proccessed before the step 6 or if it was open the time the Production Number passed the Step 6.
my search that works is as shown. But its limited to the 50.000 results.

i try to to make it with index=pfps-k sourcetype=NAS OR sourcetype=NAU OR sourcetype=MP.
I get all the data but i cant do the same like the join so compare the SNSM IDs and then the Productionstep

index=pfps-k sourcetype=NAS ( PRODNR="1*" OR PRODNR="2*" ) |where 'SPERRE' like ("PZM51%")
|dedup PRODNR,PRUEFUNG
|join type=left max=0 left=NAS right=NAU where NAS.SNSM=NAU.SNSM [search index=pfps-k sourcetype=NAU ( PRODNR="1*" OR PRODNR="2*" ) |dedup SNSM]
|join type=left max=0 left=L right=MP where L.NAS.PRODNR=MP.PRODNR [search index=pfps-k sourcetype="MP" earliest=@d+6h |where MELDEPUNKT=6.0 |where like(PRODNR,"1%") OR like(PRODNR,"2%")]

Re: Join 3 sources without join

inventsekar — Fri, 25 Nov 2022 14:14:50 GMT

Hi @Splunk_User2806 .. not sure, maybe, pls check this topic:

https://community.splunk.com/t5/Splunk-Search/How-to-compare-fields-over-multiple-sourcetypes-without-join/td-p/113477

Re: Join 3 sources without join

isoutamo — Fri, 25 Nov 2022 14:22:05 GMT

Another answer to avoid join https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-join-and-the/m-p/391288/thread-id/113948

There is also some conf prentations “Join datasets without join command” or something similar which could help you.

Re: Join 3 sources without join

Splunk_User2806 — Fri, 25 Nov 2022 14:24:13 GMT

Hey thank you! Yes i know this post. I try it like for a week. I searched every Topic here. MY Problem is that i need the coloumn SNSM to be shorn in my results for the source=NAS and for the source=NAU. like rename them to SNSM_NAS ans SNSM_NAU... so i can compare if they are the same or if the SNSM_NAU is missing. Nothing worked for me till now. I need a easy join left for all thre sources and compare the SNSM numbers. Without the issue of missing Data

Re: Join 3 sources without join

isoutamo — Fri, 25 Nov 2022 14:30:24 GMT

Can you give a some scrambled sample data? That way it’s much easier to understand your challenge. I suppose that couple of events are enough.

Re: Join 3 sources without join

Splunk_User2806 — Fri, 25 Nov 2022 14:38:37 GMT

hey thank you! yes here they are. It has to look like this. I Need all Source datas one next to the other and liks the NAU and NAS data by the SNSM as you see. at the end i want to show the 6.0 for every PRODNR in the data. and i need the empty fields, so i know if the issue is closed and if it is before or after the 6.0 point

Re: Join 3 sources without join

maciep — Fri, 25 Nov 2022 16:12:30 GMT

Not sure if I fully understand the requirements, but maybe something like this could get you started? I can't really test so this could be all sorts of wrong - but I think it seems reasonable?

Include all events in base search
create some new vars based on the sourcetype (so kinda like the rename you want to do)
get the detection time and process time for each snsm id (use eventstats to keep the rest of the event)
get the step6 time for each prodnr (think this should put it on the nau/nas events too)
Now just look at the NAS events as they should have the detection time, process time and step 6 time and the prod nr as well
check the timestamps to see which thing happened when and provide the relevant output

index=whatever sourcetype IN ("MP","NAS","NAU") | eval step6_time = case(sourcetype="MP" AND meldepunkt="6",strptime(zeitstempeli,"%d.%m.%Y %H:%M")) | eval detection_time = case(sourcetype="NAS",strptime(errfaast,"%d.%m.%Y %H:%M")) | eval process_time = case(sourcetype="NAU",strptime(errfaast,"%d.%m.%Y %H:%M")) | eventstats values(detection_time) as detection_time, values(process_time) as process_time by snsm | eventstats values(step6_time) as step6_time by prodnr | where sourcetype="NAS" | eval result = case(isnotnull(process_time) AND process_time < step6_time,"fault processed before",isnotnull(process_time),"fault processed after step 6",isnull(process_time),"fault not processed")

Re: Join 3 sources without join

Splunk_User2806 — Mon, 28 Nov 2022 14:37:28 GMT

Hey! seems to work. The key was the sourcetype IN ("MP"...) and the extraction with eval case...
But my Problem now is i want to see all Production Numbers PRODNR wich passed the Step 6 today so earliest @d+6h is right... but the cause could be up to 2-3 days back! The NAS or NAU.

How can i extract the MP PORDNR from today but search for all Causes in NAS and NAU wich are linkt to that PRODNR even if they are some days back?

Thank youo very much guys! 😁

Re: Join 3 sources without join

maciep — Mon, 28 Nov 2022 18:03:16 GMT

You may be able to do a subsearch, but not sure if that's needed here. If you simply search the past 3 days (or however long in the past you need to go), then you should be able to filter only those events where step 6 occurred today.

Not looking to type it all out again, but something like this once you have all of the fields you need and are ready to actually check timestamps.

... | where step6_time > relative_time(now(),"@d")

That should eliminate any events where step 6 happened before today.