MySQL - Counting Log Events and Grouping them by Month

1

New to Grafana and need a little help on a MySQL query. I have a MySQL table with the following structure…

±--------------------±---------------------±-----±----±----------------------------------±---------------------+
| Field | Type | Null | Key | Default | Extra |
±--------------------±---------------------±-----±----±----------------------------------±---------------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| organizationID | varchar(18) |YES| | NULL | |
| connectionType | varchar(3) |YES| | NULL | |
| timestamp | timestamp |YES| | CURRENT_TIMESTAMP | |
±--------------------±---------------------±-----±----±----------------------------------±---------------------+

I’d like to create a time-series bar chart showing the total number of events that meet “WHERE” criteria, grouped by month showing the current month and previous eleven month in ascending order. Here’s the query I’ve tried but it failing with a Query failed message…

SELECT
UNIX_TIMESTAMP(timestamp) as time_sec,
count(*) as value,
‘Wired Connections’ as metric
FROM EventLog
WHERE $__timeFilter(timestamp) and organizationID = ‘US_VA_MCPL_BLLC’ and connectionType = ‘WRD’ and YEAR(timestamp) = ‘2020’
GROUP BY MONTH(timestamp)
ORDER BY MONTH(timestamp) ASC

Any help / direction you can provide would be appreciated!

2

I’ve made some progress. The following MySQL query in Grafana shows the correct counts in a Table…

SELECT
  COUNT(*) AS Connections
FROM EventLog
WHERE organizationID = CONCAT('US_VA_MCPL_','$branch') and connectionType = '$connection' and YEAR(timestamp) = '2020'
GROUP BY MONTH(timestamp)

How do I convert this from a table view to a time-series bar chart?