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:
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
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
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 | .. | .. | .. | .. | .. |
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?
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]" |
| 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?
I miss the following columns. LPN (first row below) 00008851780400696790 has data for all columns in events available, but it is not visible here.
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>[^\]]+)"