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
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
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
How would you do this using the timeseries command (using metrics)?
I would suggest to use getHour() and getDayOfWeek() functions instead of formatTimestamp() and conversion.
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
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.
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,