Kusto - Identify if a a failed operation is the last operation in a summarized group and print that operation and user details

Temptable

let TempTable =datatable(timeStamp:datetime, fName:string, lName:string, opName:string, result:string, location:string, error:string)
[
    '2022-02-17 16:47',  'abc', 'cde' , 'PUT' ,0, 'loc1', "success",
    '2022-02-18 16:47',  'abc', 'cde' , 'Patch' ,1, 'loc1', "warning",
    '2022-02-19 16:47',  'abc', 'cde' , 'Patch' ,2, 'loc1',"specific error",
    '2022-02-20 16:47',  'abc', 'cde' , 'Delete' ,2, 'loc1',"error",
    '2022-03-01 19:47',  'xyz', 'uvw' , 'PUT' ,0, 'loc2',"success",
    '2022-03-02 19:47',  'xyz', 'uvw' , 'Patch' ,2, 'loc2',"specific error",
    '2022-03-03 19:47',  'xyz', 'uvw' , 'Delete' ,0, 'loc2',"success",
    '2022-03-04 19:47',  'ijk', 'lmn' , 'PUT' ,0, 'loc3',   "success",
    '2022-01-17 22:47', 'ijk', 'lmn' , 'Patch' ,2, 'loc3',"error",
    '2022-01-18 22:47',  'ijk', 'lmn' , 'Delete' ,0, 'loc3',"success",
    '2022-01-19 22:47',  'ijk', 'lmn' , 'PUT' ,1, 'loc3',"warning",
    '2022-01-20 22:47',  'ijk', 'lmn' , 'Patch' ,0, 'loc3',   "success",
    '2022-02-17 16:47',  'abc1', 'cde' , 'PUT' ,0, 'loc1', "success",
    '2022-02-18 16:47',  'abc1', 'cde' , 'Patch' ,1, 'loc1', "warning",
    '2022-02-19 16:47',  'abc1', 'cde' , 'Patch' ,2, 'loc1',"specific error",
];
TempTable | summarize by timeStamp, fName, lName, opName, result, location, error

Expected result - 
>'2022-02-19 16:47',  'abc', 'cde' , 'Patch' ,2, 'loc1',"specific error",
>'2022-02-19 16:47',  'abc1', 'cde' , 'Patch' ,2, 'loc1',"specific error",

Status 0 - Success

Need to get details (expected output above) of users who performed a patch operation which failed(status 2) with a specific error and no other operations were either attempted after the patch failed (with specific error) or attempted but operations were failed, until now.

Solution

scan operator

step 1: You get the requested specific error or you're already in step 1, you haven't switch to another user and the current record result is an error of some kind (including the specific error)
step 2: You haven't switch to another user, the current record result is not an error and it's the first time you enter this step

Each match starts from the specific error, followed by optional additional errors, followed by optional non-error.
Each user might have multiple matches.
For each user's match we find how it ended and we bring along the record of the first specific error.
For each user we take the last match and filter for those who ended with an error.

let TempTable =datatable(timeStamp:datetime, fName:string, lName:string, opName:string, result:string, location:string, error:string)
[
    '2022-02-17 16:47' ,'abc'  ,'cde' ,'PUT'    ,0 ,'loc1' ,"success",
    '2022-02-18 16:47' ,'abc'  ,'cde' ,'Patch'  ,1 ,'loc1' ,"warning",
    '2022-02-19 16:47' ,'abc'  ,'cde' ,'Patch'  ,2 ,'loc1' ,"specific error",
    '2022-02-20 16:47' ,'abc'  ,'cde' ,'Delete' ,2 ,'loc1' ,"error",
    '2022-03-01 19:47' ,'xyz'  ,'uvw' ,'PUT'    ,0 ,'loc2' ,"success",
    '2022-03-02 19:47' ,'xyz'  ,'uvw' ,'Patch'  ,2 ,'loc2' ,"specific error",
    '2022-03-03 19:47' ,'xyz'  ,'uvw' ,'Delete' ,0 ,'loc2' ,"success",
    '2022-03-04 19:47' ,'ijk'  ,'lmn' ,'PUT'    ,0 ,'loc3' ,"success",
    '2022-01-17 22:47' ,'ijk'  ,'lmn' ,'Patch'  ,2 ,'loc3' ,"error",
    '2022-01-18 22:47' ,'ijk'  ,'lmn' ,'Delete' ,0 ,'loc3' ,"success",
    '2022-01-19 22:47' ,'ijk'  ,'lmn' ,'PUT'    ,1 ,'loc3' ,"warning",
    '2022-01-20 22:47' ,'ijk'  ,'lmn' ,'Patch'  ,0 ,'loc3' ,"success",
    '2022-02-17 16:47' ,'abc1' ,'cde' ,'PUT'    ,0 ,'loc1' ,"success",
    '2022-02-18 16:47' ,'abc1' ,'cde' ,'Patch'  ,1 ,'loc1' ,"warning",
    '2022-02-19 16:47' ,'abc1' ,'cde' ,'Patch'  ,2 ,'loc1' ,"specific error"  
];
TempTable
| extend specific_error_flag = opName == 'Patch' and result == 2 and error == 'specific error'
| order by fName, lName, timeStamp asc
| scan with_match_id = _mid
    declare (is_valid:bool)
    with
    (
        step s1 : specific_error_flag or (fName == s1.fName and lName == s1.lName and result == 2) => is_valid = false;
        step s2 : fName == s1.fName and lName == s1.lName and result != 2 and isnull(s2.is_valid) => is_valid = true;
    )
| summarize (_max_ts_mid,_max_ts_mid_is_valid) = arg_max(timeStamp,is_valid), _min_ts_mid = arg_min(iff(specific_error_flag,timeStamp,datetime(null)),*) by fName,lName,_mid
| summarize arg_max(_mid,*) by fName,lName
| where _max_ts_mid_is_valid == false
| project-away _*

fName	lName	timeStamp	opName	result	location	error	specific_error_flag	is_valid
abc1	cde	2022-02-19T16:47:00Z	Patch	2	loc1	specific error	true	false
abc	cde	2022-02-19T16:47:00Z	Patch	2	loc1	specific error	true	false

Fiddle