CODEWITHSUNDEEP
azureazure-application-insightsazure-data-explorerazure-analytics
Abhijit
Abhijit

Reputation: 691

Find the start and end time (or time span) of the kusto query is running on azure log analytics?

I am struggling here to find the time interval over which the kusto query is being run.

What I am trying to do is to calculate the downtime of some specific API's which are running on Azure app service. All the logs of the azure APP service are present in App analytics.

To plot the downtime , I was able to find the number of minutes for the API is considered down through the query.

Now the challenge is to calculate the UP time percentage. The query runs over the logs selected through the time range provided on portal.

Meaning if I change the time range in console, the downtime minutes are getting calculated accordingly. But as I don't know the time range, I am not able to calculate the % up time for the it.

Right now, I have to update the query every time , I change the time range from console to calculate UP time percentage.

Edit 1

requests
| where client_Type != "Browser" 
| where operation_Name =~ 'GET Account/Login'
| summarize failed_requests=sumif(itemCount, toint(resultCode) >= 500), total_requests=sum(itemCount)  by bin(timestamp, 1m)
| extend failure_percentage=(failed_requests * 100/ total_requests)
| extend external_sla_failure=iff(failure_percentage < 100, 0, 1) 
| extend internal_sla_failure=iff(external_sla_failure == 0  and failed_requests < 3, 0 , 1)
| summarize internal_sla_downtime_min=sum(internal_sla_failure), external_sla_downtime_min=sum(external_sla_failure)

This query calculates the two downtime , internal and external. Now I just want to calculate the % uptime for both downtime. To calculate that I will need the time duration selected from the portal time range picker.

This time range is selected from azure portal app analytics time range picker or from azure dashboard time picker. Is there any variable or something which stores the time span (or start and endtime) which has been selected from the time picker on portal (either app analytics or azure dashboard) ?

Edit 2

I can put a hacky but imperfect logic by using min and max function on timestamp to get start and endtime but if the app does not receive any request for first n minutes or last m minutes of the selected time , the % uptime calculation will show incorrect data.

Upvotes: 5

Views: 5019

Answers (1)

KumZ
KumZ

Reputation: 595

We had a similar problem where we also need the start and end values from the time picker.

As you found there is no way to retrieve those values via variables or other tricks.

As a workaround, we used another metric that we know have values for all the selected period to first extract it's min and max which should match the time picker selection and use them in our normal query.

let mintime = toscalar(TheAlwaysPresentMetric | summarize MinTS=min(TimeGenerated));
let maxtime = toscalar(TheAlwaysPresentMetric | summarize MaxTS=max(TimeGenerated));
requests
| where [...use the mintime/maxtime...]

Upvotes: 3

Related Questions