How to use where condition in a kusto/appinsight join

Question

I am trying to achieve these things:

1. Get most recent data for certain fields (base on timestamp) -> call this latestRequest
2. Get previous data for these fields (basically timestamp < latestRequest.timestamp)-> call this previousRequest
3. Count the difference between latestRequest and previousRequest

This is what I come with now:

let LatestRequest=requests
| where operation_Name == "SearchServiceFieldMonitor"
| extend Mismatch = split(tostring(customDimensions.IndexerMismatch), " in ")
| extend    difference = toint(Mismatch[0])
        ,   field = tostring(Mismatch[1])
        ,   indexer = tostring(Mismatch[2])
        ,   index = tostring(Mismatch[3])
        ,   service = tostring(Mismatch[4])
| summarize MaxTime=todatetime(max(timestamp)) by service,index,indexer;    



let previousRequest = requests
| where operation_Name == "SearchServiceFieldMonitor"
| extend Mismatch = split(tostring(customDimensions.IndexerMismatch), " in ")
| extend    difference = toint(Mismatch[0])
        ,   field = tostring(Mismatch[1])
        ,   indexer = tostring(Mismatch[2])
        ,   index = tostring(Mismatch[3])
        ,   service = tostring(Mismatch[4])
|join (LatestRequest) on indexer, index,service
|where timestamp <LatestRequest.MaxTime

However, I get this error from this query:

Ensure that expression: LatestRequest.MaxTime is indeed a simple name

I tried to use toDateTime(LatestRequest.MaxTime) but it doesn't make any difference. What I am doing wrong?

Answer 1

The error you get is because you can't refer to a column in a table using the dot notation, you should simply use the column name since the results of a join operator is a table with the applicable columns from both side of the join.

An alternative to join might be using the row_number() and prev() functions. You can find the last record and the one before it by ordering the rows based on the key and timestamp and then calculate the values between the current row and the row before it.

Here is an example:

datatable(timestamp:datetime, requestId:int, val:int) 
    [datetime(2021-02-20 10:00), 1, 5,
    datetime(2021-02-20 11:00), 1, 6,
    datetime(2021-02-20 12:00), 1, 8,
    datetime(2021-02-20 10:00), 2, 10,
    datetime(2021-02-20 11:00), 2, 20,
    datetime(2021-02-20 12:00), 2, 30,
    datetime(2021-02-20 13:00), 2, 40,
    datetime(2021-02-20 13:00), 3, 100
]
| order by requestId asc, timestamp desc
| extend rn = row_number(0, requestId !=prev(requestId))
| where rn <= 1
| order by requestId,  rn desc 
| extend diff = iif(prev(rn) == 1, val - prev(val), val)
| where rn == 0
| project-away rn

The results are: