KQL report application availability and account for maintenance window

These days most public-facing applications are expected to be available 24/7, 365 days a year. But intranet applications often still have agreed maintenance windows and computing actual SLA for those applications requires to take those maintenance hours into account and compute SLA only for specific application hours of operation.

While for Azure alerts we can leverage Alert processing rules for Azure Monitor alerts - Azure Monitor | Microsoft Docs, taking into account hours of operation when computing application availability and response times needs to be done using custom queries.

Below provided one example how this can be easily accomplished using AppAvailabilityResults table that collects Application Insight Availability Test results.

For example lets say our application hours of operation are specified as below:

• 5:30 AM to 7:00 PM, Monday to Friday

• 5:30 AM to 3:00 PM, every second Wednesday

• 6:30 AM to 7:00 PM, Saturday

• 6:30 AM to 5:00 PM, Sunday

The query to accommodate this scenarios could look something like below:

AppAvailabilityResults 
| extend localtime = (TimeGenerated-5h)
| extend dayweek = dayofweek(localtime)
| extend hourday = hourofday(localtime)
| where 
        (hourday > 6) or (hourday==6 and datetime_part("Minute", localtime) >= 30)
     or (dayweek between (1d..5d) and (hourday > 5) or (hourday==5 and datetime_part("Minute", localtime) >= 30)) 
| where 
       (dayweek in (0d) and (hourday < 17)) 
    or (dayweek in (1d,2d,4d,5d,6d) and (hourday < 19)) 
    or
        (dayweek == 3d and 
            (hourday < 15 
             or 
             (not(dayofmonth(localtime) > 7 and dayofmonth(localtime) < 15) and (hourday < 19))
             )
         )    

The first "where" clause ensures we keep only records generated after start of operations hours.

E.g. and availability result generated 6:30am and later is to be accounted for every day:

      (hourday > 6) or (hourday==6 and datetime_part("Minute", localtime) >= 30) 

and availability result generated 5:30am and later to be accounted for Monday-Friday

     or (dayweek between (1d..5d) and 
     (hourday > 5) 
     or (hourday==5 and datetime_part("Minute", localtime) >= 30)) 

Now that filtered out records before operational hours the second "where" clause will keep only records before the end of operational hours

On Sunday we only interested in availability tests before 5pm

where 
      (dayweek in (0d) and (hourday < 17))  

Monday to Saturday (with exception of Wednesday special case) we select records before 7pm

 or (dayweek in (1d,2d,4d,5d,6d) and (hourday < 19)) 

On Wednesdays we take into account records before 7pm unless this is second Wednesday of the month then we collect only records before 3pm

    or
        (dayweek == 3d and 
            (hourday < 15 
             or 
             (not(dayofmonth(localtime) > 7 and dayofmonth(localtime) < 15) and (hourday < 19))
             )
         )  

Note: to ensure functions "dayofweek" and "hourday" work on local time we have to compute it using time zone offset (actual code of course has to take into account Daylight Savings time)

| extend localtime = (TimeGenerated-5h)

Now lets do some quick tests :

let rawdata=AppAvailabilityResults ... // use query above

rawdata
| where dayweek == 3d 
| summarize min(hourday), max(hourday), min(localtime), max(localtime) by day=dayofmonth(localtime), month=datetime_part("Month", localtime) 

We we can see that availability tests accounted for on Wednesdays from 5:30am to 7pm, unless this is second Wednesday of the month then 5:30am to 3pm