Kusto query to get queued events from a table

Question

Can anyone help with constructing a kusto query from the below table data:

ProcessName	ProcessID	TimeStamp	Status
abc	101	11:45:06	Queued
xyz	102	11:45:51	Queued
abc	101	11:45:57	Progress
abc	101	11:47:28	Succeeded
abc	103	11:48:51	Queued
abc	103	11:49:57	Progress
abc	103	11:50:28	Succeeded

I would like to get the xyz value that is in queued state as a result of the query, the condition is morethan 5m in queued state.

Here is waht I have been trying but no success.

let Events = MyLogTable | where ... ;

Events
| where Status == "Queued"
| project ProcessName, ProcessId, StartTime=TimeStamp
| join (Events 
        | where Status !in ("InProgress","Succeeded")
        | project ProcessId) 
    on ProcessId
| where StartTime>ago(5m)
| project ProcessName, ProcessId, StartTime, Status

Any help is really appreciated, Thanks in Advance.

Yoni L. Yoni L. · Accepted Answer · 2021-02-03T20:54:51

assuming there's exactly one record with Status == Queued per process ID, this could work:

let Events = datatable(ProcessName:string, ProcessID:int, TimeStamp:datetime, Status:string)
[
    'abc', 101, datetime(2021-02-02 11:45:06), 'Queued',
    'xyz', 102, datetime(2021-02-02 11:45:51), 'Queued',
    'abc', 101, datetime(2021-02-02 11:45:57), 'Progress',
    'abc', 101, datetime(2021-02-02 11:47:28), 'Succeeded',
    'abc', 103, datetime(2021-02-02 11:48:51), 'Queued',
    'abc', 103, datetime(2021-02-02 11:49:57), 'Progress',
    'abc', 103, datetime(2021-02-02 11:50:28), 'Succeeded',
]
;
Events
| where Status == "Queued" and ago(5m) > TimeStamp
| where ProcessID !in ((
    Events
    | where Status != "Queued"
    | project ProcessID
))

Kusto query to get queued events from a table

1 Answers