Filter data by column

Grafana MySQL
1

Is it possible to filter data by a column not by row?

I have some data from an external mysql database of which I have no control to fix the data formatting. As a result my data is set with a TimeStamp column then a series of columns named ‘Prog1’,‘Prog2’,‘Prog3’,‘Prog4’,…etc…

I am trying to return the numeric values for the given time range for a single column only, so that I can present this as a line graph.

image
image1180×326 25.6 KB

image
image1848×378 130 KB

This is what i Have so far, but this cannot be filtered by the variables.

Can anyone offer any advice on how to tackle this better.

Thanks

image

2

Update:

DECLARE @DynamicColumnName NVARCHAR(MAX)
SET @DynamicColumnName = 'Prog' + CAST($Program AS NVARCHAR(MAX))

DECLARE @SqlQuery NVARCHAR(MAX)
SET @SqlQuery = N'SELECT TimeDate AS time, ' + QUOTENAME(@DynamicColumnName) + N' FROM AGV.dbo.AGVeipALL WHERE TimeDate BETWEEN @StartTime AND @EndTime'

-- Replace [start_time] and [end_time] with your desired time range
DECLARE @StartTime DATETIME = '2024-03-01 00:00:00'
DECLARE @EndTime DATETIME = '2024-03-31 23:59:59'

EXEC sp_executesql @SqlQuery, N'@StartTime DATETIME, @EndTime DATETIME', @StartTime, @EndTime

This is my best so far. it works, but I cant seem to replace the fixed times with the variable $__timeFilter(TimeDate)

Can anyone offer any help ?

3

Please post schema structure of AGV.dbo.AGVeipALL DDL

Though your question is more sql than grafana, lets see what we can do

4

Hi. Thanks for the response. I am still very new to this, so not sure what you are asking for, but I get this from the inspector.

[
{
“schema”: {
“refId”: “A”,
“meta”: {
“executedQueryString”: "SELECT TOP(50) * FROM AGV.dbo.AGVeipALL "
},
“fields”: [
{
“name”: “TimeDate”,
“type”: “time”,
“typeInfo”: {
“frame”: “time.Time”
},
“config”: {}
},
{
“name”: “EIPcount”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog1”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog2”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog3”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog4”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog5”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog6”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog7”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog8”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog9”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog10”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog11”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog12”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog13”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog14”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog15”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog16”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog17”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog18”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog19”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog20”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog21”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog22”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog23”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog24”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog25”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog26”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog27”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog28”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog29”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog30”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog31”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog32”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog33”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog34”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog35”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog36”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog37”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog38”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog39”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog40”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog41”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog42”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog43”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog44”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog45”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog46”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog47”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog48”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog49”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog50”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog51”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog52”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog61”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
},
{
“name”: “Prog62”,
“type”: “string”,
“typeInfo”: {
“frame”: “string”
},
“config”: {}
}
]
},
“data”: {
“values”: [
[
]
]
}
}
]

5

The column names of this table

6

Hi

TimeDate, Prog1, Prog2, Prog3, … Prog60

all the Prog# columns are numeric values
the TimeDate column is the datestamp of the event with one record per 10 minutes.

I think I have it working now as required, but my SQL is very poor and I dont seem to be able to integrate the Grafana function

$__timeFilter(TimeDate)

so I am still using a fixed time to prove the concept.

DECLARE @DynamicColumnName NVARCHAR(MAX)

SET @DynamicColumnName = 'Prog' + CAST($Program AS NVARCHAR(MAX))

DECLARE @SqlQuery NVARCHAR(MAX)

SET @SqlQuery = N'SELECT TimeDate AS time, CAST(' + QUOTENAME(@DynamicColumnName) + N'as INTEGER) FROM AGV.dbo.AGVeipALL WHERE TimeDate BETWEEN @StartTime AND @EndTime'

-- Replace [start_time] and [end_time] with your desired time range

DECLARE @StartTime DATETIME = '2024-03-01 00:00:00'

DECLARE @EndTime DATETIME = '2024-03-31 23:59:59'

EXEC sp_executesql @SqlQuery, N'@StartTime DATETIME, @EndTime DATETIME', @StartTime, @EndTime

This draws the correct chart, but is not dynamic like the rest of the dashboard.

image
image1812×762 183 KB

As always, thank you for the continued support.

1 Like
7

Not really a grafana issue best to post to a mssql or mysql forum

But until then why was this designed as a pivoted design approach? Progn+1

Will there be more progs in the future?

Dynamic query are a security issue for possible sql injection and horrible at performance, use stored procedures instead and use unpivot

I would redesign the schema

But all that is anothetlr conversation for another time

But let’s try to unpivot and eliminate dynamic query. Give me a minute for :coffee:

8

Thanks for the advice.
unfortunately at this time I have no control over the table structure. This concern will be addressed in the future, but until then, I just need to get some Grafana dashboards up and running to best demonstrate the power to other departments within my company.

1 Like