Grafana Visualization of Historical Data

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