I want to investigate which tag is flooding the historian.
To start, how do I add a between time clause with fromMilli timestamp:
SELECT *
FROM sqlt_data_1_20240429
WHERE t_stamp BETWEEN ... AND ...
ORDER BY t_stamp DESC
LIMIT 1000
COUNT might be more useful.
Here's a named query from one of my projects that we used to review database usage by tags.
SQL
SELECT
a.tagid,
COUNT(a.tagid) AS records,
b.tagpath
FROM {schemaName}.sqlt_data_1_{tableYear}_{tableMonth} a
INNER JOIN {schemaName}.sqlth_te b ON a.tagid = b.id
WHERE a.t_stamp >= (UNIX_TIMESTAMP(:dateTimeStart) * 1000)
AND a.t_stamp <= (UNIX_TIMESTAMP(:dateTimeEnd) * 1000)
GROUP BY a.tagid
ORDER BY records DESC , a.tagid
LIMIT 0 , 100
(I should probably have used BETWEEN in my query.)
Create a view with a table on it and use a query binding on the data property (with the named query above).
We found that examining an hour at a time once or twice a week was enough to give us a good idea if anything was recording silly amounts of data. We set a notional limit of 200 samples per hour and anything exceeding that was worthy of investigation. It helped reduce our monthly default historian table size by 90%!
2 Likes
Very Cool and Perfecto! Kudos!
I did it also, both in SQL and MySQL
SQL
select
d.*,
e.tagpath,
f.scname
from(
select
c.tagid,
count(c.tagid) as frequency
from(
select
a.*,
b.tagpath
from(
select
*,
DATEADD(HH,-5,DATEADD(MILLISECOND, t_stamp % 1000, DATEADD(SECOND, t_stamp / 1000, CAST('1970-01-01' as datetime2(3))))) as ts
from
sqlt_data_1_2024_05
) as a
left join
sqlth_te as b on a.tagid=b.id
where
a.ts > dateadd(HH, -1, getdate())
and
b.tagpath like '%1%'
) as c
group by
c.tagid
) as d
left join
sqlth_te as e on d.tagid=e.id
left join
sqlth_scinfo as f on e.scid=f.id
MySQL
SELECT
e.*,
f.tagpath,
g.scname
FROM(
SELECT
d.tagid,
COUNT(d.tagid) AS frequency
FROM(
SELECT
c.*
FROM(
SELECT
a.*,
FROM_UNIXTIME(a.t_stamp/1000) AS ts,
b.tagpath
FROM
sqlt_data_1_2024_05 AS a
LEFT JOIN
sqlth_te AS b ON a.tagid=b.id
) AS c
WHERE c.ts > DATE_SUB(NOW(), INTERVAL 1 HOUR)
) AS d
GROUP BY
d.tagid
) AS e
LEFT JOIN
sqlth_te AS f ON e.tagid=f.id
LEFT JOIN
sqlth_scinfo AS g ON f.scid=g.id