Reading Alarm Timestamps from WinCC Archive Using VB Script

Overview: Reading Archived Alarm Timestamps from WinCC

Siemens SIMATIC WinCC stores every alarm, failure, and operator event generated by the runtime in the Alarmlogging database. When long-term archiving is enabled, completed messages are rotated into the Alarmlogging Archive (a Microsoft SQL Server database). To retrieve the exact timestamp of an archived message from a VB Script — for example, to display it in a custom dialog, log it to a third-party database, or correlate it with a tag value from the Taglogging Archive — use the WinCC OLE DB Provider delivered with the SIMATIC WinCC Connectivity Pack option.

The WinCC OLE DB Provider exposes the Alarmlogging and Taglogging archives as OLE DB data sources, which means any ADO-capable client — including WScript, CScript, or VB Script running inside WinCC — can connect, issue SQL queries, and read back the MSGDATE, MSGTIME, and MSGMT columns that hold the date and time the alarm was raised, cleared, and acknowledged.

Prerequisites

Before writing the script, verify the following on the engineering and runtime stations:

1. WinCC runtime with Alarmlogging. Confirm that alarms are being generated and that the Alarmlogging Editor shows a configured archive (right-click Archives in the WinCC Explorer Alarmlogging tree).
2. SIMATIC WinCC Connectivity Pack license and install media. The option installs the WinCC OLE DB Provider (CLSID {CC220A0F-46F5-11d1-9DDB-006097D50408}, ProgID WinCCOLEDBProvider.1), the Connectivity Pack documentation, and the reference project.
3. Reference sample from the setup DVD. The Connectivity Pack ships with a complete example at <InstallationDVD>\samples\Connectivity Pack\DemoProject. Use it to confirm the connection string and SQL syntax against your installed version before adapting the code.
4. MDAC / Windows Data Access components. VB Script uses ADODB.Connection and ADODB.Recordset, which require the Microsoft Data Access Components to be present. On modern Windows versions (Windows 10 / Windows 11 / Windows Server 2016 and newer), these are part of the operating system.
5. Read access to the archive SQL Server instance. The archive user must be a member of the SQL Server role that can SELECT from the archive database. The default user is WinCCUser for local access; remote access requires the WinCC archive SQL Server to allow SQL authentication for that user.

How the WinCC OLE DB Provider Exposes the Archive

The provider is a registered OLE DB data source that maps the WinCC runtime archives onto a virtual SQL surface. Two archive families are reachable:

• Alarmlogging Runtime Database — the live, in-memory alarm buffer that the WinCC Alarm Control reads from.
• Alarmlogging Archive — the long-term SQL Server database where WinCC swaps out closed/alarm-completed messages for permanent storage.
• Taglogging Runtime Database — current tag values, for correlating an alarm with the process value at the moment the alarm fired.
• Taglogging Archive — historical tag values, for plotting the analog value around the alarm timestamp.

Each archive is addressed through a connection string that the Connectivity Pack documentation describes. The generic pattern for local access is:

Provider=WinCCOLEDBProvider.1;
Catalog=CC_Alg_<ComputerName>_<RuntimeDBName>_<TimeStamp>_<Random>;
Data Source=<SQLServer>\WinCC;
User ID=<WinCCArchiveUser>;
Password=<Password>;

The Catalog parameter names the runtime database that contains the alarm tables; archived alarms live in databases whose names start with CC_ALG_. To find the correct Catalog name at runtime, connect first to the master catalog and query MSysObjects or use the WinCC Archive Connector configuration tool.

Step-by-Step: Reading an Alarm Timestamp in VB Script

Step 1 — Create the OLE DB Connection

Open an ADODB.Connection and pass the provider connection string. The script below is written so it can be pasted into a WinCC button action (Global Script) or a standalone .vbs file run by CScript.

' --- WinCC_AlarmTime.vbs ---
Option Explicit

Dim conn, rs, sConn, sSQL, sResult

' Adjust the Catalog to the Alarmlogging runtime database on this station.
' Use the Connectivity Pack tool "WinCC Archive Connector" to enumerate Catalog names.
sConn = "Provider=WinCCOLEDBProvider.1;" & _
        "Catalog=CC_Alg_LOCALHOST_DEFAULT_ARCHIVE_2024_01_01_12_00_00_R;" & _
        "Data Source=.\WinCC;" & _
        "User ID=WinCCUser;" & _
        "Password=YourPassword;"

Set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 10
conn.CommandTimeout    = 30
conn.Open sConn

Step 2 — Query the Alarm Timestamp Columns

The Alarmlogging tables expose message date and time as separate integer columns. The Connectivity Pack documentation lists the following timestamp columns on the ALVIEW / message table:

The pattern below retrieves the last 20 raised alarms and reads back MSGDATE and MSGTIME for each row. The query is the standard Alarmlogging SQL idiom and is documented in the Connectivity Pack manual under "SQL examples for Alarmlogging".

sSQL = "SELECT TOP 20 MSGNR, MSGTEXT, MSGDATE, MSGTIME, MSGMT, STATE " & _
       "FROM ALVIEW " & _
       "WHERE STATE <> 0 " & _
       "ORDER BY MSGDATE DESC, MSGTIME DESC, MSGMT DESC;"

Set rs = CreateObject("ADODB.Recordset")
rs.Open sSQL, conn, 3, 1   ' adOpenStatic, adLockReadOnly

Step 3 — Convert the Date and Time to a Readable String

The Alarmlogging MSGDATE and MSGTIME columns are stored as integers. The WinCC OLE DB provider supports a helper syntax that converts them to a string in the same step. The Connectivity Pack documentation shows two equivalent methods:

1. Format function in SQL:

sSQL = "SELECT TOP 1 " & _
       "  MSGNR, " & _
       "  MSGTEXT, " & _
       "  CONVERT(VARCHAR(10), DATEADD(dd, MSGDATE, '1899-12-30'), 120) AS MsgDate, " & _
       "  CONVERT(VARCHAR(12), MSGTIME / 10000) + ':' + " & _
       "  RIGHT('0' + CONVERT(VARCHAR(2), (MSGTIME / 100) % 100), 2) + ':' + " & _
       "  RIGHT('0' + CONVERT(VARCHAR(2), MSGTIME % 100), 2) + '.' + " & _
       "  RIGHT('00' + CONVERT(VARCHAR(3), MSGMT), 3) AS MsgTime " & _
       "FROM ALVIEW " & _
       "WHERE MSGNR = 1000001 " & _
       "ORDER BY MSGDATE DESC, MSGTIME DESC;"

2. Read raw integers and convert in VB Script — useful when you want to keep the values in VB for further arithmetic (time-difference between two alarms, correlation with a tag value, etc.):

Do While Not rs.EOF
    Dim nDate, nTime, nMilli, nState, sWhen
    nDate  = rs.Fields("MSGDATE").Value      ' days since 30.12.1899
    nTime  = rs.Fields("MSGTIME").Value      ' HHMMSS as integer
    nMilli = rs.Fields("MSGMT").Value        ' 0..999
    nState = rs.Fields("STATE").Value

    ' Combine into a Windows Date using DateAdd
    Dim dtAlarm
    dtAlarm = DateAdd("d", nDate, CDate("1899-12-30"))
    dtAlarm = DateAdd("h", Int(nTime / 10000), dtAlarm)
    dtAlarm = DateAdd("n", Int((nTime / 100) Mod 100), dtAlarm)
    dtAlarm = DateAdd("s",     (nTime Mod 100),       dtAlarm)
    dtAlarm = DateAdd("s", nMilli / 1000,        dtAlarm)

    sWhen = FormatDateTime(dtAlarm, vbLongDate) & " " & _
            DateAndTime.TimeString          & "." & _
            Right("00" & CStr(nMilli), 3)

    HMIRuntime.Trace sWhen & vbTab & rs.Fields("MSGNR").Value & vbTab & rs.Fields("MSGTEXT").Value
    rs.MoveNext
Loop

The line DateAndTime.TimeString returns the system clock in 24-hour "HH:mm:ss" format. The property is culture-invariant, so it always uses the 24-hour clock with leading zeros regardless of the regional settings of the WinCC station. The full reference is in the Microsoft.VisualBasic.DateAndTime.TimeString documentation. Use it when you need a fast, non-localized wall-clock string for the local script execution time — for example, as a "retrieved at" marker on a report generated from archived alarms.

Step 4 — Close the Connection

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

Failing to close the connection leaks SQL Server sessions. WinCC Global Script reuses the script host process, so a leak in a frequently-fired button action eventually exhausts the SQL connection pool.

Time-Conversion Reference Table

Reading Tag Values Around the Alarm Time

Often the alarm time is only half of the answer — the operator also wants the process value at the moment the alarm fired. Open a second ADODB.Connection against the Taglogging Archive, then issue a query that pins a timestamp with WHERE clauses on the time range:

sConn2 = "Provider=WinCCOLEDBProvider.1;" & _
         "Catalog=CC_TLG_<ComputerName>_DEFAULT_...;" & _
         "Data Source=.\WinCC;User ID=WinCCUser;Password=YourPassword;"

sSQL = "SELECT TIMESTAMP, REALVAL, MS " & _
       "FROM TAG_<ArchiveName>_<TagNo_Range> " & _
       "WHERE TIMESTAMP BETWEEN '" & sStart & "' AND '" & sEnd & "' " & _
       "ORDER BY TIMESTAMP;"

Taglogging stores TIMESTAMP as DATETIME directly, so the join with the alarm timestamp is a string-comparison once both have been normalized to YYYY-MM-DD HH:MM:SS. The Connectivity Pack documentation lists the exact Taglogging column names per archive (they depend on the tag archive configuration; check the WinCC Explorer under Tag Logging > Archives > Properties for the archive name used in the SQL FROM clause).

Filtering for State Changes

The STATE column is a bitmask. Filtering on it lets you retrieve only the timestamp when the alarm came in, went out, or was acknowledged:

To read only the raise timestamp, add WHERE (STATE & 1) = 1. To read only the acknowledgement, add WHERE (STATE & 4) = 4. The Connectivity Pack manual documents the full bit assignments.

Common Errors and Their Causes

Verification

1. Smoke test the provider. From a command prompt on the WinCC station, run a minimal script:

   cscript //nologo WinCC_AlarmTime.vbs

   The script should print at least one alarm row with a sensible timestamp.
2. Cross-check against the Alarm Control. Open the WinCC Alarm Control on the same station, sort by Date/Time descending, and verify that the most recent message in your script output matches the most recent message in the Alarm Control. The raise timestamp of the live alarm should match within one second of the MSGDATE/MSGTIME pair returned by the script.
3. Cross-check against SQL Server Management Studio. Connect to the archive database directly with the same User ID and run the same SELECT. If the SSMS result set matches the script output, the connection string is correct.
4. Verify the 24-hour clock for the "retrieved at" stamp. Confirm that DateAndTime.TimeString returns HH:mm:ss with leading zeros (e.g. 09:05:02, not 9:5:2 AM) — see the Microsoft.VisualBasic.DateAndTime.TimeString reference.
5. Leak test. Run the script from a button with a one-second cycle for several minutes; monitor SQL Server with sp_who2 and confirm that the connection count does not grow.

Field-Proven Tips

• Use the demo project as a contract. The path <DVD>\samples\Connectivity Pack\DemoProject referenced in the Connectivity Pack documentation is the only place where the connection string and SQL syntax are guaranteed to match the installed provider version. If the demo project queries something and your script does not, copy the demo and modify it — do not invent new SQL.
• Prefer ALVIEW over the raw ALERT table. ALVIEW is the documented read-only view; the underlying tables have a different layout per WinCC version.
• Log to WinCC tracing during development. HMIRuntime.Trace "..." writes to the WinCC diagnostic file (default <WinCCProject>\<ComputerName>\WinCC_Sys_01.LOG). It is faster than the Alarm Control for high-frequency retrieval tests.
• Do not run the script from Application.OnTime events without batching. The Connectivity Pack rate-limits at the provider level; flood the provider and queries will queue and time out.

Frequently Asked Questions