@ITWhisperer thank you for your quick answer.  I added the sort as you recommend it. | sort "Record Number", "PI Number", time  It seems to works well thank you ... View more

index=_internal | head 1 | fields _raw | eval _raw="ID, Version, Date, Status 10874381,1,2020-01-15T08:36:00Z,New 10874381,1,2020-01-15T08:46:00Z,Completed - Action Performed 14688643,1,2016-10-06T06:30:00Z,New 14688643,1,2016-10-07T08:32:00Z,Investigating 14688643,1,2016-10-24T15:10:00Z,Completed - Nothing Found" | multikv forceheader=1 | table ID, Version, Date, Status | rename COMMENT as "the logic" | streamstats window=2 list(Status) as tmp_status by ID | where mvcount(tmp_status) > 1 | eval Status_1=mvindex(tmp_status,0),Status_2=mvindex(tmp_status,1) | rex field=Status_2 "(?<Result>Completed)" | eval Result=if(isnull(Result),"Work",Result) | table ID Status_* Result | rename ID as Record_number ... View more

index=_internal | head 1 | fields _raw | eval _raw="Status A, B, A A, A, B B, B, A B, A, B" | multikv forceheader=1 | eval Status=trim(split(Status,",")) | rename COMMENT as "the logic" | eval State = Status | eval State=case(mvdedup(Status)="A" AND mvcount(mvdedup(Status))=1,"Value_1" ,mvdedup(Status)="B" AND mvcount(mvdedup(Status))=1,"Value_4" ,mvfind(Status,"A")=0,"Value_2" ,mvfind(Status,"B")=0,"Value_3") | eval remove_index=0,tail=mvindex(Status,(remove_index+1),mvcount(Status)-1) ,Status=tail | eval Status=mvjoin(Status,",") | streamstats count as Event | table Event Status State foreach works for fields, not value. ... View more

| stats latest(_time) as Time, latest(PI_Event_Status) as PI_Event_Status by "Record Number", PI_Number ... View more