WinCC.net
Home
FAQs
Downloads
Feedback

FAQs

Querying WinCC Alarms/Message Data

Due to the fact that the WinCC alarm data is stored in it's runtime database, it is possible to run an SQL query to pull back specific information into any front end SQL/ODBC compliant package.

NOTE: This document assumes that you have a basic understanding of SQL queries. The query included in this document can easily be adapted to suit your needs. The main purpose of this faq is to demonstrate how such queries are composed and not neccesarily what data is returned.

IMPORTANT: Siemens reserve the right to modify the structure of the database tables. There is no guarantee that the structure will remain the same across versions. All tables in the WinCC database are for read access only. See warranty terms for further information

A standard requirement is the ability to view the top five most frequent alarms. To do this all we need to do is construct a query that can pull back the data from the two databases; the configuration database for items such as the message text, class and type; and the runtime database for the alarm occurrences.

What this document outlines is how to

  1. Link the two databases together
  2. Construct the query to return the top 5 most frequent alarms

Linking the databases

  1. Ensure WinCC is running and your project is is runtime mode
  2. From the Windows Start Menu select Run, type in scview and press OK to start Sybase Central - this is the graphical interface window onto the WinCC databases.
  3. From the Tools menu select Connect to login to the WinCC databases. Login as UserID: dba and Password: sql
  4. You should now see your computer name appear in the tree list in the left pane of the window. Double-click on the computer name to expand the tree. As long as WinCC is started and in runtime, you will see two entries in the list. The first will be pointing at the configuration database and the second is looking at the runtime database.
  5. Double-click on the runtime database entry and navigate to the Remote Servers folder
  6. Double-click on the Add Remote Server entry to start the wizard which will step us through linking the configuration database into the runtime database
    • In the Create Remote Server window enter a meaningful name for this connection, e.g. config. Hit Next
    • In the Specify Server Class window select Sybase Adaptive Server Anywhere from the drop down list. Hit Next
    • In the Define Connection Parameters window select the ODBC radio button. In the Parameters textbox. Hit Next you need to enter the connection details to the configuration database. The minimum details you need to specify are the database name, user id and password. e.g. DSN=CC_WinCCSQL_02-05-02_11:50:24;UID=dba;PWD=sql. Hit Next
    • In the Specify Read Only window you can check the box to ensure that the Configuration database can't be modified by any of your queries. Hit Next
    • In the Add External Logins you don't need to enter anything because the login will be the same. Hit Finish
  7. You should now see your configuration database listed in the main Sybase Central window
  8. Double click on the remote server entry to access the Properties window
  9. Select the Proxy Tables tab
  10. Select the Add button to start the wizard to insert a proxy table
    • On the Create a New Proxy Table window, ensure your remote server is selected in the first drop down box. Hit Next
    • On the Location the Remote Object window find and select the MSMsgs entry in the list box and hit Next
    • On the Select which Columns to Show window do not change anything and hit Next
    • On the Ready to Create the Proxy Table window you may enter a comment for the table if you wish and then hit Finish
  11. Repeat step 10 to add the tables MSClass, MSType & TXTTable

Constructing the query

Now the two databases have been linked together it is possible to create a query to run against the runtime database which can return the data we want to see, namely the top five most frequent alarms.

The basic query is listed below. I would recommend that you start of by pasting the query into Sybase' Interactive SQL window to test that the syntax is correct. Once you have the data returned in InteractiveSQL you can then paste the query into the front end of your choice, e.g. WinCC, Excel, Web Page, etc.

To open Interactive SQL from within Sybase Central, right-click on your runtime database name in the tree list in the left hand pane to see the context specific menu. From this menu select Open Interactive SQL which will open a new window. You can then paste the query into the Command window and hit the Execute button to run the query.

SQL QUERY AGAINST THE RUNTIME DATABASE TO RETURN TOP 5 MOST FREQUENT ALARMS

SELECT top 5 MsgNum=msgnr,
Occurred=count(msgnr),
MsgTxt=(select l9 from dba.txttable tt join dba.msmsgs mm on tt.textid=mm.tb1 where mm.nr = m.msgnr),
Class=(select l9 from dba.txttable tt join dba.msclass c on c.name = tt.textid join dba.msmsgs mm on c.classtyp = mm.class where mm.nr = m.msgnr),
Type=(select l9 from dba.txttable tt join dba.mstype t on t.name = tt.textid join dba.msmsgs mm on t.id = mm.type where mm.nr = m.msgnr)
FROM DBA.MSArcShort m
WHERE status=1
group by msgnr
order by count(msgnr) desc

 

WARRANTY TERMS

If you input your own data/information into our software by means of the interfaces declared open by us or if you connect our software with your data/information, we shall not accept any liability or grant any warranty for the compatibility of your data/information and our software. If errors or problems occur in our software due to compatibility problems, the elimination of such errors or problems will be charged for at our usual rates.

2007 Salma Ghafoor