HeatMap by Day's and hours (not date)

Grafana Dashboards
, , ,
1

I almost have a working heatmap that splits hourly occourances by day (not date). I cannot figure out how to smush the graph together so it looks as I need it too, anyone have an idea? Why is this not an option yet, why can we not declare our own x’s and y’s on all the built in charts, only controling x is so frustrating :frowning: .

My time slice is selected for the last 90 days

image
image1894×492 46.1 KB

  • What Grafana version and what operating system are you using?
    9.5.1
    Below is the code I am using with a Status history graph.

SELECT
Created
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘0’ THEN 1 ELSE 0 END) AS ‘00:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘1’ THEN 1 ELSE 0 END) AS ‘01:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘2’ THEN 1 ELSE 0 END) AS ‘02:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘3’ THEN 1 ELSE 0 END) AS ‘03:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘4’ THEN 1 ELSE 0 END) AS ‘04:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘5’ THEN 1 ELSE 0 END) AS ‘05:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘6’ THEN 1 ELSE 0 END) AS ‘06:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘7’ THEN 1 ELSE 0 END) AS ‘07:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘8’ THEN 1 ELSE 0 END) AS ‘08:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘9’ THEN 1 ELSE 0 END) AS ‘09:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘10’ THEN 1 ELSE 0 END) AS ‘10:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘11’ THEN 1 ELSE 0 END) AS ‘11:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘12’ THEN 1 ELSE 0 END) AS ‘12:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘13’ THEN 1 ELSE 0 END) AS ‘13:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘14’ THEN 1 ELSE 0 END) AS ‘14:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘15’ THEN 1 ELSE 0 END) AS ‘15:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘16’ THEN 1 ELSE 0 END) AS ‘16:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘17’ THEN 1 ELSE 0 END) AS ‘17:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘18’ THEN 1 ELSE 0 END) AS ‘18:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘19’ THEN 1 ELSE 0 END) AS ‘19:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘20’ THEN 1 ELSE 0 END) AS ‘20:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘21’ THEN 1 ELSE 0 END) AS ‘21:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘22’ THEN 1 ELSE 0 END) AS ‘22:00’
, SUM(CASE WHEN EXTRACT(HOUR FROM created) = ‘23’ THEN 1 ELSE 0 END) AS ‘23:00’
, CASE DAYOFWEEK(created)
WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
END AS day
FROM table
WHERE TenantId = ‘${TenantId}’
AND $__timeFilter(Created)
Group BY DAYOFWEEK(Created)

2

From your screenshot it looks like you are using the ‘Status History’ Panel?
What happens if you use the ‘State timeline’ instead and settings from below.

Image 73

3

image
image1877×457 54.7 KB

Its practically the same. And I cannot for the life of me figure out why its reversing Saturday and Sunday in all of the visulazations lol.

4

That wasn’t the solution i guess. :sweat_smile:
Possible to make a screenshot of the ‘table view’ ?

As for the Saturday and Sunday. Cant you use Transform → Organize fields for that?
Or Transform → Sort By?

5

image
image1808×386 23.4 KB

Theres part of the table view, and the other end:
image

As far as organize:
image
Day is not split out like hours are

6

I think the problem lies in the column ‘Created’. Which contains dates + times.
As I understood correctly from your post. your main goal was to get insight for the weekdays (of your selection)

I don’t know how it works with SQL but maybe this can still help you. I’m using influx myself. and there is a command drop() function. Which i use at the very end of my query.

Link: drop() function | Flux 0.x Documentation

So you have the values for all the days (of your range) but not the timestamps which triggers the ‘Status History’ panel. If that makes any sense.

7

Maybe you want more control by using this plugin?

image
image1333×693 58.9 KB

8

or this

9

What plugin is this? Is it installed via JSON? the way our instance is setup, we cannot install plugins that are not just JSON :frowning:

10

not sure what that means installed via json? Can you show us this method. the 2 plugin I showed you are from Volkov Labs @mikhailvolkov

Dynamic Text

Apache eChart

1 Like
11

image
image1090×565 25.7 KB

12

I did some testing and used a regular ‘table panel’ in the end with some styling for the cells (background color) for this approach. For this I grepped the ‘time’ part from the full date + time structure and converted this to strings like “01:00:00” “02:00:00” etc… and dropped the “_time” column at the end so there is no official time column present anymore.

The example on the right is based on the “Hourly Heatmap” panel and uses the “_time” column based on dateTime:RFC3339 as input

For better viewing right click on the image and open in ‘new tab’


heatmap_vs_heatmap

2 Likes
13

Thank you for this idea! I have gotten a table setup like the example on the left, I am however having a hard time getting the days of the week to be the X (listed on top of the chart) with the hours on the Y. May I ask how you achieved this? I seem to only be able to get the hours on the X and days on the Y.

1 Like
14

@tekwhat
I will check this for you tonight

1 Like
15

I finally got the solution

image
image1217×758 197 KB

SELECT
CASE EXTRACT(HOUR FROM (CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)))
WHEN 0 THEN ‘00:00’
WHEN 1 THEN ‘01:00’
WHEN 2 THEN ‘02:00’
WHEN 3 THEN ‘03:00’
WHEN 4 THEN ‘04:00’
WHEN 5 THEN ‘05:00’
WHEN 6 THEN ‘06:00’
WHEN 7 THEN ‘07:00’
WHEN 8 THEN ‘08:00’
WHEN 9 THEN ‘09:00’
WHEN 10 THEN ‘10:00’
WHEN 11 THEN ‘11:00’
WHEN 12 THEN ‘12:00’
WHEN 13 THEN ‘13:00’
WHEN 14 THEN ‘14:00’
WHEN 15 THEN ‘15:00’
WHEN 16 THEN ‘16:00’
WHEN 17 THEN ‘17:00’
WHEN 18 THEN ‘18:00’
WHEN 19 THEN ‘19:00’
WHEN 20 THEN ‘20:00’
WHEN 21 THEN ‘21:00’
WHEN 22 THEN ‘22:00’
WHEN 23 THEN ‘23:00’
END AS Hours

, SUM(CASE WHEN DAYOFWEEK(created) = 1 THEN 1 ELSE 0 END) AS ‘Sunday’
, SUM(CASE WHEN DAYOFWEEK(created) = 2 THEN 1 ELSE 0 END) AS ‘Monday’
, SUM(CASE WHEN DAYOFWEEK(created) = 3 THEN 1 ELSE 0 END) AS ‘Tuesday’
, SUM(CASE WHEN DAYOFWEEK(created) = 4 THEN 1 ELSE 0 END) AS ‘Wednesday’
, SUM(CASE WHEN DAYOFWEEK(created) = 5 THEN 1 ELSE 0 END) AS ‘Thursday’
, SUM(CASE WHEN DAYOFWEEK(created) = 6 THEN 1 ELSE 0 END) AS ‘Friday’
, SUM(CASE WHEN DAYOFWEEK(created) = 7 THEN 1 ELSE 0 END) AS ‘Saturday’

Group by Hours
Order by Hours

1 Like
16

I just wanted to come back here and share some stuff I will start a new post for another question pertaining to this.

image
image1024×872 59.2 KB

A co-worker and I were able to get a dummy table in grafana.

It must be the first query:

SELECT

CASE

    WHEN hr = 0 THEN '12:00 AM'

    ELSE CAST(hr AS CHAR)

END AS 'Time Bucket',

MAX(CASE WHEN day = 'Monday' THEN 0 ELSE NULL END) AS `Monday`,

MAX(CASE WHEN day = 'Tuesday' THEN 0 ELSE NULL END) AS `Tuesday`,

MAX(CASE WHEN day = 'Wednesday' THEN 0 ELSE NULL END) AS `Wednesday`,

MAX(CASE WHEN day = 'Thursday' THEN 0 ELSE NULL END) AS `Thursday`,

MAX(CASE WHEN day = 'Friday' THEN 0 ELSE NULL END) AS `Friday`,

MAX(CASE WHEN day = 'Saturday' THEN 0 ELSE NULL END) AS `Saturday`,

MAX(CASE WHEN day = 'Sunday' THEN 0 ELSE NULL END) AS `Sunday`

FROM (

SELECT '12:00 AM' AS hr UNION SELECT '1:00 AM' UNION SELECT '2:00 AM' UNION SELECT '3:00 AM' UNION

SELECT '4:00 AM' UNION SELECT '5:00 AM' UNION SELECT '6:00 AM' UNION SELECT '7:00 AM' UNION

SELECT '8:00 AM' UNION SELECT '9:00 AM' UNION SELECT '10:00 AM' UNION SELECT '11:00 AM' UNION

SELECT '12:00 PM' UNION SELECT '1:00 PM' UNION SELECT '2:00 PM' UNION SELECT '3:00 PM' UNION

SELECT '4:00 PM' UNION SELECT '5:00 PM' UNION SELECT '6:00 PM' UNION SELECT '7:00 PM' UNION

SELECT '8:00 PM' UNION SELECT '9:00 PM' UNION SELECT '10:00 PM' UNION SELECT '11:00 PM'

) AS hours

CROSS JOIN (

SELECT 'Monday' AS day UNION SELECT 'Tuesday' UNION SELECT 'Wednesday' UNION

SELECT 'Thursday' UNION SELECT 'Friday' UNION SELECT 'Saturday' UNION SELECT 'Sunday'

) AS days

GROUP BY hr
ORDER BY STR_TO_DATE(hr, ‘%l:00 %p’);

Then your second query is your data:

SELECT all_hours.est_hour as ‘Time Bucket’,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)) = 1 THEN 1 ELSE 0 END), 0) AS Sunday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)) = 2 THEN 1 ELSE 0 END), 0) AS Monday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)) = 3 THEN 1 ELSE 0 END), 0) AS Tuesday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)) = 4 THEN 1 ELSE 0 END), 0) AS Wednesday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)) = 5 THEN 1 ELSE 0 END), 0) AS Thursday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)) = 6 THEN 1 ELSE 0 END), 0) AS Friday,
COALESCE(SUM(CASE WHEN DAYOFWEEK(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’)) = 7 THEN 1 ELSE 0 END), 0) AS Saturday
FROM (
SELECT DISTINCT coalesce(DATE_FORMAT(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’), ‘%l:00 %p’)) AS est_hour
FROM ‘table’
) all_hours
LEFT JOIN ‘table’ ON all_hours.est_hour = DATE_FORMAT(CONVERT_TZ(created, ‘+00:00’, ‘-04:00’), ‘%l:00 %p’)
WHERE $__timeFilter(created)
GROUP BY coalesce(all_hours.est_hour)
ORDER BY STR_TO_DATE(all_hours.est_hour,‘%l:00 %p’) asc;

At this point, we have to go to transforms:

Join the two tables then merge the corrisponding day’s:

image
image1510×523 41.9 KB

Then use orginize and hide everything but your hours and the combined day’s:

image
image707×219 17.3 KB

Finally rename your combined day’s
image

This should give you a heatmap, with all hours/days reperesented reguardless of data or not.

3 Likes
17

Thanks for providing this explanation. I would like to know which panel did you use to create the heatmap. I tried the built in one from Grafana, but it wouldn’t work.

18

You should be able to achieve this with the normal ‘Table’ Panel.
You can style the different cells with colors depending on value or state.

e.g. Value mapping, Tresholds or Field Overide.

1 Like
19

Thanks it works now.

2 Likes
20

great!

1 Like