Working with Alarms and Events Data

Alarms and Events Queries in the Excel Add-In

Querying Alarms and Events data in the Excel Add-In retrieves alarms and events data according to your Query Criteria. Three query types are available: Alarm, Alarm history, and Events. For more detail refer to the Alarm Query Types topic.

Note: At the moment Alarm & Event data from FIX32 SCADA systems is not available through the Excel Add-In. To retrieve this data, you must use OLE DB. For more information, refer to Using the OPC AE Collector with FIX32 SCADA Collectors.

Querying Alarms and Events Data in the Excel Add-In

Procedure

  1. From the Historian menu, choose Query Alarms & Events.
    The Query Alarms & Events window appears.
  2. Select a Server. Your default server should be selected. To set the selected server as default, ensure the Set Server to Default option is enabled.
  3. Select from Query Type.
  4. Select a Query Criteria.
  5. Select your Output Display and Sorting.
  6. Select OK.
    Note: Excel limits the amount of data that can be entered into a formula or returned into a worksheet. The following are guidelines for formula and result limits in various versions of Excel. Output is limited to:
    • Excel XP: 32767 cells
    • Excel 2003 32767 rows

Alarm Query Types

Three query types are provided by the Excel Add-In. These types are described in the following table.

Query TypeDescription
AlarmsIn Historian, an alarm's entire life cycle is stored as a single record in the alarm archive. Thus, when retrieving from the archive, the entire life cycle of an alarm will be returned in a single record.
Alarm HistoryIf the Alarm History query type is chosen, each change in the alarm's state will be returned in a single record.
EventsOne row per event is returned to the Excel spreadsheet.

Query Criteria

The Excel Add-In can be set up to filter by one or more of an alarm's attributes with the Query Criteria section of the window. For example, you may want to include alarms where the Alarm ID is equal to a specific Alarm ID occurring after a specific start time.

Several query criteria are provided by the Excel Add-In to retrieve alarms and events data from Historian. In addition to specifying which criteria to use in your query, you can specify which attributes will be displayed, and how the results are sorted in your Excel spreadsheet.

Filtering Alarms and Events Data

Procedure

  1. Open the Query Alarms & Events window.
  2. Select a Query Criteria.
  3. From the Add query criteria where... list box, select a query condition. Available query conditions are:
    • is Less Than or Equal to
    • is Greater Than or Equal to
    • is Equal To
    • is Not Equal To
    Note: Some conditions may not be available for some query criteria
  4. In the value or cell... field, enter a comparison value. This can be a specific value, or a cell reference.
  5. Select the Add to Query button. The query criteria will appear in the Retrieve alarms where... list.
  6. To add multiple query criteria, repeat steps 2-5. Each criteria will be added with an AND operator.
  7. To remove a query criteria, select it from the list and select on Remove Selected.
    Consult your OPC Alarms and Events server documentation for more information on which attributes it provides.

Output Display and Sorting

The Output Settings section of the Alarm Query window is separated into five sections.

Output Range

Select in the Output Range field and select a range of cells in a single row or column to determine where the returned data is placed.

Output Orientation

Select either Columns or Rows for the output display. Selecting Columns displays a table of values with parameters arranged in columns with header labels at the top. Selecting Rows rotates the table 90 degrees.

Maximum Results

Enter a maximum number of results for the query to return

Note: The Excel Add-In will not display more than 255 columns and 32,767 rows when displaying results.

Output Display

The Output Display section specifies which attributes the Excel Add-In should return to the spreadsheet. Multiple selections can be made by holding the CTRL key and selecting.

Output Sorting

Before displaying the returned alarms and events data in your Excel spreadsheet, the Excel Add-In can sort the values according to the criteria you specify in the Output Sorting section. The following table describes how the different sorting options sorts values:

Sorting OptionsValues Displayed
Alarm TimeSorts the returned alarms or events by the alarm or event's Start Time attribute. The results will be sorted in descending order.
Custom SortAllows you to select which fields to sort the returned alarms and events by. You can also specify whether to sort in ascending or descending order. Multiple sort conditions are supported.
NoneThe returned alarms and events are not sorted at all. They will be returned in the order they are received from the alarms and events database.

Sorting Alarms and Events Data by Specific Attributes

Procedure

  1. In the Sort By section, select Custom Sort.
  2. Select a sortable attribute. Multiple selections can be made by holding the CTRL key and selecting.
  3. Select on Sort-> to move the selected attribute(s) to the Attribute Sort list box.
  4. Select in the Check for Descending check box to sort an individual attribute in descending order. The default is to sort the attribute in ascending order.
  5. Returned values are sorted from the top down. For example, if the Attribute Sort field contained the values Start Time, Data Source Name, and Condition Name, the results would first be sorted by the start time, then by the data source, then by the condition name.
    To change the order of sorting, select an attribute in the Attribute Sort list box, and select Up or Down.

Joining Alarms and Events Data with Tag Data (Excel Add-In)

The Excel Add-In allows you to retrieve limited Alarms and Events data when you query tag data from the Historian archive. The available Alarms and Events data appears as additional options in the Output Display list box in the Query Current Values, Query Raw Data, Query Calculated Data, and Query Filtered Data windows.

Two options for alarm data appear:

  • Alarm Message
  • Alarm ID

Importing Alarms and Events Data in the Excel Add-in

About this task

Alarms and Events data can be imported into Historian through the Excel Add-In. This is useful to include alarms and events data into the Historian archive that is not normally collected by Historian or when you are migrating data from an older system into Historian.

Note: The Excel worksheet must contain source and timestamp columns as a minimum.

To import alarms and events data in the Historian Excel Add-In:

Procedure

  1. Create a new Excel spreadsheet and populate it with your alarms and events data.
  2. From the Historian menu, select Administration and then Import Alarms.
    A message box appears.
  3. The Historian Excel Add-In will attempt to import the current worksheet. If successful, a window appears confirming the completion of the import function. Select OK to close the window.
    Note: If errors occur on the import, a window appears detailing the issues encountered in the import. If an error occurs in any line of the import, the whole import is aborted.

Exporting Alarms and Events Data in the Historian Excel Add-In

About this task

Historian Alarms and Events data can be exported as XML or CSV files or to a new worksheet. Exporting alarms and events data is similar to querying alarms and events data, and generally has the same query types and criteria.

To export alarms and events data in the Historian Excel Add-In:

Procedure

  1. From the Historian menu, choose Administration and then Export Alarms. The Historian Alarm Export window will appear.
  2. Select a Server. Your default server should be selected.
  3. Select a Query Type.
  4. Select Query Criteria.
  5. Select your Output settings and maximum results.
  6. Select your Export Options:
    • To New Worksheet will export the alarms and events data to a new Excel worksheet with the supplied file name.
    • To CSV File will export the alarms and events data with comma separated values to a new file with the supplied file name.
    • To XML File will export the alarms and events data to a new XML file with the supplied file name.
  7. Select OK to export.

OLEDB Provider and Historian Alarms and Events

The Historian OLE DB provider has been extended to include alarms and events data. For more information refer to: