Sequence detection in KQL

Question

I wonder wha't is the most idiomatic way to do sequence detection in KQL.
I've read a lot of KQL docs & articles but still struggling what exact operator I need.
A bit of context. Let's say I have a table

datatable(timestamp:datetime, event:string, someId:int) [
    datetime(2023-09-11), "A", 1,
    datetime(2023-09-12), "B", 1,
    datetime(2023-09-13), "A", 1,

    datetime(2023-09-14), "A", 2,
    datetime(2023-09-15), "B", 2,
]

I need to to find only those someId, where there are no A events after B event for each someId (assuming records are ordered by timestamp). So in example above only someId==2 meets this condition.
I know Oracle has quite flexible MATCH RECOGNIZE clause for doing these sequence detection kind of things, ( https://docs.oracle.com/en/database/oracle/oracle-database/18/dwhsg/sql-pattern-matching-data-warehouses.html#GUID-136DAC89-DA17-45C6-9E37-C9892723AC79 ) but is there anything similar in KQL?

I think some combination of partition operator and | evaluate sequence_detect(...) might do this in KQL, but not sure how exactly/.

Answer 1

Hi @Ilminsky, Mykola-ext ,

I didn't found a operator similar to the pattern match in the provided link. But (I think) I found a way to meet your conditions.

find a record, which meets the following conditions:
field1 == "A"
and
there are no records with field1 == "B" anywhere down below in dataset (assuming we have sorted the dataset)

Kusto:

CompTable
| order by someid asc,Timestamp asc 
| serialize 
| summarize mylist = make_list(event) by someid
| where not(tostring(mylist) matches regex "A.*B")

---

And the second case:

find a record, which meets the following conditions:
field1 == "A"
and
there are no records with field1 == "B" anywhere down below after "A"
and
there is at least 1 record with field1 == "C" anywhere down below after "A"

Kusto:

CompTable
| order by someid asc,Timestamp asc 
| serialize 
| summarize mylist = make_list(event) by someid
| where not(tostring(mylist) matches regex "A.*B")
| where tostring(mylist) matches regex "A.*C"

Hope this will help.

Kind regards, Wilko

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

Answer 2

Hello @Ilminsky, Mykola-ext,

Have you tried next() and prev() too?

If you combine this with a where clause, you can compare two rows:

Here, the heartbeat is filled with a counter. Each time the device is reset, the number is restarted at 1.

It seems the device is reset twice.

Notice that we need to 'serialize' the first query so the comparison can be made.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.