I need to query Ignition historical data from an external reporting application and I’d like to know if there’s any existing tools (stored proc, UDF etc…) I could use.
Historian software such as Pi and iHistorian provide Stored Procedures that manage queries that span over multiple tables/archives/partition and can also manage data interpolation.
Is there something similar available with Ignition ?
Thanks for your help
Hi,
Unfortunately there isn’t currently a set of stored procedures available to do this. The tables are only really meant to be queried by Ignition, but I think it certainly would be possible for us to created procedures to expose the data in a more user friendly format.
Depending on your reporting requirements, you might find it better to store some data through transaction groups, which let you store it however you want. I’ve seen many people store data in both ways, for use in Ignition, and then for external use.
We certainly would like to work towards better accessibility on this front in the future, though.
Regards,
Thanks for your reply,
I took a look at the tables created by Ignition and everything is extremely well organized.
Glad you do 
Meanwhile, looks like I'll need to create them myself 
Thanks and have a nice day
YES we want a stored procedure for this! 
We have a LOT of use for this. We do all our reporting in MS reporting services.
yes we would like this also.
I could use this also!
+1 for this features, stored proc for MS SQL SERVER and MYSQL 
+1 as well!
I was recently at a MES training course and we were talking about the possibility of there being a historical analysis controller component similar to the analysis controller components in the MES modules. I was excited about that prospect since those components are very powerful. That would be an awesome idea!
any updates on a stored procedure to collect sqltag history? we need to display some data in an outside application and need a way to grab values from the sqltag history tables. We have way too many tags to create transaction groups for.
We haven’t had any available time to write the stored procedure. The stored procedure would be an example that you could import into a project. This could be written by anyone with an understanding of SQL.
The only problem using transaction groups is that it does not compress the data in any way when storing the data. We had to use transaction groups as we need to access the data and after 6 months our DB is at 24gigs.
Any plans on having transaction groups compress the data it inserts?
Thanks
No, there is nothing we can do to compress transaction group data. However, you can do this on the database side of things. The database may have some features available for compressing table data. MySQL and SQL Server have these features, unfortunately SQL Server only offers table compression in their enterprise edition.
Hi, are there any updates about stored procedures (or anything else usable by an external tools)?
Thank you
You can use the system.tag.queryTagHistory function. Documentation for it here: docs.inductiveautomation.com/di … TagHistory
system.tag.queryTagHistory gets the data for you in the right format.
Once you get the data with system.tag.queryTagHistory you can insert that data into a database. Then you can use a simple database query to get the data. I know this solution is roundabout but it should work as a way to get historical data from applications outside Ignition.
Another way is to install and use the WebDev module. You can then have external applications make http requests to Ignition and Ignition can execute a script that gets the historical data by using system.tag.queryTagHistory and returns the data in the http response.
Best,
If you are using PostgreSQL, you can create an empty “master” table and manually add the SQLtag history partitions to it as “inherited” children. Although normal partitioning starts with the master and creates children from it, PostgreSQL allows both directions. See the ALTER TABLE … INHERITS … syntax.
Once set up, querying the master table would return data from all of the attached children.
[quote=“nmudge”]You can use the system.tag.queryTagHistory function. Documentation for it here: docs.inductiveautomation.com/di … TagHistory
system.tag.queryTagHistory gets the data for you in the right format.
Once you get the data with system.tag.queryTagHistory you can insert that data into a database. Then you can use a simple database query to get the data. I know this solution is roundabout but it should work as a way to get historical data from applications outside Ignition.
Another way is to install and use the WebDev module. You can then have external applications make http requests to Ignition and Ignition can execute a script that gets the historical data by using system.tag.queryTagHistory and returns the data in the http response.
Best,[/quote]
Hi, thank you for your suggestions !
[quote=“pturmel”]If you are using PostgreSQL, you can create an empty “master” table and manually add the SQLtag history partitions to it as “inherited” children. Although normal partitioning starts with the master and creates children from it, PostgreSQL allows both directions. See the ALTER TABLE … INHERITS … syntax.
Once set up, querying the master table would return data from all of the attached children.[/quote]
Hi, I am using MySQL, but I will keep it in mind for future applications !