Use variables to filter data and use varibale name as metric name

Hey everyone,

hope I can explain what I want to archive. My target is to create dynamic panels on the users input.

First of all this is my SQL Database. I have one table with Drive IDs and their names and one table with all the drive data. This table hast the following columns:

Drive_ID | Period | TimeStamp | Torque_Average | Torque_Peak_Pos | ... and some more.

My idea is that the user can choose which drive he wants to see and which data from these drives.

For the ID I use a variable $drive_no with this query:

SELECT description AS "__text", id AS "__value" from (SELECT * FROM dbo.DriveInfo) x(id,description);

For the data I use the variable $data_type with query:

Select queryTag as "__text", description as "__value" from (SELECT objname, CONVERT(varchar(50), value) FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'DriveData', 'column', NULL)) x(description,queryTag);

Which gives me this table as result:
SQLQuery

In my panel I use now this query for the data:

SELECT 
  TimeStamp/1000 as time,
  ${data_type:raw}
FROM dbo.DriveData
WHERE Drive_ID = $drive_no
ORDER BY TimeStamp ASC;

This is working very well. I get my panels with the choosen parameters. Here’s an example:

But now I come to my question, finally :slight_smile:, I want to have the datavalues as the description from my table. So If I load the data manually I could write Select Torque_Average as “Torque Avg” or something like this. But how could I get this when using a variable?

I thought of something like Select [${data_type:raw}] as [${data_type}] but this doesn’t work. Is there any way to do this?

Or could I just replace the “_” with a space? This would result in the same.

Currently on the bottom all the variables have “_” it its name. It’s no big issue but I want to know If this is doable.

Welcome @julek23 to the Grafana forum.

Can you post a screenshot of each of your two variables config (incl. the preview of values) that you created in Grafana? Something like this:

Sure. Here are my 2 variables:


Nice requirements write up but could you please post proper DDL and DML. This will help us help you because we will have your data in our database.

create table DriveInfo_Sample(
Drive_ID  int, --?
Period int, --?
)

--etc

insert into DriveInfo_Sample
values(1, 1)
--etc

as much data as you can, if there is sensitive data then you can just use bogus data.
Without sample data it would just be guess work.

Good morning,

here is a script of the current database I work with. It has only testdata.

Can’t post it here, maybe too big. So I uploaded it: HiDrive

Sorry, not going to click some random url. please post the sample data as show in below DDL and DML?

create table DriveInfo_Sample(
Drive_ID  int, --?
Period int, --?
)

--etc

insert into DriveInfo_Sample
values(1, 1)

It’s just a hidrive link. But here you go. If you need more data you need to download the script.
Answers are limited to 32k chars.

CREATE TABLE [dbo].[Periods](
	[Period] [int] NOT NULL,
	[Description] [varchar](40) NULL,
 CONSTRAINT [PK_Periods] PRIMARY KEY CLUSTERED 
(
	[Period] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DriveInfo](
	[Drive_ID] [int] NOT NULL,
	[Description] [varchar](40) NULL,
 CONSTRAINT [PK_MotorInfo] PRIMARY KEY CLUSTERED 
(
	[Drive_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MaschinePressure](
	[Period] [int] NOT NULL,
	[TimeStamp] [bigint] NOT NULL,
	[Pressure] [real] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DriveData](
	[Drive_ID] [int] NOT NULL,
	[Period] [int] NOT NULL,
	[TimeStamp] [bigint] NOT NULL,
	[Torque_Average] [real] NULL,
	[Torque_Peak_Pos] [real] NULL,
	[Torque_Peak_Pos_Position] [real] NULL,
	[Torque_Peak_Neg] [real] NULL,
	[Torque_Peak_Neg_Position] [real] NULL,
	[Machine_Speed] [int] NULL,
	[Drive_Velocity] [real] NULL,
	[Drive_Temperature] [real] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (5, 1, 1659943709909, 5.94001, 6.090011, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (6, 1, 1659943709909, 5.94001, 6.090011, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (7, 1, 1659943709909, 5.94001, 6.090011, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (8, 1, 1659943709909, 5.94001, 6.090011, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (9, 1, 1659943709909, 5.94001, 6.090011, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (10, 1, 1659943709909, 5.94001, 6.090011, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (11, 1, 1659943709909, 5.94001, 6.090011, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (1, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (3, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (4, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (5, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (6, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (7, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (8, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (9, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (10, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (11, 1, 1659943711009, 6.27015, 6.42001343, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (1, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (3, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (4, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (5, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (6, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (7, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (8, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (9, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (10, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (11, 1, 1659943712109, 6.600097, 6.75001574, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (1, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (3, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (4, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (5, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (6, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (7, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (8, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (9, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (10, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (11, 1, 1659943713208, 6.98453, 7.48001766, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (1, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (3, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (4, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (5, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (6, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (7, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (8, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (9, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (10, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (11, 1, 1659943714309, 8.860016, 10.0100136, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (1, 1, 1659943715409, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (3, 1, 1659943715409, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (4, 1, 1659943715409, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (6, 1, 1659943715410, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (7, 1, 1659943715410, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (8, 1, 1659943715410, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (9, 1, 1659943715410, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (10, 1, 1659943715410, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (11, 1, 1659943715410, 11.3910675, 12.5400085, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (1, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (3, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (4, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (5, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (6, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (7, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (8, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)
INSERT [dbo].[DriveData] ([Drive_ID], [Period], [TimeStamp], [Torque_Average], [Torque_Peak_Pos], [Torque_Peak_Pos_Position], [Torque_Peak_Neg], [Torque_Peak_Neg_Position], [Machine_Speed], [Drive_Velocity], [Drive_Temperature]) VALUES (9, 1, 1659943716509, 13.9200058, 15.0700035, 349.63, -20.47, 120.83, 60, 59.98, 47.11)

INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (1, N'Drive 1')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (2, N'Drive 2')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (3, N'Drive 3')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (4, N'Drive 4')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (5, N'Drive 5')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (6, N'Drive 6')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (7, N'Drive 7')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (8, N'Drive 8')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (9, N'Drive 9')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (10, N'Drive 10')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (11, N'Drive 11')
INSERT [dbo].[DriveInfo] ([Drive_ID], [Description]) VALUES (12, N'Drive 12')
GO
INSERT [dbo].[Periods] ([Period], [Description]) VALUES (1, N'Cycle')
INSERT [dbo].[Periods] ([Period], [Description]) VALUES (2, N'DayAverage')
INSERT [dbo].[Periods] ([Period], [Description]) VALUES (3, N'WeekAverage')
INSERT [dbo].[Periods] ([Period], [Description]) VALUES (4, N'MonthAverage')
INSERT [dbo].[Periods] ([Period], [Description]) VALUES (5, N'YearAverage')
GO
ALTER TABLE [dbo].[DriveData]  WITH CHECK ADD  CONSTRAINT [FK_DriveTorqueAverage_DriveInfo] FOREIGN KEY([Drive_ID])
REFERENCES [dbo].[DriveInfo] ([Drive_ID])
GO
ALTER TABLE [dbo].[DriveData] CHECK CONSTRAINT [FK_DriveTorqueAverage_DriveInfo]
GO
ALTER TABLE [dbo].[DriveData]  WITH CHECK ADD  CONSTRAINT [FK_DriveTorqueAverage_Periods] FOREIGN KEY([Period])
REFERENCES [dbo].[Periods] ([Period])
GO
ALTER TABLE [dbo].[DriveData] CHECK CONSTRAINT [FK_DriveTorqueAverage_Periods]
GO
ALTER TABLE [dbo].[MaschinePressure]  WITH CHECK ADD  CONSTRAINT [FK_MaschinePresure_Period] FOREIGN KEY([Period])
REFERENCES [dbo].[Periods] ([Period])
GO
ALTER TABLE [dbo].[MaschinePressure] CHECK CONSTRAINT [FK_MaschinePresure_Period]
GO
EXEC [SN_Datalog].sys.sp_addextendedproperty @name=N'MS_Description', @value=NULL 
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Torque Average' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Torque_Average'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Torque Peak Positiv' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Torque_Peak_Pos'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Torque Peak Positiv Position' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Torque_Peak_Pos_Position'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Torque Peak Negativ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Torque_Peak_Neg'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Torque Peak Negativ Position' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Torque_Peak_Neg_Position'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Machine Speed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Machine_Speed'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Drive Velocity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Drive_Velocity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Drive Temperature' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DriveData', @level2type=N'COLUMN',@level2name=N'Drive_Temperature'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Pressure' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MaschinePressure', @level2type=N'COLUMN',@level2name=N'Pressure'
GO
1 Like

full of errors when run on my sql server. please test locally first before posting?

Edited the code. Please try again

One way is select All for select data then rename using transformation

But this assumes you will not have any new Torque related fields in the future.

Also imho it is a bit overboard to use extended properties which makes this hard to maintain and as we can see here, hard to use within grafana.

create a view on that table

alter view vw_turquoise
as
SELECT TimeStamp,
       Drive_ID as 'Drive ID',
       Torque_Average as 'Torque Average',
	   Torque_Peak_Pos as 'Torque Peak Pos',
	   Torque_Peak_Pos_Position as 'Torque Peak Pos Position'
	   ,Torque_Peak_Neg as 'Torque Peak Neg',
	   Torque_Peak_Neg_Position as 'Torque Peak Neg Position',
	   Machine_Speed as 'Machine Speed',
	   Drive_Velocity as 'Drive Velocity',
	   Drive_Temperature 'Drive Temperature'
FROM dbo.DriveData
go

change your data type variable

select concat('[', c.name,']') as "__value", c.name as "__text" 
  from sys.objects o, sys.columns c 
 where o.object_id = c.object_id 
     and o.name = 'vw_turquoise'

change your query to use in

SELECT 
  TimeStamp/1000 as time,
  ${data_type:raw}
FROM dbo.vw_turquoise
WHERE [Drive ID] in ($drive_no)
ORDER BY TimeStamp ASC;

image

image

I like the second way more as I only have to change the columns in the database.
The problem now is that I can choose the timestamp and drive id also. Some way to hide these 2?

image

The secret is in this variable builder. I will let you figure it :fish:

select concat(‘[’, c.name,‘]’) as “__value”, c.name as “__text”
from sys.objects o, sys.columns c
where o.object_id = c.object_id
and o.name = ‘vw_turquoise’

No changes were made to columns in the database. Only names in the view

I mean, I only have to change the columns in the database if we track more data. And I dont have to adjust the dashboard also.

Your solution works good. Thanks a lot :+1:

1 Like