Azure Question posted in
The official documentation to get you started can be found here.

Azure – How can I get all but the last row in a KQL query?

I’ve got a KQL query, and I want to strip out the last row of data. However, the number of rows returned is variable, so I can’t use take (aka limit) because I don’t know what number to pass it.

Is there any way to get the number of rows returned and pass that to limit?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I couldn't find any way to do this in one query, but How to convert row count result to another column in Kusto query inspired me to do it in two:

    let result_table = my_table
| query here ...
| order by x // you have to sort to get the same row removed every time!
;
// Now get all but the final row
let num_rows = toscalar(result_table | summarize count());
result_table | take num_rows - 1

  2. 0

    Firstly my table looks like :

    enter image description here

    How can I get all but the last row in a KQL query?I couldn’t find any way to do this in one query

    You could do this in one query using below KQL query:

    RithTest
| extend new_column = 0
| sort by new_column asc
| extend rn=row_number()
| project-away new_column
| order by rn desc
| extend new_column = 0
| sort by new_column asc
| extend rn1=row_number()
| where rn1>1
| order by rn asc
| project-away rn,new_column,rn1

    Output:

    enter image description here

    Fiddle.

    Login or Signup to reply.
  3. 0

    Just to add in my answer here, you can do probably what you want with Windows functions and specifically next().

    let T = range Timestamp from ago(3h) to ago(1h) step 1m
| extend Value = rand(999);
T
| order by Timestamp asc
| where isnotnull(next(Value)) //If the next value exists it isn't our last record
    Timestamp Value
    2024-03-26T04:29:59.9749979Z 203
    2024-03-26T04:30:59.9749979Z 866
    2024-03-26T04:31:59.9749979Z 219
    2024-03-26T04:32:59.9749979Z 144
    2024-03-26T04:33:59.9749979Z 350
    2024-03-26T04:34:59.9749979Z 651
    2024-03-26T04:35:59.9749979Z 216
    2024-03-26T04:36:59.9749979Z 390
    2024-03-26T04:37:59.9749979Z 554
    2024-03-26T04:38:59.9749979Z 735
    2024-03-26T04:39:59.9749979Z 799
    2024-03-26T04:40:59.9749979Z 940
    etc etc
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search