Hi! I am having trouble figuring out how to get my data to display the way I’m wanting to in a table. I have a table with this query formatted as a time series.
SELECT COUNT(DISTINCT ft.ticketnumber) AS count, IF(ft.tags LIKE '%migration%', 'migration', IF(ft.tags LIKE '%plus%' OR ft.tags LIKE '%V35%', 'plus', 'None')) AS tag, NOW() AS time, ft.cf_reported_issue FROM freshdesk_tickets AS ft WHERE ft.spam = 0 AND ft.deleted = 0 AND UNIX_TIMESTAMP() > ft.created_at AND (ft.cf_reported_issue = 'Reference' OR ft.cf_reported_issue = 'Internal') GROUP BY tag, ft.cf_reported_issue HAVING (tag = 'migration' OR tag = 'plus' OR tag = 'None')
The idea is to display the number of freshdesk tickets (stored in a mysql db) that have a specific tag and reported issue combo (e.g reference issue migration tag, reference issue no tag, internal issue plus tag etc.), that existed on every day in the selected time range in Grafana. The important thing is that I don’t just want the tickets that were created on that date, I need to count whether a ticket was created on or before that date.
I have it displaying one row correctly with a dummy NOW() and UNIX_TIMESTAMP() for the dates, but am not certain where to go from here.
Any help would be hugely appreciated, thanks!