I’ve been experimenting with Grafana for visualization of my historical data and I see two limitations - at least for my skillset. The historical data is split in monthly tables. While I can use a JOIN or UNION of existing past month tables, I am not really sure how to add future monthly tables that do not exist yet. And while I can query for a specific tagid, there is no guarantee that that id will not be retired over time and replaced by a new id. So I would need to query for the specific tagpath and keep track of the retirements periods…
Has anybody found a way to deal with those limitations yet?
Can you point Grafana at a web API instead of a DB? Then you can expose an API with the WebDev module and have its implementation query tag history with Ignition’s scripting methods. IA’s historian really should be treated as a black box.
There is a JSON path to Grafana (Introduction | JSON API for Grafana) but the messages would need to constantly update to visualize live data…
Below SP , you can create it and then call it in your Grafana plugin.
Sp had 3 parameters, tagpath,start time,End time.
CREATE PROCEDURE dbo.RetrieveData @tagpath NVARCHAR(255),
@begintime BIGINT,
@endtime BIGINT
AS
DECLARE @tagId VARCHAR(50)
SET @tagId = (SELECT id
FROM sqlth_te
WHERE tagpath LIKE @tagpath)
DECLARE @PNAME NVARCHAR(100)
DECLARE @SQL NVARCHAR(max)
DECLARE mycursor CURSOR local fast_forward FOR
SELECT pname AS 'PNAME'
FROM sqlth_partitions
WHERE end_time >= @endtime
AND sqlth_partitions.start_time <= @begintime
OPEN mycursor
FETCH next FROM mycursor INTO @PNAME
SET @SQL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL + 'select floatvalue, t_stamp from '
+ @PNAME + ' WHERE tagid = ' + @tagId + ' UNION '
FETCH next FROM mycursor INTO @PNAME
END
SET @SQL = LEFT (@SQL, Len(@sql) - 8)
EXEC(@SQL)
IF Cursor_status('global', 'myCursor') >=- 1
BEGIN
DEALLOCATE mycursor
END
go
--exec RetrieveData '%utilities/mainco2_tot_kg%', 1612127027358, 1611489115070