Hey Guys
I was wondering if anyone has any ideas on how I could go about triggering a stored procedure right before a history table gets dropped by the pruning system. It would be a nice option to actually add to pruning setup on the gateway.
We partition daily and prune monthly. So what we would like to do is instead of just dropping all that data for each day we want to be able to run a SP that will “compress” that data into an “Summary” table that would give us daily Min, Max, Mean, STDEV and so on for each tag that is logged. We then can use this table to develop some base lining for specific machines over a much greater time.
Hi,
You’ve reminded me of something that we had planned when the history system was first built- the prune data operation was just supposed to be one type of “maintenance” operation that could be configured. Others, like what you want to make, would “archive” the data, summarize it, etc. A simple way to accomplish something like this might be to create the option to do what you ask- call a stored procedure.
Now, ultimately, you could simply disable pruning and create your own mechanism. The pruning system doesn’t really do anything in memory, it simply looks for old partitions, drops them, and deletes them from the sqlth_partitions table. Theoretically it should also clean up the sqlth_sce and sqlth_te tables as well, but it doesn’t currently.
You could write your own cleanup procedure, and then trigger it to run periodically from a stored procedure group or gateway timer script.
Regards,