Datapoints outside time range when creating SQL query graph

Grafana
1

Hi, I have successfully connected Grafana with a MSSQL database using the Grafana-Simple-SQL-Datasource proxy.

https://github.com/gbrian/grafana-simple-sql-datasource.

I have created a single stat dashboard with KPI’s from our helpdesk system.

However I would now like to create some graphs as well.

One example query is:

select COUNT (*) from dbo.HELPDESK where GRUPP_ID IN (1,2,4,6,7,8,15,16) AND STATUSID = 20

This query shows all cases with the wanted status and groups. I can see that the query successfully pulls the current value with the current value is not graphed over time.

I get the message Data points outside time range (Can be caused by timezone mismatch or missing time filter in query).

Any ideas?

In the future I would like to have start and end time as variables to the graph can be changed on request.

2

Specify table format in metrics and options tab.

3

Thanks Torkel for the reply, but the suggestion did not work. I have attached some screenshots of my settings.

Legend
Legend880×330 13.7 KB
Axes
Axes1000×432 15.3 KB
Metrics
Metrics995×324 15.4 KB
Display
Display1142×368 19.2 KB
Graph
Graph1117×449 14.2 KB

4

please read docs specify a query that returns time series. When you add a new query the query editor will generate an example query for how a time series query needs to look. Needs to return time_sec, value and metric columns.

5

Update, I haven’t looked into this in a while and I have now got a query that does what I want. However I still need to declare the timefilter some how but have no idea how to do it with MSSQL.

Here is my query:

SELECT COUNT(DATEPART(yy, REGDATUM)) AS Total,
DATEPART(yy, REGDATUM) AS Year,
DATEPART(mm, REGDATUM) AS Month,
DATEPART(DD, REGDATUM) AS Day
FROM dbo.HELPDESK where GRUPP_ID IN (1,2,4,6,7,8,15,16) AND HELPDESK.STATUSID IN (12,3,16,20,21,1,14,9,0)
GROUP BY DATEPART(yy, REGDATUM), DATEPART(mm, REGDATUM), DATEPART(DD, REGDATUM)
ORDER BY Year DESC, Month DESC, Day DESC

It produces a table and graph with no problem however all entries on the graph is dated 1970-01-01.

Any more ideas would be appreciated.

6

make a query like this:
SELECT
([TimeStamp]) time,
metric1,metric2,…
FROM [Grafana].[dbo].[table]
where $__timeFilter([TimeStamp])

7

Use “getdate() as time” for the time