BackStaff works as a complement for different types of plant software. A handy one is the Historian application and among these Ignition Tag Historian is a prevalent one. BackStaff can integrate with Ignition Tag Historian to
- Create indicators or event
- Show time trends
BackStaff Configuration for communication with Ignition Historian
The first step consists of creating a database connection, similar to the next example
Do not forget to test the connection, to validate the server name or IP address, as well as the user and password.
Query to pull data from Ignition Historian
At the end of this post, there is a stored procedure that retrieves the values of the different variables or tags within the Historian. This stored procedures already considers that the Tag Historian splits the information between different tables, uses a Unix type UTC stamp.
exec sp_TagHistorianQuery StartDate, EndDate, TagPath, AggregationMode, Database, TimeZone, Resolution
where StartDate and EndDate are the time query boundaries, TagPath is the tag we want the information for, AggregationMode is what type of aggregated information we want:
- ‘Avg’: Returns the average value
- ‘StdDev’: Returns the standard deviation
- ‘Min’: Returns the minimum
- ‘Max’: Returns the maximum
- ‘None’: Returns all the values in the interval with the time stamps and quality for each one
Database is the name of the database that contains the information, TimeZone is an integer for the time zone (for example -6 for CST), and Resolution corresponds to the time interval between values in milliseconds.
For example
exec sp_TagHistorianQuery ‘2018-06-01’, ‘2018-07-07′, ”Avg’, refrigeration/tower1/fanspeed’, ‘demo’, -6, 1000
This query asks for the average value of the tag refrigeration/tower1/fanspeed between 2018-06-01 00:00:00 and 2018-07-07 00:00:00 CST with 1 second separation between values.
Based on this we can create an indicator for the average fan speed for the last 24 hours,
and those can be seen as part of a scorecard
Trends based on Ignition Historian Data
BackStaff is capable of storing in its database the information that reads from the Historian database. Nonetheless, it would be redundant information, so BackStaff is capable of doing trends based on queries from other databases.
For getting the data for the BackStaff trend, it is necessary to include the historical query into the IPGParametros table in the BackStaff IPGIndicadores table, for the field HISTORIAN-QUERY. The proper query is
DECLARE @TagPath VARCHAR(500)=’%s’, @StartDateTime DATETIME = DATEADD(%s,-%s,getdate()), @EndDateTime DATETIME = GETDATE()
EXEC SP_ObtenerHistoriaTag @TagName=@TagPath, @StartDateTime=@StartDateTime, @EndDateTime = @EndDateTime
Summary
BackStaff integration with Ignition Historian is pretty straightforward and can be ready in less than one hour.
Stored Procedure Code
USE [Demo]
GO
/****** Object: StoredProcedure [dbo].[spGNE_ConsultaDatosIndividual] Script Date: 7/17/2018 10:19:56 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_TagHistorianQuery]
@StartDate nvarchar(max),–Date and time for the first value
@EndDate nvarchar(max),–Date and time for the last value
@Tagname nvarchar(max),–TagPath
@AggregationMode nvarchar(10),–Agregation mode: Avg, StdDev, Min, Max, None
@DBName nvarchar(100) = ‘demo’,–Ignition project database where the Tag Historian stores data
@GMT_Zone int = -6,–Set the timezone according to your needs or convert it to a parameter
@Resolution int = 1000–Set it in milliseconds. This corresponds to retrieving a value every second maximum
as
set nocount on
begin try
create table ##TableData(
[TagName] nvarchar(max),
[Value] nvarchar(max),
[Quality] int,
[t_stamp] bigint,
[DateTime] datetime)
end try
begin catch
delete from ##TableData
print ‘Error creating ##TableData’
end catch
declare @EndDateSec bigint = convert(bigint, datediff(second, ‘1970-01-01’, @EndDate)) * 1000 – @GMT_Zone*3600*1000
declare @StartDateSec bigint = convert(bigint, datediff(second, ‘1970-01-01’, @StartDate)) * 1000 – @GMT_Zone*3600*1000
declare @TableQuerys table( Id int identity, [Querys] nvarchar(max) )
declare @TableTagIds table( Id int identity, [TagId] int )
declare @Datatype int — 0 -> [intvalue], 1 -> [floatvalue], 2 -> [stringvalue], 3 -> [datevalue]
declare @query nvarchar(max)
declare @query2 nvarchar(max)
declare @typeColumn nvarchar(max)
declare @TagId int
declare @it int = 1
declare @it_max int
set @query2 = ‘SELECT @Datatype = [datatype] FROM [‘+ @DBName +’].[dbo].[sqlth_te] where [tagpath] = ‘ +””+ @Tagname + ”” +’ and [retired] is null order by [created] asc’
exec sp_executesql @query2, N’@Datatype int output’, @Datatype = @Datatype output
if @Datatype = 0
begin
set @typeColumn = ‘[intvalue]’
set @query = ‘SELECT ”’+ @Tagname + ”’, convert(nvarchar(max), convert(int,[intvalue])) as Value ,[dataintegrity],[t_stamp], DATEADD(ms, [t_stamp]%1000, DATEADD(ss, [t_stamp] / 1000 + 3600*’ + convert(nvarchar(max), @GMT_Zone) + ‘, ”’ + ‘1970-01-01’+”’)) as DateTime FROM [‘+ @DBName +’].[dbo].[‘
end
if @Datatype = 1
begin
set @typeColumn = ‘[floatvalue]’
set @query = ‘SELECT ”’+ @Tagname + ”’, convert(nvarchar(max), convert(decimal(30,10),[floatvalue])) as Value ,[dataintegrity],[t_stamp], DATEADD(ms, [t_stamp]%1000, DATEADD(ss, [t_stamp] / 1000 + 3600*’ + convert(nvarchar(max), @GMT_Zone) + ‘, ”’ + ‘1970-01-01’+”’)) as DateTime FROM [‘+ @DBName +’].[dbo].[‘
end
if @Datatype = 2
begin
set @typeColumn = ‘[stringvalue]’
set @query = ‘SELECT ”’+ @Tagname + ”’, convert(nvarchar(max), ‘ + @typeColumn + ‘) as Value ,[dataintegrity],[t_stamp], DATEADD(ms, [t_stamp]%1000, DATEADD(ss, [t_stamp] / 1000 + 3600*’ + convert(nvarchar(max), @GMT_Zone) + ‘, ”’ + ‘1970-01-01’+”’)) as DateTime FROM [‘+ @DBName +’].[dbo].[‘
end
if @Datatype = 3
begin
set @typeColumn = ‘[datevalue]’
set @query = ‘SELECT ”’+ @Tagname + ”’, convert(nvarchar(max), ‘ + @typeColumn + ‘) as Value ,[dataintegrity],[t_stamp], DATEADD(ms, [t_stamp]%1000, DATEADD(ss, [t_stamp] / 1000 + 3600*’ + convert(nvarchar(max), @GMT_Zone) + ‘, ”’ + ‘1970-01-01’+”’)) as DateTime FROM [‘+ @DBName +’].[dbo].[‘
end
set @query2 =
‘SELECT ‘ +”” + ‘SELECT ‘ +””+ ‘ + ‘ + ””””” + ‘ + ‘ +
+”” + @Tagname + ””+ ‘ + ‘ + ””””” + ‘ + ‘ +”” +
‘, convert(nvarchar(max), convert(decimal(30,10),’ + @typeColumn + ‘)) as Value, [dataintegrity],[t_stamp], DATEADD(ms, [t_stamp]%1000, DATEADD(ss, [t_stamp] / 1000 + 3600*’+ convert(nvarchar(max), @GMT_Zone) +’,’ + ”” + ‘ + ‘ + ””””” + ‘ + ‘ +”” +
‘1970-01-01’ +”” + ‘ + ‘ + ””””” + ‘ + ‘ +
+ ”” + ‘)) as DateTime FROM [‘+ @DBName +’].[dbo].[‘+ ”” + ‘+ ‘ +
‘[pname]’ + ‘ +’+ ”” +
+ ‘] A inner join [‘+ @DBName +’].[dbo].[sqlth_te] B on A.tagid = B.id where B.tagpath = ‘ +””+ ‘ + ‘ + ””””” + ‘ + ‘ + ””+
+@Tagname + ””+ ‘ + ‘ + ””””” + ‘ + ‘ + ””+
‘and [t_stamp] >= ‘ + convert(nvarchar(max),@StartDateSec) + ‘ and [t_stamp] <= ‘ + convert(nvarchar(max),@EndDateSec) + ‘ and [t_stamp] % ‘ + convert(nvarchar(max), @Resolution) + ‘ = 0’ + ””+
‘ FROM [‘+ @DBName +’].[dbo].[sqlth_partitions]’
insert into @TableQuerys exec(@query2)
–#########################################################
–Query Execution
declare @i int = 1
declare @i_max int
select @i_max = max(Id) from @TableQuerys
while @i <= @i_max begin
select @query = [Querys] from @TableQuerys where Id = @i
insert into ##TableData exec(@query)
set @i = @i + 1
end
–Historical Table
if @AggregationMode = ‘None’
select [DateTime], Value, Quality from ##TableData
–Statistical Data
if @AggregationMode = ‘Avg’
select avg(convert(decimal(10,2),Value)) as ‘Average’ from ##TableData
if @AggregationMode = ‘StdDev’
select stdev(convert(decimal(10,2),Value)) as ‘Standard Deviation’ from ##TableData
if @AggregationMode = ‘Min’
select max(convert(decimal(10,2),Value)) as ‘Maximum’ from ##TableData
if @AggregationMode = ‘Max’
select min(convert(decimal(10,2),Value)) as ‘Minimum’ from ##TableData
drop table ##TableData
