Group by hours of the day with complete listing of hours

[KA]

Hi Dynatrace Community,

Seeking some guidance on a DQL that presently fetches data for the past 28 days and groups by hours of the day which shows us the peak usage hours for our web application for a period of time. This works well however there are gaps in the hours of the day (no usage) which results from periods that has zero entries.

We want to ensure that all hours of the day (00-23) are included, even if they have zero entries.  Below is the DQL and feel perhaps we should look utilise maketimeseries instead. #stilllearning

fetch spans, from: now() - 28d, to: now() - 1d
	| fieldsAdd hourOfDay = formatTimestamp(start_time, format: "HH")
	| summarize count = count(), by: { hourOfDay }    
	| sort hourOfDay asc
	| fields hourOfDay, count

[sinisa_zubic]

Hi @KA 

yes you should go the makeTimeseries way. 

What do you think about this query

fetch spans, from: -28d
| makeTimeseries { count = count(), start = start() }, interval:1h
| fields count = record(count = count[], timestamp = start[])
| expand count
| fields count = count[count], timestamp = formatTimestamp(count[timestamp], format: "HH")
| summarize { count = sum(count)}, by: { timestamp }
| sort timestamp asc

Best,
Sini

[KA]

Thanks @sinisa_zubic for your assistance. Have tested the DQL and the results include all hours of the day (00-23), including entries with zero logs - much appreciated and a great solution!

Of note, when presenting this in a Table visualization we see all hours of the day, however if we change the presentation to Categorical (bar layout), the zero entry periods are hidden - included a visual below for reference. To overcome this, we set the count default (0.01) with rounding.

Below is an alternative approach we found via a Community thread, however when presenting Categorical the x-axis label included the current date i.e. Feb 18 ... pref. your provided solution

| fieldsAdd timestamp = start_time - start_time@d + @d

// group by hour + set default for zero entries
| makeTimeseries count = count(default: 0), from: @d, to: @d+1d, interval: 1h