0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
daxu
  • 3,514
  • 5
  • 38
  • 76
  • From your question it's not clear what you're trying to achieve. Please edit the question and add sample input (in datatable format), and expected output, then ping here by adding a comment, and we'll try to help. – Slavik N Feb 19 '21 at 14:41

1 Answers1

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:

enter image description here

Avnera
  • 7,088
  • 9
  • 14
  • Thanks, I worked out the problem now. In appinsight, we have a matrix of monitor data and want to create alert if any data changes dramatically (say 20%). I learned prev function, but prev seems give me some wrong data for the last row when the join condition changes. So I did the stupid way, but seems working – daxu Feb 21 '21 at 21:41