Forum Discussion

abhijitchaudhari's avatar
abhijitchaudhari
Copper Contributor
Mar 29, 2019

Use time range value in kusto query to calculate % uptime

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  

  • Lucas Chies's avatar
    Lucas Chies
    Copper Contributor

    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

    • ankitac's avatar
      ankitac
      Icon for Microsoft rankMicrosoft

      I'm trying to use a similar query, taking the TimeGenerated field as input and get the below error. 

       

      Error is Query could not be parsed at 'datetime(start_time)'

      let start_time =  Heartbeat | summarize min(TimeGenerated);
      let end_time =  Heartbeat | summarize max(TimeGenerated);
      Heartbeat
      | where TimeGenerated > datetime(start_time) and TimeGenerated < datetime(end_time)

       

  • 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 

     

     

     

    • abhijitchaudhari's avatar
      abhijitchaudhari
      Copper Contributor

      CliveWatson 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. 

      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft

        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 

         

Resources