[Imported] WITS DNP3 Scan Time Calculation Using SQL

[sbeadle]

>>Message imported from previous forum - Category:Scripts and Tips<<
User: mchartrand, originally posted: 2018-10-25 19:51:15 Id:291
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.

_______

**_NIWTelemetry:
Hello Everyone.
Looking for some help. Basically I am trying to develop a mechanism to calculate the average scan time for a series of WITS DNP3 telemetry outstations within a given set.
I am trying to identify outstations only which are in communication with the master station, in service etc..and calculating the average time difference between the DataTimestamp and Now time for that group of selected outstations.
I'm trying to use something on the lines to calculate a value'_**

_SELECT SUM (DATEDIFF(‘n',DataTimestamp,NOW())) *1.0 / (SELECT COUNT(*) *1.0 FROM CADVOUTSTATION)
FROM
CADVOUTSTATION WHERE ("SetID" = 1234) AND ("StateDesc" LIKE ‘%Healthy%')_

**_Anybody tried this before?_**

____________________________________

bevanweiss:
Are you using nice templates?

It sounds like something that might benefit from the use of Data Sets and Data Set Rows.

You'd create a Data Set, and give it a name.
Then create a Data Set Row within your template and populate it with the information that you're after for each particular site. You'd also want to include the particular filter criteria as additional columns.

Then you can easily just refer to the name of the Data Set in your SQL query and return all the rows (being the instances of your template).

I'm not really sure why you're doing a DATEDIFF using NOW()... how does that relate to the scan time? By scan time, do you mean the telemetry poll time? or perhaps the actual RTU program cycle time (in which case surely this is easier to just get direct from the RTU via programming)? or do you mean the IO scan rate (which might not always match the RTU program cycle time)?

_________________

**_NIWTelemetry:
Hello Bevan,
Thank you for your input. Basically we are trying to create a mechanism to alert us to should a lock up happen within a given WITS DNP3 outstation set. I cant use the last poll time because I need to calculate the time difference btween now time and the last outstation update time for each outstation within that set then calculate an average. If the average is greater than say 15 minutes, than an alarm is raised. This is why I need to calculate the time difference between NOW() and DataTimestamp using the DATEDIFF function. I hope this explains. Your idea of using a data set is interesting._**

_________________

bevanweiss:
When you say 'lock up', what do you mean?

Do you mean that the device will stop responding to DNP3 communications? In this case then it should generate an alarm when the integrity poll doesn't get a response.

Or do you mean that it still responds to DNP3, but that the points are no longer getting new Class 0 data (i.e. the DNP3 point value does not match the IO input value)?

I'll assume the first, but that you have a long integrity poll period, and you want an alarm earlier....
With unsolicited protocols, it used to be reasonably popular to code MTUs such that if an RTU had not reported in for X period of time then the MTU would force a poll of the RTU to verify that communications was still possible. My suggestion would be to look into a small piece of logic which would do this... if the outstation is indeed no longer working, then forcing a .Refresh would cause an alarm to be raised if it doesn't respond.

On re-reading your message. You've mentioned Outstation Set. Why would an Outstation Set 'lock up'? Are you saying there is a driver bug? What evidence do you have of this?
Or are you saying that the channel on the Outstation Set has failed, in which case you should receive a Channel Fail Alarm.

______________________________

**_NIWTelemetry:
We are currently in a process to swap out older PS1 and PS5 Outstations using the Proteus Protocol to Talus T4e Outstations using WITS DNP3 and have shared protocol sets using both Proteus and DNP3. We have been experiencing issues with the firmware of certail Talus outstations causing a number outstations to lock up and 'hog' the channel causing communication issues with other Talus outstations on that set. Schneider are assisting with this issue. But until then we require a temporaty monitoring tool to supervise outstation updates on a given scanner set.
Perhaps we are getting off topic here, I was simply looking for some advice on a SQL query / mechanism to automatically run at intervals to calculate time difference in minutes between current now date and time and successful last update date and time on an outstation by outstation basis within a set and calculate an average and write to an internal analogue point. If that value exceeds a given value then an alarm is generated. I hope this provides clarity._**

_____________________

bevanweiss:
_SELECT
CAST( SUM( { FN TIMESTAMPDIFF( SQL_TSI_SECOND, DATATIMESTAMP, CURRENT_TIMESTAMP ) } ) AS REAL ) / CAST( COUNT(*) AS REAL )
FROM
CADVOUTSTATION WHERE ("SetID" = 1234) AND ("StateDesc" LIKE '%Healthy%')_

If you are stuck on SQL syntax, you can always look in the SQL Technical Reference. It's installed alongside ClearSCADA.

I still think you're going about this wrong.
If the device is failing to respond, then it likely won't be 'Healthy' for much longer. PS: Using StateDesc of 'Healthy' isn't valid for a multi channel outstation, nor one which might have PSTN or which might be Incoming only.

You should be trying to have your devices with long times between data updates, the devices should only report when significant things have happened in the field. Even individual pump starts/stops aren't all that important (the times of them are, hence DNP3 timestamps, but having them immediately unsolicit isn't), the same with levels, flows and pressures. If there is an alarm, then that is important, and once it unsolicites it would share all the historical information anyway (but efficiently).

If you have recently migrated to digital radios, then the issue might not be with the actual IEDs, but might instead be other devices transmitting junk (like ARP requests, Windows Domain DNS requests etc etc etc) onto the network. We've seen lots of these situations where people get lazy and implement the digital radio as all Layer 2, then inevitably as more devices get added the junk traffic blocks up all the slower links.

______________________

**_NIWTelemetry:
Bevanweiss,
Thank you very much for your reply.
The purpose of this mechanism is a simple temporary quick monitoring tool for now until. Thanks for your assistance and advice - appreciated!_**