Forum Discussion

null null's avatar
null null
Copper Contributor
Jul 05, 2018

Date range with time window to do SLA statistcs

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?

 

Cheers,

Juergen

  • Hi,

    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,
    Meir
  • null null's avatar
    null null
    Copper Contributor

    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;
    availabilityResults
    | 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?

Resources