Sign In Help
Schneider Electric
HelpSign In
Schneider Electric Exchange
  • Home
  • Collaborate
  • Develop
  • Shop
Home Collaborate Develop Shop Log in or Register Help

Invite a Co-worker

Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
You have entered an invalid email address. Please re-enter the email address.
This co-worker has already been invited to the Exchange portal. Please invite another co-worker.
Please enter email address
Send Invite Cancel

Invitation Sent

Your invitation was sent.Thanks for sharing Exchange with your co-worker.
Send New Invite Close
  • Home
  • Collaborate
  • Exchange Community
  • :
  • SCADA & Telemetry Solutions
  • :
  • Geo SCADA Expert Forum
  • :
  • [Imported] WITS DNP3 Scan Time Calculation Using SQL
Community Menu
  • Forums
    • By Topic
        • EcoStruxure IT
          • EcoStruxure IT forum
        • Industrial Automation
          • Industry Automation and Control Forum
          • Alliance System Integrators Forum
          • Machine Solutions in the Digital Transformation
          • EcoStruxure Automation Expert / IEC 61499 Forum
          • Industrial Edge Computing Forum
          • Level and Pressure Instrumentation Forum
          • Modicon User Group
          • PLC Club Indonesia
          • SEE Automation Club Forum
          • Fabrika ve Makina Otomasyonu Çözümleri
          • Форум по промышленной автоматизации СНГ
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Forum
          • SCADA and Telemetry Devices Forum
        • Power Distribution IEC
          • Power Distribution and Digital
          • Power Standards & Regulations
          • Paneelbouw & Energie Distributie
          • Eldistribution & Fastighetsautomation
        • Power Distribution Softwares
          • EcoStruxure Power Design Forum
          • SEE Electrical Building+ Forum
          • LayoutFAST User Group Forum
        • Wireless Information Network Solutions
          • Instrument Area Network
          • Remote Monitoring
          • Tank Level Monitoring
          • Remote Data Collection
        • Solutions for your Business
          • Solutions for Food & Beverage Forum
          • Solutions for Healthcare Forum
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Forum
        • Healthcare
          • Solutions for Healthcare Forum
      • EcoStruxure IT
        • EcoStruxure IT forum
      • Industrial Automation
        • Industry Automation and Control Forum
        • Alliance System Integrators Forum
        • Machine Solutions in the Digital Transformation
        • EcoStruxure Automation Expert / IEC 61499 Forum
        • Industrial Edge Computing Forum
        • Level and Pressure Instrumentation Forum
        • Modicon User Group
        • PLC Club Indonesia
        • SEE Automation Club Forum
        • Fabrika ve Makina Otomasyonu Çözümleri
        • Форум по промышленной автоматизации СНГ
      • SCADA & Telemetry Solutions
        • Geo SCADA Expert Forum
        • SCADA and Telemetry Devices Forum
      • Power Distribution IEC
        • Power Distribution and Digital
        • Power Standards & Regulations
        • Paneelbouw & Energie Distributie
        • Eldistribution & Fastighetsautomation
      • Power Distribution Softwares
        • EcoStruxure Power Design Forum
        • SEE Electrical Building+ Forum
        • LayoutFAST User Group Forum
      • Wireless Information Network Solutions
        • Instrument Area Network
        • Remote Monitoring
        • Tank Level Monitoring
        • Remote Data Collection
      • Solutions for your Business
        • Solutions for Food & Beverage Forum
        • Solutions for Healthcare Forum
      • Food & Beverage
        • Solutions for Food & Beverage Forum
      • Healthcare
        • Solutions for Healthcare Forum
  • Blogs
    • By Topic
        • Industrial Automation
          • Industrial Edge Computing Blog
          • Industry 4.0 Blog
          • Industrie du Futur France
        • SCADA & Telemetry Solutions
          • SCADA and Telemetry Blog
        • Power Distribution IEC
          • Power Events & Webinars
          • Power Foundations Blog
        • Power Distribution NEMA
          • NEMA Power Foundations Blog
        • Power Distribution Softwares
          • EcoStruxure Power Design Blog
          • SEE Electrical Building+ Blog
        • Solutions for your Business
          • Solutions for Food & Beverage Blog
          • Solutions for Healthcare Blog
          • Solutions for Retail Blog
        • Community experts & publishers
          • Publishers Community
    • By Segment
        • Food & Beverage
          • Solutions for Food & Beverage Blog
        • Healthcare
          • Solutions for Healthcare Blog
        • Retail
          • Solutions for Retail Blog
      • Industrial Automation
        • Industrial Edge Computing Blog
        • Industry 4.0 Blog
        • Industrie du Futur France
      • SCADA & Telemetry Solutions
        • SCADA and Telemetry Blog
      • Power Distribution IEC
        • Power Events & Webinars
        • Power Foundations Blog
      • Power Distribution NEMA
        • NEMA Power Foundations Blog
      • Power Distribution Softwares
        • EcoStruxure Power Design Blog
        • SEE Electrical Building+ Blog
      • Solutions for your Business
        • Solutions for Food & Beverage Blog
        • Solutions for Healthcare Blog
        • Solutions for Retail Blog
      • Community experts & publishers
        • Publishers Community
      • Food & Beverage
        • Solutions for Food & Beverage Blog
      • Healthcare
        • Solutions for Healthcare Blog
      • Retail
        • Solutions for Retail Blog
  • Ideas
        • Industrial Automation
          • Modicon Ideas & new features
        • SCADA & Telemetry Solutions
          • Geo SCADA Expert Ideas
          • SCADA and Telemetry Devices Ideas
  • Knowledge Center
    • Building Automation Knowledge Base
    • Industrial Automation Knowledge Base
    • Industrial Automation How-to videos
    • SCADA & Telemetry Solutions Knowledge Base
    • Digital E-books
    • Success Stories Corner
    • Power Talks
  • Events & Webinars
    • Innovation Talks
    • Innovation Summit
    • Let's Exchange Series
    • Technology Partners
  • Support
    • Ask Exchange
    • Leaderboard
    • Our Community Guidelines
    • Community User Guide
    • How-To & Best Practices
    • More
Join Now
How can we help?
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
51317members
Join Now
245520posts
Join Now

[Imported] WITS DNP3 Scan Time Calculation Using SQL

Options
  • Subscribe to RSS Feed
  • Mark Topic as New
  • Mark Topic as Read
  • Float this Topic for Current User
  • Bookmark
  • Subscribe
  • Mute
  • Printer Friendly Page
Back to Geo SCADA Expert Forum
sbeadle
Sisko sbeadle Sisko
Sisko
‎2019-11-05 02:11 PM
0 Likes
0
182
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content
‎2019-11-05 02:11 PM

[Imported] WITS DNP3 Scan Time Calculation Using SQL

>>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!_**

Labels
  • SCADA
Share
  • All forum topics
  • Previous Topic
  • Next Topic
Related Products
Schneider Electric
EcoStruxure™ Geo SCADA Expert
Top Experts
User Count
sbeadle
Sisko sbeadle Sisko
188
BevanWeiss
Sisko BevanWeiss
53
AdamWoodland
Lt. Commander AdamWoodland Lt. Commander
16
JChamberlain
Lieutenant JChamberlain Lieutenant
16
AndrewScott
Lieutenant AndrewScott
15
See More Top Experts
Find a Service Provider
Find a certified partner to help you address your integration, installation, maintenance and project needs.
View all Providers
Support

Have a question? Please contact us with details, and we will respond.

Contact Us
FAQ

Look through existing questions to find popular answers.

Learn More
About

Want to know more about Exchange and its possibilities?

Learn More

Full access is just steps away!

Join Exchange for FREE and get unlimited access to our global community of experts.

Connect with Peers & Experts

Discuss challenges in energy and automation with 30,000+ experts and peers.

Get Support in Our Knowledge Base

Find answers in 10,000+ support articles to help solve your product and business challenges.

Ask Questions. Give Solutions

Find peer based solutions to your questions. Provide answers for fellow community members!

Register today for FREE

Register Now

Already have an account?Log in

About Us FAQ Terms & Conditions Privacy Notice Change your cookie settings
©2020, Schneider Electric