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