Hello! First poster here, and new to time series. I’ve been making MSSQL table queries for a while in Grafana, but Time Series always eluded me.
I have a question somewhat similar to @nfonz23 post (Graph Panel as Series - MSSQL). I recognize the table name from the support desk software he’s using. My goal is to plot the daily total number of tickets created on a given service board.
The column values I’m interested in are:
[Date_Entered_UTC] - the date the ticket was created
[SR_Service_RecID] - the ticket number
[Board_Name] - the name of the service board, for filtering purposes (I’ll make a variable of this)
The table name in this case is:
v_api_collection_service_ticket (which is a mirror of @nfonz23 v_rpt_service table)
The data plot should be:
Y-Axis - Count of tickets created, per day (every 24h)
X-Axis - Date
I’m also factoring in Grafana’s time range selection in the browser window. I know that part of the SQL code works as I use it in many other Table queries.
The (bad) query that I came up with:
SELECT [Date_Entered_UTC] as time, CONVERT(CHAR(8),[SR_Service_RecID]) AS metric FROM v_api_collection_service_ticket WHERE [Board_Name] like 'Support' AND (Date_Entered_UTC BETWEEN '${__from:date:iso}' AND DATEADD(DAY, 1,'${__to:date:iso}')) GROUP BY [Date_Entered_UTC],[SR_Service_RecID] ORDER BY time desc
…Which in MSSMS yields data that looks like:
| # | time | metric |
|---|---|---|
| 1 | 2021-05-06 22:40:35.840 | 213918 |
| 2 | 2021-05-06 22:29:41.097 | 213915 |
| 3 | 2021-05-06 22:16:01.177 | 213911 |
…and Grafana says (Time Series):
…and Grafana also says (Table):
Any help with making my brain understand this better would be helpful. I feel like I’m not understanding the values that go into representing the data, then grouping it such that the values can be plotted on an x/y chart.