Assuming _time and OrderId uniquely identify events in the search and summary index, try something like this index=ABC (sourcetype=DepTsuEventTrackingUpdate DepTsuEventTrackingUpdate.LocationQualifi...
See more...
Assuming _time and OrderId uniquely identify events in the search and summary index, try something like this index=ABC (sourcetype=DepTsuEventTrackingUpdate DepTsuEventTrackingUpdate.LocationQualifiedName=Tray* DepTsuEventTrackingUpdate.TsuSuspect.TsuSuspectReason!=null AND DepTsuEventTrackingUpdate.TsuSuspect.TsuSuspectReason!="TsuUnknownContent") OR (sourcetype=DepTsuEventContentMove)
| foreach *.OrderId
[| eval OrderId=coalesce('OrderId','<<FIELD>>')]
| replace ProtrusionFront with Protrusion , ProtrusionBack with Protrusion , ProtrusionLeft with Protrusion , ProtrusionRight with Protrusion , ProtrusionTop with Protrusion
| rename DepTsuEventTrackingUpdate.TsuSuspect.CheckResult.CheckType as Error DepTsuEventTrackingUpdate.TsuSuspect.TsuSuspectReason as TsuSuspectReason DepTsuEventContentMove.SenderFmInstanceName as Location DepTsuEventTrackingUpdate.TsuId as TsuId DepTsuEventContentMove.TsuContent.Quantity as Quantity DepTsuEventContentMove.LocationQualifiedName as TrayLoad DepTsuEventContentMove.TsuContent.CaseTypeId as CaseTypeId
| eval OrientationError=if(Error="Orientation","1","0") , ProtrusionError=if(Error="Protrusion","1","0") , LengthError=if(Error="Length","1","0") , WidthError=if(Error="Width","1","0") , HeightError=if(Error="Height","1","0") , OffCentreError=if(Error="OffCentre","1","0")
| eval DimensionError=if(LengthError>0 OR WidthError>0 OR HeightError>0, "1","0")
| eval ErrorQty=(OrientationError+ProtrusionError+DimensionError+OffCentreError) , TrayError=(OrientationError+ProtrusionError+LengthError+WidthError+HeightError+OffCentreError) , TrayError=if(TrayError>0,"1",null)
| eval Dimension=if(DimensionError>0 AND ErrorQty="1" ,"1","0") , Orientation=if(OrientationError="1" AND ErrorQty="1","1","0") , Protrusion=if(ProtrusionError="1" AND ErrorQty="1","1","0") , Length=if(LengthError="1" AND ErrorQty="1","1","0") , Width=if(WidthError="1" AND ErrorQty="1","1","0") , Height=if(HeightError="1" AND ErrorQty="1","1","0") , OffCentre=if(OffCentreError="1" AND ErrorQty="1","1","0") , Mixed=if(Dimension="0" AND ErrorQty>1,"1","0")
| eval Layer=if(TrayLoad="PalletInPosition","1",null) , CaseQty=if(TrayLoad="TrayLoad1" OR TrayLoad="TrayLoad2",Quantity,null) , Tray=if(TrayLoad="TrayLoad1" OR TrayLoad="TrayLoad2","1",null)
| stats min(_time) as _time values(Location) as Location sum(Layer) as PalletLayers sum(Tray) as TrayQty sum(CaseQty) as CaseQty sum(TrayError) as TrayError sum(Orientation) as OrientationError sum(Length) as LengthError sum(Width) as WidthError sum(Height) as HeightError sum(Protrusion) as ProtrusionError sum(OffCentre) as OffCentreError sum(Dimension) as Dimension sum(Mixed) as Mixed values(CaseTypeId) as CaseTypeId by OrderId
| eval reporttype="DepTrayCaseQty"
| eval foo=Dimension+Mixed+OrientationError+ProtrusionError+OffCentreError
| table _time reporttype OrderId CaseTypeId Location PalletLayers TrayQty CaseQty TrayError foo Dimension Mixed OrientationError LengthError WidthError HeightError ProtrusionError OffCentreError
| where isnotnull(CaseQty)
| eval flag=1
| append [search index=analyst
| eval flag=2]
| eventstats sum(flag) as flags by _time OrderId
| where flags = 1
| fields - flag flags
| collect index=analyst