×
Siemens Industry Online Support
Siemens AG
Entry type: FAQ Entry ID: 5668269, Entry date: 05/16/2017
(5)
Rate

How do you use the "MsgFilterSQL" property of the WinCC Alarm Control in order to use an SQL instruction to filter the messages to be displayed?

  • Entry
  • Associated product(s)
Dynamic message selection via a script
This entry uses an example to show how the "MsgFilterSQL" property of the WinCC Alarm Control is dynamized to set the following message filter on a WinCC Alarm Control. Using the message filter, only those messages are to be displayed which:
  • Are assigned to the messages classes "Class 1" ("Alarm"), "Class 2" ("Warning") or "Class 4" ("PLC process control messages").
  • Contain the character string "TI4711" in the user text block tb1 ("source").
  • Contain the character string "area1" or "area2" in the user text block tb2 ("area").
  • Are not hidden. (The option for hiding alarms is available in WinCC V6.2 and higher.)

The script shown in the following figure assigns an "SQL-where-clause" to the "MsgFilterSQL" property of the WinCC Alarm Control plus an additional flag so that only the messages required are displayed.


Fig. 01

General information is available in the WinCC Online Help under "SQL Instructions for Filtering Messages in the WinCC Alarm Control" (V6.0) and in the WinCC Information System under "Working with WinCC > Setting Up an Alarm System > Display of Messages in Runtime > SQL Instructions for Filtering Messages in the WinCC Alarm Control" (as from V6.2).

The following table describes the components of the SQL instructions used:
 

No. Description
1 "#VisibleOnly\\"
With WinCC V6.2 and higher
it is possible to hide messages. In order to display only the hidden or only the visible messages you have two control character strings: "#VisibleOnly\" and "#HiddenOnly\". Since the "\" character is a special character in the programming language "C", it is preset as "\\" in a C character string.
  • The control character strings "#VisibleOnly\" and "#HiddenOnly\" can be used right at the beginning or right at the end of the SQL instruction.
  • With the control character string "#VisibleOnly\" only visible messages are displayed.
  • With the control character string "#HiddenOnly\" only hidden messages are displayed.
  • If you use neither the "#VisibleOnly\" nor the "#HiddenOnly\" control character string, then both visible and hidden messages are displayed.

Note
The SQL instruction contains settings of the "Selection" dialog. In WinCC V6.2 and higher the SQL instruction also contains the settings of the "Display options" dialog. The settings in the two dialogs do not affect each other. See the notes in the section "Non-displayable SQL instructions".

2 "CLASS IN(1,2,4)"
Through this part of the instruction only those messages are displayed that are assigned to message classes "Class 1" ("Alarm"), "Class 2" ("Warning") and "Class 4" ("PLC process control messages"). The message classes used are specified by the message class numbers. You can find the number of each message class in the "Configure message classes" dialog in the Alarm Logging editor.
3 "AND TYPE IN(1, 2, 19, 20, 55, 56)"
This part of the instruction extends the filter criterion. Only those messages are displayed that are assigned to message types "Type 1", "Type 2", "Type 19", "Type 20", "Type 55" or "Type 56". The message types used are specified by the message type numbers. You can find the number of each message type in the "Type" dialog of a message type in the Alarm Logging editor.

Note
Since there is to be no explicit filtering in this application example, this part of the instruction could be simply left out of the SQL query view in order to achieve the same effect. However, you should observe the note in Line 1 of the table below.

4 "AND TEXT1 LIKE 'TI4711'"
This part of the instruction extends the filter criterion. Only those messages are displayed that contain the character string "TI4711" in the user text block "TB1".

Note
At this point the "LIKE" instruction deviates slightly from the usual SQL syntax. You must not use the special character "%" as wildcard in the LIKE instruction.

5 "AND TEXT2 IN('area1', 'area2')"
This part of the instruction extends the filter criterion. Only those messages are displayed that contain exactly the character string "area1" or "area2" in the user text block "TB2".

Note
However, you should observe the note in Line 1 of the table below.

Non-displayable SQL instructions
If the message filter is defined by an SQL instruction, it might happen that the message filter set cannot be displayed in the Selection dialog or "Display options" dialog of the WinCC Alarm Control. If such a filter criterion is active on the WinCC Alarm Control and you open the "Selection" dialog or the "Display options" dialog, then you get the message indicating that the current selection cannot be displayed in the Selection dialog and will be discarded.


Fig. 02

The following table describes SQL filter conditions that execute the required message filter at Runtime, but cannot be displayed in the Selection dialog of the WinCC Alarm Control.
 

No. Description
1 "IN clause in user text blocks"
Using IN operator you can define multiple texts that are valid as filter criteria for a common user text block. For example, with this method you can define a filter criterion that displays the messages of multiple selected areas (Subsystem A or Subsystem B, for example).

Such a filter condition cannot be displayed in the Selection dialog of the WinCC Alarm Control, because multiple character strings cannot be specified as filter criteria for a user text block in the Selection dialog.

2 Filters without specification of message types
If a message filter is to be defined with reference to message classes and the message types are not relevant, from the SQL query view you can do without the explicit specification of the message types. However the message filter cannot be displayed completely with the Selection dialog of the Alarm Control. If all message types are explicitly specified for all message classes in the SQL instruction, the display is complete.

Note
In this case, the warning message mentioned above indicating that the filter criterion cannot be displayed is not shown.

3 Uppercase/lowercase characters in SQL operators
The Selection dialog of the WinCC Alarm Control does not support lowercase characters in SQL operators in all cases (LIKE, TYPE, for example). The message filter is executed correctly, but the display of the message filter in the Selection dialog is not complete. You should always use uppercase characters when writing the SQL operators.

Note
In this case, the warning message mentioned above indicating that the filter criterion cannot be displayed is not shown.

4 Common usage
Do not use any filter conditions where you query specific user text blocks about being exactly equal (Operator "=") and other user text blocks about being not exactly equal (Operator "LIKE"). Mixing these two operators leads to incorrect display of the messages in the WinCC Alarm Control.

Note
You can use the operator "IN" instead of the operator "=" to define the same filter condition for the messages. You can apply the IN and LIKE operators combined to user text blocks. However, you should observe the notes in Line 1 of this table.

Note
We recommend the following procedure to insert an error-free SQL instruction.
First, you can create a filter using the Configuration dialog of the WinCC Alarm Control.

  • Open the Configuration dialog of the WinCC Alarm Control.
  • Open the "Message lists" tab.
  • In the "Selection" area you click "Edit...".
  • Configure your selection (including special characters). Close the selection with "OK".
  • Close the Properties of the Alarm Control with "OK".
  • Open the Object Properties of the Alarm Control.
  • Under Properties > Control Properties you select the attribute "DefaultMsgFilterSQL".
  • Now the correct SQL instruction is displayed in the "Static" tab.
  • Copy this instruction to your script.


Security information
In order to protect technical infrastructures, systems, machines and networks against cyber threats, it is necessary to implement – and continuously maintain – a holistic, state-of-the-art IT security concept. Siemens’ products and solutions constitute one element of such a concept. For more information about cyber security, please visit
https://www.siemens.com/cybersecurity#Ouraspiration.