Use time range value in kusto query to calculate % uptime

%3CLINGO-SUB%20id%3D%22lingo-sub-390582%22%20slang%3D%22en-US%22%3EUse%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390582%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%26nbsp%3B%20a%20way%20to%20access%20time%20range%20selected%20from%20azure%20portal%20in%20log%2Fapp%20analytics%20query%20to%20help%20calculate%20the%20%25%20uptime%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20able%20to%20calculate%20the%20the%20downtime%20in%20minutes%20using%20our%20custom%20logic%20in%20the%20query%20%2C%20in%20order%20to%20calculate%20the%20%25%20uptime%20%2C%20I%20need%20to%20know%20the%20variable%2Ffunction%20which%20can%20give%26nbsp%3B%20time%20range%20selected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EAbhijit%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-390582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394838%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F269923%22%20target%3D%22_blank%22%3E%40abhijitchaudhari%3C%2FA%3E%20Hello%2C%3C%2FP%3E%0A%3CP%3EI'm%20using%20in%20my%20subscription%20this%20script.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20start_time%3Dstartofday(datetime(%222019-03-01%2000%3A00%3A00%20AM%22))%3B%0Alet%20end_time%3Dendofday(datetime(%222019-03-31%2011%3A59%3A59%20PM%22))%3B%0AHeartbeat%0A%7C%20where%20TimeGenerated%20%26gt%3B%20start_time%20and%20TimeGenerated%20%26lt%3B%20end_time%0A%7C%20summarize%20heartbeat_per_hour%3Dcount()%20by%20bin_at(TimeGenerated%2C%201h%2C%20start_time)%2C%20Computer%0A%7C%20extend%20available_per_hour%3Diff(heartbeat_per_hour%26gt%3B0%2C%20true%2C%20false)%0A%7C%20summarize%20total_available_hours%3Dcountif(available_per_hour%3D%3Dtrue)%20by%20Computer%20%0A%7C%20extend%20total_number_of_buckets%3Dround((end_time-start_time)%2F1h)%0A%7C%20extend%20availability_rate%3Dtotal_available_hours*100%2Ftotal_number_of_buckets%3C%2FPRE%3E%0A%3CP%3EWith%20this%20you%20can%20get%20the%20total%20time%2C%20in%20hours%2C%20by%20vms%20and%20the%20avg%20during%20the%20month.%3C%2FP%3E%0A%3CP%3EI%20hope%20this%20help%20you.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3ETks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394572%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F269923%22%20target%3D%22_blank%22%3E%40abhijitchaudhari%3C%2FA%3E%26nbsp%3B%20Just%20a%20thought%2C%20but%20if%20this%20is%20something%20that%20is%20running%2024hrs%2C%20could%20you%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fstartofdayfunction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Estartofday%3C%2FA%3E%20operator%20to%20get%20a%20%25%20up%20time%20for%20a%2024hr%20period%20-%20i.e%20I%20was%20up%20for%2010%25%20of%20the%2024hr%20day.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20consider%20submitting%20a%20user%20Voice%20request%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ffeedback.azure.com%2Fforums%2F267889-log-analytics%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Ffeedback.azure.com%2Fforums%2F267889-log-analytics%3C%2FA%3E%26nbsp%3Bfor%20your%20feature%20to%20retrieve%20the%20portal%20time%20span.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394521%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394521%22%20slang%3D%22en-US%22%3E%3CP%3EBut%20if%20I%20do%20not%20get%20any%20call%20for%20first%20n%20minutes%20and%20last%20m%20minutes%20%2C%20I%20will%20not%20able%20able%20calculate%20the%20%25%20correctly%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emore%20details%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F55379172%2Ffind-the-start-and-end-time-or-time-span-of-the-kusto-query-is-running-on-azur%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F55379172%2Ffind-the-start-and-end-time-or-time-span-of-the-kusto-query-is-running-on-azur%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391636%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F269923%22%20target%3D%22_blank%22%3E%40abhijitchaudhari%3C%2FA%3E%26nbsp%3B%20I%20don't%20think%20you%20can%20get%20the%20last%20Time%20Range%20that%20was%20set%20in%20the%20portal.%26nbsp%3B%20All%20you%20can%20do%20is%20re-create%20it%20in%20your%20query%20with%20%3CSTRONG%3ETimeGenerated%3C%2FSTRONG%3E%20filters.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20don't%20specify%20a%20time%20range%20in%20a%20query%2C%20then%20the%20one%20selected%20in%20the%20current%20portal%20window%20is%20used%20by%20default.%3C%2FP%3E%0A%3CP%3EAre%20you%20trying%20to%20check%20to%20see%20what%20default%20was%20used.%20to%20display%20it%20in%20some%20way%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20check%20the%20start%20%2F%20end%20data%20and%20workout%20the%20interval%20from%20that%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EEvent%0A%7C%20summarize%20min_time%20%3D%20min(TimeGenerated)%2C%20max_time%20%3D%20max(TimeGenerated)%0A%7C%20extend%20my_duration%20%3D%20max_time%20-%20min_time%20%20%20%2F%2F%20days%20%2F%20hrs%2F%20min%20%2F%20seconds%0A%2F%2F%20do%20other%20things...%0A%7C%20project%20%22All%20done%20%22%20%2C%20my_duration%20%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391249%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391249%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BNot%20exactly.%20What%20I%20am%20trying%20to%20do%20is%20to%20find%20the%20time%20range%20selected%20from%20portal%20and%20use%20it%20inside%20the%20log%20analytics%20query.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390637%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390637%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20are%20you%20asking%20about%20System%20Up%20time%20(from%20Servers%2C%20VMs%2C%20desktops)%20-%20you%20can%20get%20that%20if%20you%20collect%20the%20below%20Perf%20counter%20(for%20Windows%20devices)%2C%20an%20example%20would%20be%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EPerf%0A%7C%20where%20ObjectName%20%3D%3D%20%22System%22%20and%20CounterName%20%3D%3D%20%22System%20Up%20Time%22%0A%7C%20extend%20UpTime%20%3D%20CounterValue%20*%201s%0A%7C%20summarize%20arg_max(TimeGenerated%2C%20*)%20by%20Computer%0A%7C%20project%20Computer%2C%20UpTime%2C%20TimeGenerated%0A%7C%20sort%20by%20Computer%20asc%0A%20%7C%20project%20Computer%2C%20UpTime%2C%20TimeGenerated%3C%2FPRE%3E%0A%3CP%3EThere%20is%20also%20the%20example%20query%20(when%20you%20open%20a%20new%20Log%20Analytics%20Query%20Tab)%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%2F%2F%20Availability%20rate%0A%2F%2F%20Calculate%20the%20availability%20rate%20of%20each%20connected%20computer%0AHeartbeat%0A%2F%2F%20bin_at%20is%20used%20to%20set%20the%20time%20grain%20to%201%20hour%2C%20starting%20exactly%2024%20hours%20ago%0A%7C%20summarize%20heartbeatPerHour%20%3D%20count()%20by%20bin_at(TimeGenerated%2C%201h%2C%20ago(24h))%2C%20Computer%0A%7C%20extend%20availablePerHour%20%3D%20iff(heartbeatPerHour%20%26gt%3B%200%2C%20true%2C%20false)%0A%7C%20summarize%20totalAvailableHours%20%3D%20countif(availablePerHour%20%3D%3D%20true)%20by%20Computer%20%0A%7C%20extend%20availabilityRate%20%3D%20totalAvailableHours*100.0%2F24%3C%2FPRE%3E%0A%3CP%3ETime%20and%20Date%20is%20explained%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flog-query%2Fdatetime-operations%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Flog-query%2Fdatetime-operations%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722746%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20time%20range%20value%20in%20kusto%20query%20to%20calculate%20%25%20uptime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722746%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B-%20This%20is%20the%20workaround%20that%20I%20ended%20up%20doing%20as%20well%2C%20but%20I%20really%20hate%20it%20from%20a%20code%20cleanliness%20perspective%2C%20as%20it%20seems%20like%20hacky%20to%20basically%20min%2Fmax%20a%20dataset%20of%20thousands%2Fmillions%20of%20rows%20just%20to%20get%20a%20begin%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20scenario%2C%20I%20had%20a%20Kusto%20query%20that%20had%20been%20written%20against%20an%20Application%20Insights%20workspace%2C%20that%20included%20a%20moving%20average%2Fseries%20gap%20filling%20function%20for%20some%20analysis%20we%20were%20working%20on%20internally%20for%20request%20information%20(trying%20to%20flatten%20out%201-off%20spikes%20in%20request%20duration%20skewing%20graphs).%26nbsp%3B%20While%20the%20query%20worked%20in%20the%20analytics%20portal%2C%20once%20pinned%20to%20the%20dashboard%2C%20the%20graph%20showed%20no%20information.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20opened%20a%20ticket%20with%20Azure%20Support%2C%20but%20as%20I%20opened%20the%20ticket%2C%20I%20found%2Frealized%20that%20the%20reason%20the%20widget%20wasn't%20showing%20information%20is%20that%20internally%20the%20query%20was%20failing%20(found%20on%20the%20network%20tab%20of%20the%20browser)%20due%20to%20a%20difference%20in%20windowing%2Fdates%20from%20the%20portal%20vs%20those%20that%20had%20been%20defined%20in%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20since%20modified%20the%20query%20to%20utilize%20min%2Fmax%20on%20the%20timestamp%20information%2C%20but%20it'd%20definitely%20be%20nice%20to%20have%20some%20way%20of%20accessing%20this%20information%20via%20query%2C%20similar%20to%20how%20log%20analytics%20has%20the%20injectable%20query%2Fparameter%20information%20-%20%24%7BLikeThis%7D%2C%20or%20just%20as%20a%20function%26nbsp%3B%40query_windowBegin().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyways%20-%20I%20didn't%20see%20a%20request%20for%20the%20feature%20yet%2C%20but%20not%20sure%20if%20I%20was%20using%20the%20write%20search%20terms.%26nbsp%3B%20Are%20you%20aware%20of%20any%20open%2Foutstanding%20feature%20requests%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there  a way to access time range selected from azure portal in log/app analytics query to help calculate the % uptime ? 

 

I am able to calculate the the downtime in minutes using our custom logic in the query , in order to calculate the % uptime , I need to know the variable/function which can give  time range selected.

 

 

Thanks,

Abhijit  

7 Replies

Hi, are you asking about System Up time (from Servers, VMs, desktops) - you can get that if you collect the below Perf counter (for Windows devices), an example would be:

 

Perf
| where ObjectName == "System" and CounterName == "System Up Time"
| extend UpTime = CounterValue * 1s
| summarize arg_max(TimeGenerated, *) by Computer
| project Computer, UpTime, TimeGenerated
| sort by Computer asc
 | project Computer, UpTime, TimeGenerated

There is also the example query (when you open a new Log Analytics Query Tab) 

 

// Availability rate
// Calculate the availability rate of each connected computer
Heartbeat
// bin_at is used to set the time grain to 1 hour, starting exactly 24 hours ago
| summarize heartbeatPerHour = count() by bin_at(TimeGenerated, 1h, ago(24h)), Computer
| extend availablePerHour = iff(heartbeatPerHour > 0, true, false)
| summarize totalAvailableHours = countif(availablePerHour == true) by Computer 
| extend availabilityRate = totalAvailableHours*100.0/24

Time and Date is explained: https://docs.microsoft.com/en-us/azure/azure-monitor/log-query/datetime-operations 

 

 

 

@Clive Watson Not exactly. What I am trying to do is to find the time range selected from portal and use it inside the log analytics query. 

@abhijitchaudhari  I don't think you can get the last Time Range that was set in the portal.  All you can do is re-create it in your query with TimeGenerated filters.

 

If you don't specify a time range in a query, then the one selected in the current portal window is used by default.

Are you trying to check to see what default was used. to display it in some way?

 

You could check the start / end data and workout the interval from that?

 

Event
| summarize min_time = min(TimeGenerated), max_time = max(TimeGenerated)
| extend my_duration = max_time - min_time   // days / hrs/ min / seconds
// do other things...
| project "All done " , my_duration 

 

But if I do not get any call for first n minutes and last m minutes , I will not able able calculate the % correctly

 

more details here: https://stackoverflow.com/questions/55379172/find-the-start-and-end-time-or-time-span-of-the-kusto-q...

@abhijitchaudhari  Just a thought, but if this is something that is running 24hrs, could you use the startofday operator to get a % up time for a 24hr period - i.e I was up for 10% of the 24hr day.  

 

You may consider submitting a user Voice request: https://feedback.azure.com/forums/267889-log-analytics for your feature to retrieve the portal time span.

@abhijitchaudhari Hello,

I'm using in my subscription this script.

 

let start_time=startofday(datetime("2019-03-01 00:00:00 AM"));
let end_time=endofday(datetime("2019-03-31 11:59:59 PM"));
Heartbeat
| where TimeGenerated > start_time and TimeGenerated < end_time
| summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, start_time), Computer
| extend available_per_hour=iff(heartbeat_per_hour>0, true, false)
| summarize total_available_hours=countif(available_per_hour==true) by Computer 
| extend total_number_of_buckets=round((end_time-start_time)/1h)
| extend availability_rate=total_available_hours*100/total_number_of_buckets

With this you can get the total time, in hours, by vms and the avg during the month.

I hope this help you.


Tks

@Clive Watson - This is the workaround that I ended up doing as well, but I really hate it from a code cleanliness perspective, as it seems like hacky to basically min/max a dataset of thousands/millions of rows just to get a begin end.

 

In my scenario, I had a Kusto query that had been written against an Application Insights workspace, that included a moving average/series gap filling function for some analysis we were working on internally for request information (trying to flatten out 1-off spikes in request duration skewing graphs).  While the query worked in the analytics portal, once pinned to the dashboard, the graph showed no information.  

 

I opened a ticket with Azure Support, but as I opened the ticket, I found/realized that the reason the widget wasn't showing information is that internally the query was failing (found on the network tab of the browser) due to a difference in windowing/dates from the portal vs those that had been defined in the query.

 

I've since modified the query to utilize min/max on the timestamp information, but it'd definitely be nice to have some way of accessing this information via query, similar to how log analytics has the injectable query/parameter information - ${LikeThis}, or just as a function @query_windowBegin().

 

Anyways - I didn't see a request for the feature yet, but not sure if I was using the write search terms.  Are you aware of any open/outstanding feature requests?

 

Thanks!