[Imported] how to get a list of the all the dnp events brought back from an outstation for the last hour

[sbeadle]

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: Roughneck42, originally posted: 2018-12-07 19:40:36 Id:335
hi, new CS user here. A customer has a number of outstations that are creating an unknown number of events per hour. I am looking for a way to get the last hours historic data per point into Excel so that we can see how many events per outstation and per point there are. Any suggestions? FYI there are 22 outstations and about 2000 points in total being logged

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From User: dmercer, posted: 2018-12-09 21:22:37
To get data into excel use can use SQL with either a SQL Export object in the database or by using Querypad.

There are data fields in ClearSCADA 2017R2 for all objects for EventCountCurrentHour and EventCountPreviousHour. I'm not sure in which version this was introduced.

eg.
SELECT FullName, EventCountPreviousHour
FROM CDBObject
WHERE EventCountPreviousHour 0
ORDER BY EventCountPreviousHour DESC

If those aren't there or aren't the right data for what you need, you could add metadata fields to the points, and run logic every hour to take the difference between the process count now and the previous process count and the move the current process count into the metadata field for previous process count.

Reply From User: adamwoodland, posted: 2018-12-09 22:21:36
You can also get the data using CDBHistoricFile, but this works at a weekly level. So what you would need to do is query the latest granule from CDBHistoricFile, and then X minutes later query again and calculate the difference.

Reply From User: sbeadle, posted: 2018-12-10 08:10:54
Some more info to add clarity.
1. Events are event messages. File duration (granule) is 1 hour per file per stream. The comment from [at]dmercer has a query for event counts which applies to such event messages.
2. Historic data are value/time/quality sets for a point. There is 1 file per week per point. (There are outstation fields which count how many values have been written, and also a count on the Historic aggregate, but these are not reset each week).

Reply From User: Roughneck42, posted: 2018-12-11 22:32:35
thank you, I need to clarify that the events I am referring to are DNP3 class events per point. So basically we would like to be able to view a list of the Historic values of each point per outstation with date time stamp going back 5 hours. So query CDBHISTORIC for point name, outstation name, time, value, reasondesc, status, ...

Reply From User: geoffpatton, posted: 2018-12-12 20:34:41
You have to use the point's ID there is no name in CDBHistoric. You can lookup the ID from the CDBPoint table and filter on name or fullname. Should be able to do a join on the ID and RecordID to get the name and values displayed together.

I forgot that you wanted the Outstation CDBPoint won't have that. You will have to use the specific point type tables such as CDNP3ANALOGIN.

I use SQL just enough to forget half of what I did so hopefully I am not confusing you.

Reply From User: BevanWeiss, posted: 2019-01-03 21:30:14
[at]Roughneck42 said:
thank you, I need to clarify that the events I am referring to are DNP3 class events per point. So basically we would like to be able to view a list of the Historic values of each point per outstation with date time stamp going back 5 hours. So query CDBHISTORIC for point name, outstation name, time, value, reasondesc, status, ...

DNP3 Class Events are a bit more hidden by the overall ClearSCADA system. Querying CDBHistoric for just a count of entries over time wouldn't do it, you'd need to filter for only Value Change type entries, otherwise you'll get your Class 0 poll entries (Current Data) also. Which are technically not 'class events'.

What we've done before for a rough figure is to have a calculation point executed periodically and taking the CDNP3OS property values for the various DNP3 polls etc.. then calling Reset Poll Statistics and Reset Point Statistics every midnight.