IPAM style (non-timebased) heatmap

  • Grafana: 9.2.3

  • Trying to visualise IP address consumption in network (CIDR) blocks

  • I was hoping to use a heatmap

  • All panels I’ve so far found are time based

  • Hoping to find a panel which lets me create a heat map based on a simple table containing two columns: network, host. To show which address in a network is in use and which is free.

  • Config issue: I’m unable to find a suitable panel for the data I’m trying to visualise.

  • Errors: n/a

My input is from a SQL query against a FreeRADIUS database. It gives me the following table:

SELECT DISTINCT SUBSTRING_INDEX(radreply.value,'.',3)AS 'netw',
SUBSTRING_INDEX(radreply.value,'.',-1)AS 'host'
FROM radcheck
INNER JOIN radreply USING (username)
INNER JOIN radacct USING (username)
WHERE acctstoptime IS NULL
AND radreply.attribute LIKE 'Framed-IP-Address'
ORDER BY netw, cast(host as unsigned)

netw   	host	
12.14.14	2	
12.14.14	3	
12.14.14	4	
12.14.14	5	
12.14.14	6	
12.14.14	7	
12.14.14	8	
12.14.14	9	
12.14.14	10	
12.14.14	11	
12.14.14	12	
12.14.14	13	
12.14.14	14	
12.14.14	15

image

Any idea, about a panel that I might be able to use for this? I already graph the total free and used addresses in each network block. But for this panel I’d like to show each address represented by a visual representation. And I’ll need to also show the missing ones, so a formatted table won’t work and is too big.

I could potentially add a column with each host address in a network, and then pivot the table so that there are two rows per network, or row for used addresses and the other for all addresses in the network. That I could display in a table, but feels very hackish.

1 Like