I’m using MySQL as a datasource, want to query my tickets from zammad database an display the sum of new tickets created each month. I tried and tried, but all i got was this so far
with this query:
SELECT
created_at AS ‘time’,
count(*)
FROM tickets
WHERE
$__timeFilter(created_at)
GROUP BY MONTH(created_at)
ORDER BY $__timeGroup(created_at,$__interval)
I tried to the fillvalue “previous”, which made the graph look a bit better but broke my total as it adds the filled points to my total. What i’m really trying to achieve is something like in this picture i found in another post but only by month:
https://localhost:3000/uploads/short-url/5r31BJVSnt9DTcSCYbxCcDAk5aR.png
But i’m really stuck and using Series mode as suggested in the other post doesn’t get me any further as i’m just starting with grafana. Could somebody have a heart and explain to me how i can achieve this?
Greets
Sebbo
1 Like
Got it working setting X-Axis Mode to Series, Format as Time series and modifying my sql query.
SELECT
created_at AS ‘time’,
MONTHNAME(created_at) AS ‘metric’,
count(id)
FROM tickets
WHERE
$__timeFilter(created_at)
GROUP BY MONTH(created_at)
ORDER BY created_at
4 Likes
I seem to still have a problem with that. As per below - query ran in MySQL returns expected results, but when used in Grafana (with additional $__timeFilter(created_at) ), Grafana says ‘no results’ (but doesn’t report issues with the query itself). Any ideas?
SELECT
→ created_at AS time, MONTHNAME(created_at) AS metric, count(id)
→ FROM tickets
→ GROUP BY MONTH(created_at)
→ ORDER BY created_at
→
→ ;
±--------------------±---------±----------+
| time | metric | count(id) |
±--------------------±---------±----------+
| 2019-11-20 14:04:56 | November | 3 |
| 2019-12-10 16:32:39 | December | 1 |
| 2020-01-20 14:29:58 | January | 1 |
| 2020-02-27 12:32:30 | February | 7 |
| 2020-03-20 10:23:25 | March | 5 |
±--------------------±---------±----------+
5 rows in set (0.000 sec)
You are my Savior. Perfectly Works.
