Clear historic data from all points in a group - ClearSCADA 2010 R2

[ash_aqx]

Hi All,

I'm hoping to get some guidance in writing an ST Program to clear the historic data from all points in a group and sub-groups.

The Group is a DNP3 outstation. Currently our operators go through the process of:

- Point: Disable In Service

- Historic: Disable Historic

- Historic: Enable Historic

- Point: Enable In Service

There's got to be a better way!

I've looked into using SQL in my ST Program, but I'm a little lost when it comes to selecting the Id for every point then running DELETE on every row. Seems like a pretty dangerous operation if the SELECT get's an incorrect point Id.

I'm hoping that I can end up with a boolean point that when enabled would clear historic for every point within the group it's located in.

This particular project is using ClearSCADA 2010 R2.. don't ask.

[sbeadle]

Hi Ash,

Better way? Why do this at all! And on an unsupported release...

You /can/ do this by disabling historic data - but you must wait several seconds for the disable/delete to sync on any standbys, then re-enable.

Selecting samples and deleting them with the delete method is going to be inefficient.

"...with a boolean point that when enabled would clear historic for every point within the group it's located in."

This sounds like the most risky thing to do with historic data!

[JChamberlain1]

You can't run SQL DELETE on historic tables.

What you will want to do is disable and re-enable the historic aggregate programmatically in logic, then you can put buttons to execute that logic on a mimic for your operators to use.

[ash_aqx]

Thanks for the reply,

As for the why, it's a bit of an outlier. The system is portable, everytime it's moved we need to reset all the historic readings as part of the contract requirements. This consumes a fair bit of man hours.

If there's a better way of doing this that doesn't involve recreating and remapping a site with around 100 dnp3 points then I'm all for better solutions.

[ash_aqx]

Thanks for the reply,

That's exactly what I was hoping to achieve. Some bit of logic that moves through each point in the group and disable / re-enables the historic.

Do you have any pointers / sources on how this might be achieved?

[geoffpatton]

Are you saying the whole system is moved from one site to another and all the history in ClearSCADA has to be reset? If so then just delete the History folder while the database is shutdown.

[ash_aqx]

Just the remote outstation is moved, not the scada instance.

The outstation is connected via DNP3 over a 4G connection.

So everytime the outstation is relocated we need to wipe it's data. All the other sites in GeoSCADA need to retain their historic. Can't shutdown the database as that would affect all the other connected sites.

[BevanWeiss]

Are you sure that you really wouldn't want to keep the historic data associated with the sampled location?

We have a couple of customers that have a very similar situation.

They have portable level transmitters / floats that get deployed beneath manhole covers to determine where a restriction in a sewage network is.  When the blockage location is detected and removed, then the sensor can be redeployed elsewhere.

But for this, we like to keep around the historical data for the manhole, so that if it has had sensors installed on it three times across different times we still have a record of what the levels were in each installation period (i.e. we can see that the profile on this installation matches the profile on the previous installation).

We also care about the long term values of the sensor itself however, to identify if it's had anomalous readings.

We use logic that 'copies' the historic records from the raw instrument points into Internal Points which are associated with the locations themselves.

For your deleting, then I think you'd essentially want to also use logic.  If you had it turn off the historic aggregate for each point you wanted to purge the history for, and then ran an OptimiseHistoric on it, that should flush everything out.  Then you could re-enable the historic aggregate once you wanted to start collecting historic entries again.

I looked into the schema, and unfortunately the Historic Aggregate is defined quite low in the point hierarchy, so unless you only have one specific point type (i.e. DNP3 Point / ICMP Point / Simple Point etc) then you would need to use multiple queries to disable all the historic aggregates.  The Logic could do it more easily in a loop, you'd just need to use a second logic routine called by the first with an Input parameter to access the Indirect reference for the Historic Aggregate disable/enable