KQl- RowCumsum detection

Question

UserId	T	status	StatusDuration(seconds)
1	2023-03-20T17:09:33.572Z	RUNNING	0
1	2023-03-20T17:09:28.572Z	RUNNING	5
1	2023-03-20T17:09:23.568Z	Idle	5
1	2023-03-20T17:09:18.628Z	stop	5
1	2023-03-20T17:09:13.564Z	RUNNING	5
2	2023-03-20T17:09:08.56Z	offline	0
2	2023-03-20T17:09:03.764Z	offline	5
2	2023-03-20T17:08:58.556Z	offline	10
2	2023-03-20T17:08:53.596Z	RUNNING	10
2	2023-03-20T17:08:48.552Z	Idle	10

In above table have two userIds, within the userIds if status changing from one to another status at very first then remaining records in the userIds has the same time(T) difference when the first status changed timedifference ,, How to find the StatusDuration for like above ??

Answer 1

I have used sample data posted in question to create a table and used below code to calculate value for ** StatusDuration(seconds)** column.

Code/Query

let  tbl = datatable(UserId: string, T: datetime , Status: string )
[
1,datetime(2023-03-20T17:09:33.572Z), "RUNNING",
1,datetime(2023-03-20T17:09:28.572Z), "RUNNING",
1,datetime(2023-03-20T17:09:23.568Z), "Idle",
1,datetime(2023-03-20T17:09:18.628Z),"stop",
1,datetime(2023-03-20T17:09:13.564Z),"RUNNING",
2,datetime(2023-03-20T17:09:08.56Z), "offline",
2,datetime(2023-03-20T17:09:03.764Z), "offline",
2,datetime(2023-03-20T17:08:58.556Z), "offline",
2,datetime(2023-03-20T17:08:53.596Z), "RUNNING",
2,datetime(2023-03-20T17:08:48.552Z), "Idle"
];
tbl
| serialize
| order  by  T
| extend  a = UserId != prev(UserId)
| extend ["StatusDuration(seconds)"] = case (a == true ,0 ,datetime_diff('second',prev(T),T) )
| project-away  a;

Result