CODEWITHSUNDEEP
azure-application-insightsazure-log-analytics
Brian Delaney
Brian Delaney

Reputation: 181

Application Insights Query for higher than usual

I'm trying to write a custom query in Azure Application Insights Analytics that will check whether a certain request timer metric has been greater then it had been previously.

I can easily write a query that checks whether the request time has been below 500ms 90% of the time in the last hour using the below query. It will return passed if it has been. 

    requests
| where timestamp >= ago(1hour)
| where (itemType == 'request' and (tostring(customDimensions['RequestName']) == 'TestRequest' 
| extend responseTime = tolong(customDimensions['totalMilliseconds']) 
| summarize isPassed = (percentile(responseTime, 90) < 500)
| project iff(isPassed, "PASSED", "FAILED")

However what I want to do is replace the hard coded '500' value with a dynamic value possibly generated from another Analytics query. For example I may want to ensure that that the response time for 90% (or whatever) of the queries in the last hour is less then the response time of 90% of the queries in the last month.

I can just duplicate the above query to achieve this new query by changing the value in the ago method and then not doing the pass/fail check: 

    requests
| where timestamp >= ago(30day)
| where (itemType == 'request' and (tostring(customDimensions['RequestName']) == 'TestRequest' 
| extend responseTime = tolong(customDimensions['totalMilliseconds']) 
| summarize percentile(responseTime, 90)

I'm unsure as to how I could run these two queries together for example save the output of the second query to use in the first query. Something like this is what im trying to achieve:

  requests
| where timestamp >= ago(30day)
| where (itemType == 'request' and (tostring(customDimensions['RequestName']) == 'TestRequest' 
| extend responseTime = tolong(customDimensions['totalMilliseconds']) 
| X = summarize percentile(responseTime, 90)
   requests
    | where timestamp >= ago(1hour)
    | where (itemType == 'request' and (tostring(customDimensions['RequestName']) == 'TestRequest' 
    | extend responseTime = tolong(customDimensions['totalMilliseconds']) 
    | summarize isPassed = (percentile(responseTime, 90) < X)
    | project iff(isPassed, "PASSED", "FAILED")

If anyone could point me in the direction on how I might achieve this it would be greatly appreciated.

Thanks for your help.

Upvotes: 1

Views: 2235

Answers (1)

ZakiMa
ZakiMa

Reputation: 6281

This should do a trick:

let threshold = toscalar(requests
| where timestamp > ago(30d)
| summarize percentile(duration, 90));
requests
| where timestamp > ago(1h)
| summarize percentile90 = percentile(duration, 90)
| extend isPassed = percentile90 < threshold
| project Result=iff(isPassed, "PASSED", "FAILED")

enter image description here

Upvotes: 1

Related Questions