How to join three sources without join?

[Splunk_User2806]

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%")]

[Splunk_User2806]

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

[maciep]

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?

1.  Include all events in base search
2.  create some new vars based on the sourcetype (so kinda like the rename you want to do)
3. get the detection time and process time for each snsm id (use eventstats to keep the rest of the event)
4. get the step6 time for each prodnr  (think this should put it on the nau/nas events too)
5. 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
6. 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")

[Splunk_User2806]

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! 😁

[maciep]

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.  

[isoutamo]

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.

[Splunk_User2806]

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

[inventsekar]

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

https://community.splunk.com/t5/Splunk-Search/How-to-compare-fields-over-multiple-sourcetypes-withou...

[isoutamo]

Another answer to avoid join https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi...

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