How to join multiple events based on one common value with different filters?

[Sentira]

Hi,
I've written a query query below which joins 2 different event types from same source with different filters.

source="C:\\Logs\\*" host="myHost" index="dumpchutes" "TELEGRAMID=[42]" "LastDestination=[51]"
| rex "OriginalDestination=\[(?<OriginalDestination>[^\]]+)"
| rex "OriginalDestinationState=\[(?<OriginalDestinationState>[^\]]+)"
| rex "EntrancePoint=\[(?<EntrancePoint>[^\]]+)"
| rex "EntranceState=\[(?<EntranceState>[^\]]+)"
| rex "ExitPoint=\[(?<Chute>[^\]]+)"
| rex "ExitState=\[(?<ExitState>[^\]]+)"
| rex "BarcodeScannerId=\[(?<BarcodeScanner>[^\]]+)"
| rex "BarcodeScannerDataState=\[(?<BarcodeScannerDataState>[^\]]+)"
| rex "BarcodeScannerData=\[(?<LPN>[^\]]+)"
| rex "Length=\[(?<Length>[^\]]+)"
| rex "Width=\[(?<Width>[^\]]+)"
| rex "Height=\[(?<Height>[^\]]+)"
| join LPN
[ search index="dumpchutes" source="C:\\Logs\\*" "TELEGRAMID=[CONTAINERSTATUS]" "ContainerState=[DIVERTED]" "Divert=[PALLETIZE]" "ReasonCode=[DS]"
|rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"
|rex "PalletId=\[(?<ChuteHolder>[^\]]+)"
|rex "LpnNumber=\[(?<LPN>[^\]]+)"
|rex "WcsLocation=\[(?<WcsLocation>[^\]]+)"
|rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"
]
|table _time Chute ChuteHolder LPN Length Width Height | dedup LPN

I'm having a number of issues here:

1. Using joins is too slow .
2. is there another way instead of using join?
3. is it better to save this both searches into 2 tables and then join them in the search part?

[jijulukose]

See if you can avoid join by getting LPN1 and LPN2 and coalescing them.  Then do a stats to get the values needed.

source="C:\\Logs\\*" host="myHost" index="dumpchutes" ("TELEGRAMID=[42]" "LastDestination=[51]") OR ("TELEGRAMID=[CONTAINERSTATUS]" "ContainerState=[DIVERTED]" "Divert=[PALLETIZE]" "ReasonCode=[DS]")
| rex "OriginalDestination=\[(?<OriginalDestination>[^\]]+)"
| rex "OriginalDestinationState=\[(?<OriginalDestinationState>[^\]]+)"
| rex "EntrancePoint=\[(?<EntrancePoint>[^\]]+)"
| rex "EntranceState=\[(?<EntranceState>[^\]]+)"
| rex "ExitPoint=\[(?<Chute>[^\]]+)"
| rex "ExitState=\[(?<ExitState>[^\]]+)"
| rex "BarcodeScannerId=\[(?<BarcodeScanner>[^\]]+)"
| rex "BarcodeScannerDataState=\[(?<BarcodeScannerDataState>[^\]]+)"
| rex "BarcodeScannerData=\[(?<LPN1>[^\]]+)"
| rex "Length=\[(?<Length>[^\]]+)"
| rex "Width=\[(?<Width>[^\]]+)"
| rex "Height=\[(?<Height>[^\]]+)"
| rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"
| rex "PalletId=\[(?<ChuteHolder>[^\]]+)"
| rex "LpnNumber=\[(?<LPN2>[^\]]+)"
| rex "WcsLocation=\[(?<WcsLocation>[^\]]+)"
| rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"
| eval LPN=coalesce(LPN1,LPN2)
| stats values(*) as * by LPN

[ITWhisperer]

source="C:\\Logs\\*" host="myHost" index="dumpchutes" "TELEGRAMID=[42]" "LastDestination=[51]"
| rex "OriginalDestination=\[(?<OriginalDestination>[^\]]+)"
| rex "OriginalDestinationState=\[(?<OriginalDestinationState>[^\]]+)"
| rex "EntrancePoint=\[(?<EntrancePoint>[^\]]+)"
| rex "EntranceState=\[(?<EntranceState>[^\]]+)"
| rex "ExitPoint=\[(?<Chute>[^\]]+)"
| rex "ExitState=\[(?<ExitState>[^\]]+)"
| rex "BarcodeScannerId=\[(?<BarcodeScanner>[^\]]+)"
| rex "BarcodeScannerDataState=\[(?<BarcodeScannerDataState>[^\]]+)"
| rex "BarcodeScannerData=\[(?<LPN>[^\]]+)"
| rex "Length=\[(?<Length>[^\]]+)"
| rex "Width=\[(?<Width>[^\]]+)"
| rex "Height=\[(?<Height>[^\]]+)"
| dedup LPN
| table _time Chute LPN Length Width Height
| append
[ search index="dumpchutes" source="C:\\Logs\\*" "TELEGRAMID=[CONTAINERSTATUS]" "ContainerState=[DIVERTED]" "Divert=[PALLETIZE]" "ReasonCode=[DS]"
|rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"
|rex "PalletId=\[(?<ChuteHolder>[^\]]+)"
|rex "LpnNumber=\[(?<LPN>[^\]]+)"
|rex "WcsLocation=\[(?<WcsLocation>[^\]]+)"
|rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"
|table ChuteHolder LPN | dedup LPN
]
| stats values(*) as * by LPN

[Sentira]

Thank you, but is not working for me.  My goal is to create the following view. 1 to many relation. 

Timestamp	Dump Holder	Dump Chute	Pallet group	Deactivated reason	Number of Parcel
2021-06-18 21:05:45	1	1	1	Full	4
2021-06-18 21:06:45	2	1	1	Full	6

Dump Holder	Parcel Parcode	Length	Width	Height	Parcel Type
1	0111235	478	368	100	Standard
1	0111236	35	19	2	Env
1	0111237	61	36	1	Env
1	..	..	..	..	..

[ITWhisperer]

Which part of it is not working for you? Not all the fields in your goal match those in your search. Perhaps you can share some anonymised events from your sources?

[Sentira]

I have use your solution but I got Columns without data.  When first an row is selected from table 1 then I want to show all data in the second table below. PalletId is missing in first event and to get this i linked them by LpnNumber from second event and BarcodeScannerData from first event. 
Once this done, I will join this overview with another 2 Tables later (it is not defined here yet)

Please see 2 example events. first event is for TELEGRAMID=[42] and second is for TELEGRAMID=[CONTAINERSTATUS]. 

2021-06-19 04:18:56.975 "TELEGRAMID=[42], PIC=[78379], AlibiID=[00020210619041750108444], HostPIC=[<Null!>], HostData=[<Null!>], OriginalDestination=[51], OriginalDestinationState=[1], LastDestination=[51], LastDestinationState=[1], ActualDestination=[51], DestinationTranslateState=[1], AltDestination1=[0], AltDestination2=[0], AltDestination3=[0], AltDestination4=[0], AltDestination5=[0], BarcodeScannerDataState=[6], BarcodeScannerData=[00008851780400826944], EntrancePoint=[0050.11.01], EntranceState=[1], ExitPoint=[0050.32.19], ExitState=[1], BarcodeScannerId=[501], FixedWindow=[115], NumberOfWindows=[1], Length=[478], Width=[368], CircleCounter=[0], Height=[0], ParcelBlockedStatus=[0], DivertFailedReason=[0]"

2021-06-19 04:18:57.005 "TELEGRAMID=[CONTAINERSTATUS], LpnNumber=[00008851780400826944], ContainerState=[DIVERTED], Divert=[PALLETIZE], ReasonCode=[DS], ActualWeight=[<Null!>], LocationBarcode=[6302000000], WcsLocation=[6300051], PalletId=[12640000010000289912], TaskId=[<Null!>], ScannerId=[51], MsgDbId=[564761760]"

[ITWhisperer]

| makeresults 
| eval _raw="2021-06-19 04:18:56.975 \"TELEGRAMID=[42], PIC=[78379], AlibiID=[00020210619041750108444], HostPIC=[<Null!>], HostData=[<Null!>], OriginalDestination=[51], OriginalDestinationState=[1], LastDestination=[51], LastDestinationState=[1], ActualDestination=[51], DestinationTranslateState=[1], AltDestination1=[0], AltDestination2=[0], AltDestination3=[0], AltDestination4=[0], AltDestination5=[0], BarcodeScannerDataState=[6], BarcodeScannerData=[00008851780400826944], EntrancePoint=[0050.11.01], EntranceState=[1], ExitPoint=[0050.32.19], ExitState=[1], BarcodeScannerId=[501], FixedWindow=[115], NumberOfWindows=[1], Length=[478], Width=[368], CircleCounter=[0], Height=[0], ParcelBlockedStatus=[0], DivertFailedReason=[0]\""
| rex "OriginalDestination=\[(?<OriginalDestination>[^\]]+)"
| rex "OriginalDestinationState=\[(?<OriginalDestinationState>[^\]]+)"
| rex "EntrancePoint=\[(?<EntrancePoint>[^\]]+)"
| rex "EntranceState=\[(?<EntranceState>[^\]]+)"
| rex "ExitPoint=\[(?<Chute>[^\]]+)"
| rex "ExitState=\[(?<ExitState>[^\]]+)"
| rex "BarcodeScannerId=\[(?<BarcodeScanner>[^\]]+)"
| rex "BarcodeScannerDataState=\[(?<BarcodeScannerDataState>[^\]]+)"
| rex "BarcodeScannerData=\[(?<LPN>[^\]]+)"
| rex "Length=\[(?<Length>[^\]]+)"
| rex "Width=\[(?<Width>[^\]]+)"
| rex "Height=\[(?<Height>[^\]]+)"
| append
    [| makeresults 
    | eval _raw="2021-06-19 04:18:57.005 \"TELEGRAMID=[CONTAINERSTATUS], LpnNumber=[00008851780400826944], ContainerState=[DIVERTED], Divert=[PALLETIZE], ReasonCode=[DS], ActualWeight=[<Null!>], LocationBarcode=[6302000000], WcsLocation=[6300051], PalletId=[12640000010000289912], TaskId=[<Null!>], ScannerId=[51], MsgDbId=[564761760]\""
    | rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"
    | rex "PalletId=\[(?<ChuteHolder>[^\]]+)"
    | rex "LpnNumber=\[(?<LPN>[^\]]+)"
    | rex "WcsLocation=\[(?<WcsLocation>[^\]]+)"
    | rex "LocationBarcode=\[(?<LocationBarcode>[^\]]+)"]
| stats values(*) as * by LPN

PalletId appears to have been extracted as ChuteHolder in your original post. Which columns have no data?

[Sentira]

I miss the following columns. LPN (first row below)  00008851780400696790 has data for all columns in events available, but it is not visible here.

[ITWhisperer]

That would seem to suggest that the rex extract didn't work for these LPNs. Try this to see what you get

source="C:\\Logs\\*" host="myHost" index="dumpchutes" "TELEGRAMID=[42]" "LastDestination=[51]" 00008851780400696790
| rex "OriginalDestination=\[(?<OriginalDestination>[^\]]+)"
| rex "OriginalDestinationState=\[(?<OriginalDestinationState>[^\]]+)"
| rex "EntrancePoint=\[(?<EntrancePoint>[^\]]+)"
| rex "EntranceState=\[(?<EntranceState>[^\]]+)"
| rex "ExitPoint=\[(?<Chute>[^\]]+)"
| rex "ExitState=\[(?<ExitState>[^\]]+)"
| rex "BarcodeScannerId=\[(?<BarcodeScanner>[^\]]+)"
| rex "BarcodeScannerDataState=\[(?<BarcodeScannerDataState>[^\]]+)"
| rex "BarcodeScannerData=\[(?<LPN>[^\]]+)"
| rex "Length=\[(?<Length>[^\]]+)"
| rex "Width=\[(?<Width>[^\]]+)"
| rex "Height=\[(?<Height>[^\]]+)"