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

julek23 · August 12, 2022, 9:28am

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 , 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.

grant2 · August 12, 2022, 9:40am

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:

julek23 · August 12, 2022, 9:45am

Sure. Here are my 2 variables:

yosiasz · August 12, 2022, 8:02pm

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.

julek23 · August 15, 2022, 6:30am

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

yosiasz · August 15, 2022, 1:19pm

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)

julek23 · August 15, 2022, 1:40pm

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

yosiasz · August 15, 2022, 1:48pm

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

julek23 · August 15, 2022, 2:59pm

Edited the code. Please try again

julek23 · August 16, 2022, 5:14am

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?

yosiasz · August 16, 2022, 11:26am

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

julek23 · August 23, 2022, 7:37am

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

Topic		Replies	Views
Use column names of a table or view as variables Time Series Panel	0	2541	January 14, 2022
Table as variable Grafana	5	7735	January 27, 2022
Can I use column value from mysql query as panel title? MySQL	10	5612	August 2, 2024
Filter Specific to a panel	0	1744	August 12, 2020
Using query variables as dashboard name Dashboards templating	2	1332	May 25, 2022

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

Related topics