Define business hours

[sinisa_zubic]

Hi Team, 

how could I define business hours with DQL? I looked at filtering on the timestamp but can't really find a way. For example: for monday to friday, 08am to 7pm.

Best,
Sini

[educampver · Accepted solution]

Hi Sinisa, the Notebooks app has a snippet for this use case:

Here's the query anyways:

fetch bizevents, from:now()-7d, to:now()
| fieldsAdd hour=toLong(formatTimestamp(timestamp,format:"H")), day_of_week = formatTimestamp(timestamp,format:"EE")
| filterOut day_of_week == "Sat" or day_of_week == "Sun" // Remove weekend days
| filterOut hour <= 5 or hour >= 15 // Remove everything that is not between 5am and 3pm

[krzysztof_hoja]

I would suggest to use getHour() and getDayOfWeek() functions instead of formatTimestamp() and conversion.

[gdalessandro]

How would you do this using the timeseries command (using metrics)?

[krzysztof_hoja]

For timeseries you can also use getHour() and getDayOfWeek() functions, but you need first timestamps of particular buckets using start() funtion, so you can filter out irrelevant values:

timeseries {d=avg(dt.host.cpu.usage), timestamp=start()}, interval:1h
| fieldsAdd d = if(getHour(timestamp[])>=7 and getHour(timestamp[])<=16 and not in(getDayOfWeek(timestamp[]), {6,7}) , d[])
| fieldsRemove timestamp

[Julius_Loman]

While this works for "common cases", it's much more difficult to implement a calendar-based business hours considering, for example, holidays during working days or other events. 

One approach I still use with customers is a "calendar metric" representing business availability and then you can use it in the DQL to filter by multiplication.

See details of it in this post . Even though this was originally developed for classic metrics for SLO calculation purposes, it can be used with DQL / Grail in the very same way.

[krzysztof_hoja]

Yes, if you have fact of being in or out of business hours ingested in Grail as indpendent timeseries, you can use it to filter any other timeseries with data,