Tag activity monitoring

Ignition
1

We have hundreds of thousands of tags from tens of thousands of sensors.
How do we monitor tag activity based on different tag type, eg. 100 most active
tags, 100 least active tags, and deviations from an expected tag frequency?
This monitoring could detect mis-configurations, bugs, viruses, etc.
Thank you for any help.

2

I’d do this with a query to the history sql table. Return the count of rows over x period of time and group by tag id/name
E.g.

SELECT 
   tags.tagpath,
   COUNT(tags.tagpath) as recordcount
FROM sqlth_data_202107 as data
        LEFT JOIN sqlth_te as tags ON tags.id = data.tagid
WHERE data.timestamp between x AND y
GROUP BY tags.tagpath

Table names and fields might be slightly different as they’re from memory.

You’ll probably want to convert the Unix timestamps to normal dates so you can use a daterange instead.

I use this t-sql scalar function for that:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_ConvertToDateTime] (@Datetime BIGINT, @ConvertToLocal bit)
RETURNS DATETIME2(7)
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetimeSec BIGINT
		   ,@Milliseconds BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())

	SET @AdjustedLocalDatetimeSec = @Datetime / 1000 -- Convert to seconds
	SET @Milliseconds = @Datetime % 1000

    IF @ConvertToLocal = 1
		SET @AdjustedLocalDatetimeSec = @AdjustedLocalDatetimeSec - @LocalTimeOffset;
	
	
    RETURN (SELECT DATEADD(millisecond, @Milliseconds, DATEADD(second,@AdjustedLocalDatetimeSec, CAST('1970-01-01 00:00:00.000000' AS datetime2(7)))))
END;
3

What if you want to query more than a month?

4 
SELECT 
   tags.tagpath,
   COUNT(tags.tagpath) as recordcount
FROM (
SELECT * FROM sqlth_data_202107
UNION ALL
SELECT * FROM sqlth_data_202106
UNION ALL
SELECT * FROM sqlth_data_202105
) as data
        LEFT JOIN sqlth_te as tags ON tags.id = data.tagid
WHERE data.timestamp between x AND y
GROUP BY tags.tagpath
5

Not quite what I meant, that query isn’t dynamic. You have to manually specify the tables, vs the tables being specified automatically based on the start/end date. Was curious if you had a fancy query, prepared statement, etc. you used.

6

You could do it in script by first querying to return all tables in your database, then filtering by the ones within your daterange, and finally building the query I posted from the result. I haven’t had a need for anything like it before so don’t have any code sorry, but see here for listing all tables: