Querying the database

The Data Log Viewer and the Event Log Viewer query the database, extract logged data, and display that data in Vista.

A query is a set of instructions that the log viewer uses to request particular data from the database. Each log viewer has its own individual query. Editing this query information changes what data the query retrieves and the way a log viewer displays that data.

Right-click the data or event log viewer object and select the Query tab to view the query options or access the Query Wizard. Use the query options to define the way a viewer displays data and the Query Wizard to edit the actual query.

NOTE: Anytime you place and link a new Data Log Viewer or Event Log Viewer, you need to specify a query for it. You can specify a query using the Query Wizard.

Using the Query options

To define the way a log viewer displays information, configure its query options.

To view the query options for an Event Log Viewer or Data Log Viewer, right-click the viewer object to open the Configuration dialog. Select the Query tab.

The upper section of the Query tab offers two buttons:

  • Edit Query starts the Query Wizard, a user-friendly interface that helps you edit the query.

  • Edit SQL accesses the SQL statement via Windows Notepad so you can edit the query directly using SQL, if preferred.

    NOTE: If you edit a query directly (i.e., by editing the SQL statement), your changes may not be accessible to the Query Wizard. This is not a problem unless you intend to use the Query Wizard as well.

The Options section of the Query tab offers the following options:

  • Records uploaded at a time: This option specifies the number of records Vista uploads into active memory (RAM) when you first open a Data or Event Log Viewer. The maximum value is 9999. As you scroll down the list of records, Vista uploads additional records as needed. You may want to change this default value if, for example, you want to select a large number of records for plotting, without scrolling down repeatedly.

  • Update automatically: This option is disabled (cleared) by default. The Data or Event Log Viewer does not display any new records; rather, the Log Viewer initially uploads the specified number of records (for example 100) and does not update again unless it is closed (and its query results deleted) then reopened.

    With this option selected, the Log Viewer continually receives and displays new records up to a maximum of 1000 records; the Log Viewer checks for new records every 20 seconds. However, the updates can consume a noticeable amount of CPU power, especially if several Log Viewers remain open simultaneously or if Log Viewers are uploading waveform data.

  • Delete query on close: This option is enabled (selected) by default. The Log Viewer clears its temporary records from active memory (RAM) upon closing so that every time a Log Viewer is opened, the database is queried again and all available records are displayed. (This option does not affect any original records stored in the database.)

    If this option is disabled, Vista caches the record set returned to the Log Viewer, meaning that if the Log Viewer is closed then reopened, the table is populated from memory and not from the database. Any new records inserted into the database during the time the Log Viewer was initially open do not appear.

    NOTE: Since it is recommended that Update automatically remain disabled in all but the most compelling circumstances, it is important that the Delete query on close option remains enabled.

  • Use parent node: With this option selected, the log viewer uses the same node linkage as the parent window. The node is also pre-selected in the Query Wizard - you should not select this option if you intend to query more than one node.
X-parameter for Log View Plotter

For a Data Log Viewer, the X-parameter for Log View Plotter section is offered at the bottom of the Query tab. Use this section to specify a column to be used on the X-axis of the Log View Plotter. This section offers two options:

  • Timestamp is selected as the X-parameter by default. In some cases, you may need to specify a different X-parameter. For example, when you are plotting disturbances, you need to plot your data against the Duration column.
  • Use column # lets you specify a new x-parameter. In the field provided, type the column number that you want to use as the X-parameter (for example, 3). When you click OK on the Data Log Viewer Configuration box, the specified column changes to blue.

NOTE: In the Data Log Viewer, the column specified for the X-parameter is colored blue for easy identification.

Using the Query Wizard

The Query Wizard helps you edit an existing query or create a new query for a Data Log Viewer or Event Log Viewer.

  1. Right-click the log viewer that you want to configure.
  2. Select the Query tab in the Configuration box.
  3. Click Edit Query to open the Query Wizard.

    The Query Wizard consists of four steps designed to guide you through the process of editing a query. These steps are described in more detail in the following sections.

    • Selecting the node(s) to query.
    • Specifying logs within the specified node(s).
    • Specifying columns and specifying filters.
    • Choosing a sort order.

    Depending on the type of query (new or existing), you may not need to perform all four steps. When you start the Query Wizard, it automatically opens on the appropriate step in the process.

  4. Use the four buttons at the bottom of the Query Wizard to move through or exit the Query Wizard:
    • Use the Back and Next buttons to navigate through the wizard.
    • Click Cancel to quit the Query Wizard and discard your changes.
    • Click Finish to quit the Query Wizard and save your changes.
  5. When finished, click OK to save your changes and close the viewer configuration box.

The following example describes each step in the process of creating a typical query: configuring a Data Log Viewer to display waveforms for three phases (V1, V2, V3).

Step 1: Selecting the nodes

The Available list displays the available nodes (device or software) and the Selected list displays the currently selected nodes.

Select a node (or nodes) for your query. If a node has already been specified or you select Use Parent Node on the Query tab, the Query Wizard proceeds directly to Step 2: Specifying logs.

  1. Click Edit Query on the Query tab of the Log Viewer Configuration dialog to start the Query Wizard.
  2. Highlight the name of the node that you want from the Available list and click Add. The node appears in the Selected list. Repeat this procedure to add additional nodes for this query, if required.

    If you want to remove a node from the Selected list, highlight the node and click Remove. The node disappears from the Selected list.

  3. Click Next to continue.

NOTE: If you want to view or edit a node's SQL statement directly, highlight the node in the Selected list and click the SQL button. The Windows Notepad displays the SQL statement for the specified node.

Step 2: Specifying logs

Specify one or more data logs to query. If a valid query has already been specified, the Query Wizard proceeds directly to Step 3: Specifying columns.

  1. The Available list displays the logs available on the specified node. The Selected list displays the currently selected logs.
  2. Highlight the name of the log(s) that you want from the Available list and click Add. The log(s) appears in the Selected list.

    NOTE: Check for additional tabs. If you selected more than one node in step 1, this box requires you to specify logs for each node. Each tab is labeled “Node 1,” “Node 2,” etc. and is identified at the top of each tab. Select “Advanced log view” to access any logs whose configuration has changed over time. This check box enables access to old configurations of snapshot logs.

  3. Click Next to continue.

Step 3: Specifying columns and filters

Use the Select the columns to appear in the Log Viewer section to add or remove a column from your log viewer and the Filter section to establish a range for your query and to screen out unwanted data.

If you selected multiple logs in step 2, this box contains a tab for each log. Specify the column and filter information for each tab.

Specifying columns to appear in the Log Viewer

The Available list displays the available columns and the Selected list displays the currently selected columns. The Selected list displays the columns in the same order (left to right) that they will appear in the log viewer (top to bottom).

To specify the columns to appear in the Log Viewer:

  1. Highlight the name of the column(s) you want then click Add. The column(s) appears in the Selected list.

    Select the asterisk (*) at the top of the Available list to select all available columns.

    For example, if you want to select some basic columns for the first waveform log you chose in the last step, you could highlight timestamp, node, and V1 in the Available list.

    NOTE: The * column option shows all inputs connected to a recorder for Data Log Viewers. For example, use the * option if you are using a framework and the configuration of the data log is different for several meters. The * option is not available if more than one table is selected, as in the example.

  2. Repeat this procedure for each column that you want (and for each tab).
  3. Adjust the order of the items in the Selected column—highlight the column name you want to move in the Selected (in order) list then click the up or down arrows to move it. The columns are listed from top to bottom in the order they will appear from left to right.

    For example, if you want the Timestamp column to be the first (leftmost) column in your log viewer, highlight “timestamp” in the Selected (in order) section then click the up arrow to move it to the top of the list.

After you have selected the columns you want, proceed to specify filter settings.

Specifying a filter

Use the Filter section to construct one or more filter statements for your log viewer. Filters are useful for restricting the scope of your query or specifying a priority range for alarms.

To edit an existing filter statement, double-click on it.

For example, if you want to instruct the log viewer to display data for the past month (excluding today), you could use the following procedure:

  1. In the first dropdown list on the left, select “timestamp”.
  2. In the second dropdown list to the right, select “BETWEEN”.
  3. In the third dropdown list to the right, select “last_month_to_date()”.
  4. In the fourth dropdown list (on the right), select “today_12AM()”.
  5. Click Insert.

    The following filter definition appears in the area below the filter controls:

    “timestamp” BETWEEN last_month_to_date() and today_12AM()

If you wanted to instruct an Event Log Viewer to display only unacknowledged alarms, create a filter that only displays alarms that have no acknowledgement time (ack-time).

  1. In the first box, select “ack-time”
  2. In the second box, select “IS NULL”
  3. Click Insert.

    The following filter appears in the box below the filter controls:

    “ack_time” IS NULL

As you construct a filter statement, each field offers options based on your selection in the previous field(s). For example, if you select “priority” in the first field, the third field becomes a text field where you can type a numeric value; if you select “timestamp” in the first field, the third field displays time-based options (i.e., now, today 12AM, yesterday, etc.).

The following table lists the time-related functions:

Function Description
now_() The current time.
today_12AM() Today at 00:00:00.
yesterday() Yesterday at 00:00:00.
last_week() Seven days ago at 00:00:00.
this_month() The first day of this month at 00:00:00.
last_month_to_date() The same date of the month one calendar month ago at 00:00:00.
last_month() The first day of the last calendar month at 00:00:00.
last_quarter_to_date() The first day of the month three calendar months ago at 00:00:00.

Note that time-related functions only return one record. To return all of the records for the time period, you also need to specify < today_12AM(). For example, specifying a filter of =yesterday() returns only one record, but a filter specifying >=yesterday() AND <today_12AM() returns all of yesterday's records.

The second field offers a standard set of operators.

Operator-specific data

If you select an operator that requires additional information, Vista displays a dialog where you can specify the required data.

For example, if you select the “node” column in the first field then select the “IN” operator from the second field, a dialog opens requesting that you specify the node you want to use.

If you specify any column other than “node” then select “IN”, a box appears requesting numeric values.

After you have specified a filter, click Next to continue.

Step 4: Choosing a sort order

Specify a sort order. The sort order is the order in which records appear in the viewer.

  1. Highlight the name of the column to sort by in the Available Columns list then click Add. The column appears in the Sort Order list.

    Available Columns displays the columns you chose in step 3.

  2. Double-click on the column in the Sort Order list to select either an ascending or descending sort order.

    The Sort Order list displays the column(s) that dictate the order in which the records are sorted.

  3. Click Finish to save your changes and quit the Query Wizard.
  4. Click OK in the Configuration box to save your changes.