Date range with time window to do SLA statistcs

null null
New Contributor

Dear community,


i struggle around with following:


I need to write a query which displays availabilityResults within business hours and out of business hours.


Mo-Fr: 08:00 - 18:00

and out of this.


Any guess todo it?




2 Replies

Already handled SLA hours. No i struggle around with SLA_days:

let SLA_hours = range x from 8 to 18 step 1;
let SLA_days = range y from 1 to 5 step 1;
| where timestamp >= datetime('2018-03-27') and timestamp <= datetime('2018-07-05') and location == 'West Europe'
| project timestamp, ['day']=dayofweek(timestamp), ['hour']=hourofday(timestamp), success
| join kind=inner SLA_hours
on $left.hour == $right.x
| summarize count(success) by timestamp
| extend availabilty = (count_success / 12)*100
| summarize avg(availabilty) by bin(timestamp, 1d)
Problem now is, dayofweek() is a timespan which cannot be joined.
How can i do this?

The query language have rich set of functions to calculate time differences. Here is a sample calculation:

let StartDate = now()-7d;
let StartDate8am = make_datetime(getyear(StartDate),getmonth(StartDate),dayofmonth(StartDate),8,0);
let StartDate8amWorkWeek = iif(dayofweek(StartDate8am) >= 1d and dayofweek(StartDate8am) <= 5d, StartDate8am, make_datetime(getyear(StartDate8am),getmonth(StartDate8am),1,hourofday(StartDate8am),0));
print StartDate8amWorkWeek

Hope it helps,
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies