Resolving WinCC 7.2 MsgFilterSQL Archive Filter Failures

Problem Overview

In Siemens SIMATIC WinCC V7.2, a dynamic MsgFilterSQL string assigned to an AlarmControl filters the live (online) message list correctly but returns zero rows when the operator switches the same control to the Short-term archive list or the Long-term archive list. The filter is built in VBScript from user-selected dropdown values and concatenated into a single SQL WHERE clause, for example:

VBScript
Item.MsgFilterSQL = ("CLASS IN (" & Floor_Filter & ") AND PRIORITY IN (" & Priority_Filter & ") AND STATE IN (" & State_Filter & ") AND TEXT1 LIKE '*" & Text_Filter & "*'")

No runtime error is raised. The archive view simply renders an empty result set even when matching messages exist in the archive. This silent failure is the most common cause of "my filter does not work in the archive view" tickets in WinCC V7.x projects, because the AlarmControl does no client-side validation of the SQL string and the SQL Server backend returns zero rows for an unparseable LIKE pattern.

Affected Versions and Components

The behavior has been reproduced and confirmed on the following Siemens SIMATIC WinCC releases:

The SQL subset and MsgFilterSQL property for TIA Portal WinCC Runtime Professional are documented in the Siemens TIA Portal Help: SQL statements for filtering the alarm view (RT Professional). Although the V7.2 AlarmControl pre-dates the TIA Portal RT Professional implementation, both products share the same SQL grammar for MsgFilterSQL.

Root Cause

The MsgFilterSQL property of the WinCC AlarmControl is parsed against a fixed SQL-92 subset. In that subset, the multi-character wildcard for the LIKE operator is the percent sign (%), not the asterisk (*). The asterisk is treated as a literal character.

The reason the same string appears to "work" on the live message list is a difference in how each data source is evaluated:

1. Online / live message list — backed by the in-memory ring buffer of the Alarm Logging service. The AlarmControl applies the pattern in-process and tolerates an unparseable LIKE clause by falling back to a non-restrictive match against the in-memory MSG_DATA structures. The user sees what looks like a working filter, but no SQL has actually been issued.
2. Short-term archive list — served from the configured short-term archive (memory plus flushed segments on disk). Queries go through the same ODBC path used by the long-term archive.
3. Long-term archive list — served from the segmented SQL Server archive database (default name CC_Alg_<ServerName>_<Timestamp>). Queries are issued as parameterized T-SQL against the archive view.

When the LIKE pattern contains literal asterisks, the SQL engine evaluates it character-by-character against TEXT1. A pattern such as '*Pump*' requires the string to contain actual asterisk characters at positions 1 and 5, which production TEXT1 values never do. The result is an empty set, and the AlarmControl displays "no messages".

How the AlarmControl Consumes MsgFilterSQL

The AlarmControl property sheet exposes a single Selection node that contains both a quick filter (text box at runtime) and a SQL filter. The MsgFilterSQL string is applied on top of any user-entered quick filter. Internally the AlarmControl performs the following steps when the user activates a message source (Online, Short-term, Long-term):

1. Resolves the configured data source via the WinCC Message Service (component CCMsgServ.exe).
2. Builds a parameterized T-SQL statement using the configured column list (default: MSGNR, DATETIME, TEXT1..TEXT10, CLASS, TYPE, STATE, PRIORITY and related fields).
3. Appends the WHERE clause from MsgFilterSQL verbatim — no client-side validation is performed.
4. Executes the query through the ODBC DSN CC_WinCC_Alg (default name; configurable in WinCC Explorer → "Alarm Logging" → "Archives").
5. Maps the result set back to the AlarmControl rows.

Because step 3 does no client-side validation, a malformed SQL string is sent to SQL Server and either rejected by the parser (raising an event-log error) or, more commonly in this scenario, is SQL-compliant but wildcard-mismatched — and executes successfully while returning zero rows. The latter is the case for the * wildcard in a LIKE clause.

Archive List Architecture in WinCC V7.x

WinCC V7.x maintains three distinct message sources for the AlarmControl, each with its own backing store:

Both archive sources require a working ODBC connection. The default DSN is CC_WinCC_Alg pointing to the WinCC instance \<Server>\WinCC. If this DSN is missing or misconfigured, the AlarmControl will fall back to a no-archive behavior, which can mask the real bug. Always verify the ODBC data source with the SQL Server Management Studio connection test before debugging MsgFilterSQL.

Solution: Use the SQL-92 % Wildcard

Replace every asterisk in the LIKE pattern with a percent sign. The corrected VBScript is:

VBScript
'Declarations
Dim Floor_Filter, Priority_Filter, State_Filter, Text_Filter
Floor_Filter    = HmiRuntime.Tags("Floor_Filter").Read
Priority_Filter = HmiRuntime.Tags("Priority_Filter").Read
State_Filter    = HmiRuntime.Tags("State_Filter").Read
Text_Filter     = HmiRuntime.Tags("Text_Filter").Read

'Filter - use % for LIKE wildcard, never *
Item.MsgFilterSQL = "CLASS IN ("    & Floor_Filter   & _
                    ") AND PRIORITY IN (" & Priority_Filter & _
                    ") AND STATE IN ("    & State_Filter   & _
                    ") AND TEXT1 LIKE '%" & Text_Filter   & "%'"

The two % characters at the start and end of the LIKE pattern tell the SQL engine to match any prefix and any suffix around the user-entered substring. If the user clears the text filter, Text_Filter will be empty and the resulting pattern '%%' matches every value — which is the expected "no text restriction" behavior.

Step-by-Step Correction Procedure

1. Open the WinCC Graphics Designer that hosts the AlarmControl picture (for example AlarmScreen.Pdl) and select the AlarmControl.
2. Open the properties dialog → Selection tab. Confirm that the field list is set to the expected columns. The default list covers the standard alarm fields and is required for the columns referenced in the WHERE clause to be selectable.
3. Locate the VBScript action that builds the MsgFilterSQL string. In the source project, the action is wired to a button-click or a tag-change event.
4. Replace every * in a LIKE pattern with %. The condition columns (IN (...)) and equality comparisons are not affected — only LIKE wildcards change.
5. Add a trace line at the end of the action:

   VBScript
   HMIRuntime.Trace "Filter=" & Item.MsgFilterSQL & vbNewLine
   

   The trace output will appear in the WinCC_aplog.log and in the WinCC Diagnostic window if enabled. If the traced string still contains *, you are looking at the wrong action or the action is being overwritten by a later one.
6. Recompile and re-download the picture (right-click the AlarmControl → "Configuration dialog" → "OK" to apply). Some projects require a full Graphics Designer restart to clear the cached picture.
7. Test in runtime: trigger the filter action, switch the message source dropdown to "Short-term archive" and to "Long-term archive", and confirm that matching rows now appear.

Verifying the Filter via HMIRuntime.Trace

Trace output is the fastest way to confirm both that the correct string is being assigned and that the AlarmControl is forwarding it. Add the following lines around the assignment:

VBScript
Dim sFilter
sFilter = "CLASS IN (" & Floor_Filter & ") AND PRIORITY IN (" & Priority_Filter & _
          ") AND STATE IN (" & State_Filter & ") AND TEXT1 LIKE '%" & Text_Filter & "%'"

HMIRuntime.Trace ">> MsgFilterSQL before assign: " & sFilter & vbNewLine
Item.MsgFilterSQL = sFilter
HMIRuntime.Trace ">> MsgFilterSQL after  assign: " & Item.MsgFilterSQL & vbNewLine

Then enable the WinCC trace:

1. Open WinCC Explorer → right-click the computer → Properties → Graphics Runtime tab.
2. Set HMI trace to "On" and Trace level to at least "Errors and warnings".
3. Open the Windows application event log (eventvwr.msc) and filter for source WinCC; trace lines appear there as well.

What to look for in the trace:

• If the "before" line still contains *, your edit is in a different script than the one runtime executes. Search the project for all occurrences of MsgFilterSQL using the cross-reference tool in WinCC Explorer.
• If the "after" line is empty, the AlarmControl has rejected the string. Re-check the field list (e.g. TEXT1 must be present in the configured column list) and verify that the connection point is the AlarmControl itself rather than a wrapper picture.
• If the "after" line is correct and the result is still empty, the issue is downstream (archive ODBC, SQL permissions, segment mismatch). Continue to the "Archive Connection Checklist" section.

MsgFilterSQL Field Reference

The following columns are available in the WHERE clause of MsgFilterSQL for WinCC V7.x. Column names are case-insensitive; the exact set depends on the field list configured in the AlarmControl Selection tab.

Operators supported in the WHERE clause (per the SQL-92 subset used by the AlarmControl):

• Comparisons: =, <>, <, <=, >, >=
• Logical: AND, OR, NOT
• Set: IN ( ... )
• Pattern: LIKE with % (zero or more chars) and _ (single char)
• Range: BETWEEN ... AND ...

Archive Connection Checklist

After the wildcard correction, if the archive list is still empty, walk through the following configuration items in order:

1. ODBC DSN: Open odbcad32.exe on the WinCC server. Confirm the DSN CC_WinCC_Alg exists, points to the SQL Server instance, and the "Test connection" succeeds with the configured WinCC service account.
2. SQL permissions: The WinCC runtime user (default CCSysUser) must have db_datareader on the CC_Alg_<ServerName>_<Timestamp> database. Missing SELECT permission on the message view will cause the same silent zero-row behavior.
3. Segment configuration: In WinCC Explorer → "Alarm Logging" → "Archives" → right-click the archive → "Properties" → "Segments" tab. Confirm at least one segment is enabled and that the path exists on the server. An archive with no segments will return no rows.
4. Time range: The AlarmControl's "Time range" setting in the Selection tab can be set to "All", "Last hour", "Today", "Custom". A "Today" filter applied to an archive that holds yesterday's segments will return zero rows.
5. Server vs client: On multi-server projects, the AlarmControl must be configured with the correct server prefix. Use the \<ServerName>:: syntax in the Server prefix field if mixing server-side and client-side alarm sources.
6. WinCC service state: Both CCAlgLogServer and CCMsgServ must be in "Started" state. The AlarmControl queries fail silently if either is stopped.

Field Sanitization for Free-Text Inputs

The user's project reads dropdown values, which are trusted, but the pattern below shows how to harden the LIKE input if free text is added later:

VBScript
Function SafeLike(sIn)
    Dim s
    s = Replace(sIn, "'", "''")   ' escape single quote (T-SQL)
    s = Replace(s, "--", "")      ' remove SQL comment marker
    s = Replace(s, "/*", "")      ' remove block comment start
    s = Replace(s, "*/", "")      ' remove block comment end
    SafeLike = s
End Function

Text_Filter = SafeLike(HmiRuntime.Tags("Text_Filter").Read)
Item.MsgFilterSQL = "TEXT1 LIKE '%" & Text_Filter & "%'"

Note that the percent sign in the surrounding pattern is fixed in the script — do not allow the user to inject % or _ into Text_Filter, otherwise any pattern can be matched and the filter loses its purpose.

Advanced Filter Examples

The corrected syntax scales to more complex conditions. The following examples are confirmed to work on the WinCC V7.x AlarmControl:

VBScript
' Date range filter using BETWEEN
Item.MsgFilterSQL = "DATETIME BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59' AND CLASS IN (1,2)"

' Multiple text columns with OR
Item.MsgFilterSQL = "(TEXT1 LIKE '%Pump%' OR TEXT2 LIKE '%Pump%') AND STATE IN (1,2)"

' Negation with NOT
Item.MsgFilterSQL = "CLASS IN (1,2) AND TEXT1 NOT LIKE '%Maintenance%'"

' Single-character wildcard with _
Item.MsgFilterSQL = "TEXT1 LIKE 'Error_01'"

' Priority range with BETWEEN
Item.MsgFilterSQL = "PRIORITY BETWEEN 1 AND 5 AND STATE = 1"

Compatibility Note: WinCC V6.x, V7.0 SP2, and V7.5

The % wildcard requirement is documented in the WinCC V7.0 SP2 help and has not changed through V7.5. Projects that were upgraded from WinCC V6.x, which used the older VBScript native Like operator with * and ?, will retain the V6-style wildcards in VBScript Like comparisons but must use % and _ in MsgFilterSQL. The two operators are independent:

When the source code is ported between the two contexts, the wildcards must be translated. This is the most common upgrade-time regression that produces the exact symptom in this article.

Verification Matrix

Frequently Asked Questions