Filter historical dpQuery

[kdroog4pvss]

Setup:
WinCC OA 3.20 with SQLite and NGA influxDB
Legacy default project

I have tried to use the _AuditTrail datapoint to store entries in the archive.

The AuditTrail screen uses dpGetPeriod() to retrieve a period of data from the archive. The results are then filtered by script and presented in a table.

I tried to to something similar building a query:

Code: Select all

string qry = "SELECT '.item:_original.._value', " +
               "'.newval:_original.._value', " +
               "'.oldval:_original.._value', " +
               "'.time:_original.._value' " +
               "FROM '*' WHERE _DPT = \"_AuditTrail\" AND  '.item:_original.._value' LIKE \"*.Bed*\" TIMERANGE(\"2024-08-08T11:02:49.000Z\",\"2024-08-08T17:24:12.000Z\",1,10)";
  dyn_dyn_anytype rs;
  dpQuery(qry, rs);
  DebugN(rs);

The result recordset appears to be filtered correctly, but the other values are empty. What am I missing here?. Is there any other way to let the NGA do the filtering instead of filtering the resultset? (Which is what happens in the AuditTrail panels).

Code: Select all

WCCOAui1:[dyn_dyn_anytype 11 items
WCCOAui1:     1: dyn_anytype 5 items
WCCOAui1:	     1:  (Type: 0 Sys: 0 Dp: 0 El: 0 : 0..0)
WCCOAui1:	     2: .item:_original.._value (Type: 98 Sys: 0 Dp: 0 El: 6 : _original.._value)
WCCOAui1:	     3: .newval:_original.._value (Type: 98 Sys: 0 Dp: 0 El: 10 : _original.._value)
WCCOAui1:	     4: .oldval:_original.._value (Type: 98 Sys: 0 Dp: 0 El: 9 : _original.._value)
WCCOAui1:	     5: .time:_original.._value (Type: 98 Sys: 0 Dp: 0 El: 2 : _original.._value)
WCCOAui1:     2: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton01.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:     3: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton02.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:     4: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton03.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:     5: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton02.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:     6: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton03.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:     7: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton01.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:     8: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton01.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:     9: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton01.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:    10: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton02.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:    11: dyn_anytype 5 items
WCCOAui1:	     1: HUT:_AuditTrail (Type: 98 Sys: 1 Dp: 273 El: 0 : 0..0)
WCCOAui1:	     2: "logButton03.BedieningType"
WCCOAui1:	     3: -?-
WCCOAui1:	     4: -?-
WCCOAui1:	     5: -?-
WCCOAui1:]

[hpuchegger]

Which WinCC OA version / patch are you using?

If the issue is related to pivoted queries - pivoted queries with a WHERE filter do not work conceptually (in no backend - either influx, PSQL, MSSQL, etc.)

As a workaround it is possible to this in CTRL: do the query with filter and without filter, then complete the filtered table with information from the unfiltered table.

Br, Herbert

[kdroog4pvss]

Hi Herbert, thanks for your answer. Currently we just changed from 3.19 to 3.20. The question however is not version specific I think.

I did not know about pivot queries until now

Since following query:

Code: Select all

SELECT '.newval:_original.._value', '.newval:_original.._stime', '.oldval:_original.._value', '.oldval:_original.._stime' FROM '_AuditTrail' WHERE _DPT = "_AuditTrail" TIMERANGE("2024-07-21T18:35:18.000Z","2024-08-21T18:35:19.000Z",1,0)

returns a valid result. I did not understand why adding another where clause didn't responded as I suspected.

I will just query the timerange and process the result table by removing unwanted rows from it. That will work as well.

Regards,

[leoknipp]

I have seen that in your first query you have been using the parameters "modus = 3" and "bonus = 10". At the TIMERANGE condition.
Please take into account that only modus = 1 is supported. All other values are there for compatibility reasons, they will use internally the value 1.

Normally when doing historical queries the _offline config is used instead of _original.

The hints are just common ones and not related to the query which did not work.

Best Regards
Leopold Knipp
Senior Support Specialist

[kdroog4pvss]

Thanks, I have edited my post to avoid confusion on that part, just in case.
Regards,
Kees

[leoknipp]

Possible there is an issue with the WHERE clause filtering for a DP element and an assigned value.
Please test your query without this specific WHERE condition.

Best Regards
Leopold Knipp
Senior Support Specialist