Mar 29 2019
12:46 AM
- last edited on
Apr 07 2022
05:43 PM
by
TechCommunityAP
Mar 29 2019
12:46 AM
- last edited on
Apr 07 2022
05:43 PM
by
TechCommunityAP
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
Mar 29 2019 02:43 AM
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
Mar 30 2019 03:26 AM
@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.
Apr 01 2019 01:09 AM - edited Apr 01 2019 01:27 AM
@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
Apr 04 2019 03:03 AM - edited Apr 04 2019 03:13 AM
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...
Apr 04 2019 04:48 AM - edited Apr 04 2019 11:39 AM
@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.
Apr 04 2019 11:25 AM
@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
Jun 26 2019 01:47 PM
@CliveWatson - 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!
Apr 06 2022 01:44 PM
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)