SQL Server Time Series Graphing

1

Hi,

Hoping someone can help point out where i am going wrong here.
I have a db that looks like this:

[ID] [int] IDENTITY(1,1) NOT NULL,
[device] [nvarchar](100) NULL,
[oid] [nvarchar](100) NULL,
[description] [nvarchar](100) NULL,
[mtu] [int] NULL,
[bandwidth] [bigint] NULL,
[adminstatus] [int] NULL,
[operstatus] [int] NULL,
[int_inpkt] [bigint] NULL,
[int_inerr] [bigint] NULL,
[int_outpkt] [bigint] NULL,
[int_outerr] [bigint] NULL,
[time] [datetime] NULL

[tb_grafana_network_devices] ADD  DEFAULT (getdate()) FOR [time]

I can’t seem to workout the sql query for time series data.
Right now i can get all my interfaces showing as metrics and i can get a single value showing for each. No lines get graphed and no matter how far left or right i go on the graph the values of each metric stay the same. (when i put in the $__timeFilter(time) it breaks and i lose my metrics)

SELECT
time,
description as metric,
int_inpkt
From tb_grafana_network_devices

I have tried duplicating the examples in the grafana sql doc
specifically this one:

CREATE TABLE metric_values (
time datetime,
measurement nvarchar(100),
valueOne int,
valueTwo int,
)

INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES(‘2018-03-15 12:30:00’, ‘Metric A’, 62, 6)
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES(‘2018-03-15 12:30:00’, ‘Metric B’, 49, 11)

INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES(‘2018-03-15 13:55:00’, ‘Metric A’, 14, 25)
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES(‘2018-03-15 13:55:00’, ‘Metric B’, 48, 10)

SELECT
time,
valueOne,
measurement as metric
FROM
metric_values
WHERE
$__timeFilter(time)
ORDER BY 1

and it actually doesnt produce me a graph or metric a/b so i am wondering if maybe i am missing some config or something else…

I am running on windows server 2012r2.

2

SELECT
$__time(time),
valueOne,
measurement as metric
FROM
metric_values
WHERE
$__timeFilter(time)
ORDER BY 1

Try that. I’m assuming the issue is that you need to specify the column that is the time column, at least that is how I query time series data for my panels.

3

So i haven’t made any changes, my dataset has just increased from 5000 rows to 112,000 rows and suddenly i am seeing graphs. I tried the above damienclark as that is the example given in the docs and it wasn’t plotting either.

I don’t believe it was the query as mine which was broken is now working and graphing. In the query inspector i was seeing array results and key:value pairs. Is there any requirements for graphing minimum dataset or anything… system times where the sql/grafana instance are located…?

4

Just to add. I stopped pushing data into my db and 30 minutes later i am still seeing data graphed until the current time. I have no idea where its getting data from which doesn’t exist in the db. This has me thinking there is some time configuration which is out by a certain amount of time…