Snapshot of WinCC OA DP database

[eMichx]

Hello,

I 'm looking for a solution to have a timestamped snapshot on a large liste of DP.

I have tried to use the dpQuery function, but I don't know to 'TimeStamp' the result.

For example, an SQL request like :
SELECT '_online..value',GetCurrentTime() As T FROM '*.status.value'
Note : This request doesn't work (the SQL word 'AS', seems not working!)

Is there a solution, to have a value of an DP, linked with a time when the DP has been scanned?
and widen this process in a set of DPs

best regards
Emmanuel

[leoknipp]

Hello,

you can use the dp-monitor to the get the current values for a given list of dp-elements: System management --> Reporting --> DPE monitor/DP info.

If you want to get the value and timestamp for a dp-element using a SQL-statement you have to read the attributes _online.._value + _online.._stime or _original.._value + _original.._stime.
For detailed information concerning queries in WinCC OA please have a look at the online help: System management --> Reporting --> SQL.

Best Regards
Leopold Knipp
Senior Support Specialist

[fmulder]

I guess you want to have :

SELECT '_online..value','_online.._stime' FROM '*.status.value'

Good luck

Frenk

[eMichx]

Hi,

I have already tried the SQL request : SELECT '_online..value','_online.._stime' FROM '*.status.value'
by using the function dpQuery, but I'm not looking for the time of the last change of the DP, but the time when
the DP value is requested (a snapshot) :

SELECT '_online..value',GetCurrentTime() As T FROM '*.status.value'

The time evolves during the request execution (milliseconds change)


Emmanuel

[leoknipp]

Hello,

with the CTRL-function getCurrentTime() you get the current time.
If you just want to know when the dpQuery-command was executed you can call this function before starting the dpQuery.

Best Regards
Leopold Knipp
Senior Support Specialist

[eMichx]

The SQL Request has an execution time (in my case, more or less 300ms), during this time, a change on a DP can occures
that is the reason I need to have an exact timestamp of the DP snapshot
The need is for all the DP extracted from the SQL Query.

When I'm using the function GetCurrentTime(), there's a delay between the response of the
function and the result of the dpQuery function . It means my timestamp will not be exact!

It's necessary to have an embedded time function in the SQL query to insure of the exact time
of the DP value extraction.

or perhaps, there's another solution?

Emmanuel

[Gertjan van Schijndel]

You can create a snapshot of dpe's by storing the current values of the dpe's in global variable and when you want to create a snapshot store the timestamp and create a copy of this variable. Afterwards you can save the snapshot. I heard that this is how they create snapshots in a datacentre project.

[leoknipp]

Hello,

with the solution Gertjan has mentioned you do not have to wait for the result of the dpQuery-command.
But even in this case (when using connect-functions to get the current value and store them in a script-variable) you can have a difference between the current value and the value in your script-variable when the snapshot is made. E.g. caused by a short delay when sending/receiving messages, short time needed to process CTRL-functions, ....

Why is it necessary to have the exact time (in milli seconds)?
What is the use case for it and what is done with result?

Best Regards
Leopold Knipp
Senior Support Specialist

[eMichx]

Hello,

I have implemented the Gertjan solution, I'm using it to create a snapshot every X minutes.
The values of this snapshot are stored in an external MS SQL Database.
These records are used after to replay Data .
The replay must allow :
- the step by step (this is the reason of exact time),
- rewind, foward, ...
- play in real time

regards
Emmanuel

[vogler]

Hi,

If you have an Oracle-Database then it is quite easy:

insert into snap_table (dp, value, ts) as select dp,value,systimestamp from json_table (
httpuritype('http://scada:9010/json3?pattern=HostDiag_*.cpu.val').getblob()
FORMAT JSON,
'$.values[*]'
columns (
dp varchar2(100) path '$."dp"',
value varchar2(1000) path '$."value"'
)
)

1 System1:HostDiag_DATABASE.cpu.val 35.579670 08-DEC-14 09.01.35.768322 AM +01:00
2 System1:HostDiag_DESKTOP1.cpu.val 1.437798 08-DEC-14 09.01.35.768322 AM +01:00
3 System1:HostDiag_LINUX1.cpu.val 5.696218 08-DEC-14 09.01.35.768322 AM +01:00
4 System1:HostDiag_LINUX2.cpu.val 5.135135 08-DEC-14 09.01.35.768322 AM +01:00
5 System1:HostDiag_NAS2.cpu.val 33.581590 08-DEC-14 09.01.35.768322 AM +01:00
...

br.
Andy