Perform Queries on PostgreSQL Tables for SystemLink Test Monitor

This article demonstrates how to perform queries on a PostgreSQL database table that stores SystemLink Test Monitor results.
 

Prerequisites

• NI SystemLink Server must be installed on the server PC.
• The NI SystemLink Server - Test Module must be installed on the server PC.
• The [External] pgAdmin 4 application must be installed.
  • If you are using the default PostgreSQL database that installs with SystemLink, this must be downloaded manually.
  • If you have configured a remote PostgreSQL server, pgAdmin 4 is installed by default.

To perform a query on the Test Monitor results or steps, the following is required:

1. The PostgreSQL server must be registered in pgAdmin 4.
2. Create a query in pgAdmin 4 to filter the results or steps desired.

 

Registering the PostgreSQL Server

If using a remote PostgreSQL server, there should already be a connection in pgAdmin 4. If so, skip to the next section.
Other wise, if the remote PostgreSQL server is not registered, or you are using the default server that comes with the SystemLink installation, follow the steps below:

1. Open pgAdmin 4.
2. Select the Dashboard tab.
3. In the Quick Link section, select Add New Server to add a new connection.

 

4. In File Explorer, navigate to C:\ProgramData\National Instruments\Skyline\Config and open the PostgreSQLDatabase.json file.
5. In the Register - Server window in pgAdmin 4, select the General tab.
6. In the Name field, enter the value of Postgres.Database from the JSON file.

 

7. On the Connection tab:
   • The Host name/address field should be the server's IP address.
   • The Port field should be the value of Postgres.Port in the JSON file.
   • The Username field should be the value of Postgres.User in the JSON file.
   • The Password field should be the value of Postgres.Password in the JSON file.

 

8. Click Save to apply the configuration.
9. Ensure that the connection to the server is active:
   1. On the Dashboard tab, scroll to the Database Activity section.
   2. Identify a session to the server with a State value of active.

 

Querying Test Results using pgAdmin 4

1. Open pgAdmin 4.
2. From the Object Explorer tree, expand Servers >> <server name> >> Schemas >> TestMonitor >>Tables. These are the tables stored from the Test Monitor.
   • If the TestMonitor branch is not present, this is because you have not executed any TestStand sequences since connecting to this PostgreSQL database. Follow the instructions in Integrating Test Monitor with TestStand and then run a TestStand sequence.

 

3. From the Object Explorer panel, select the Query Tool to create a new query.

 

4. Query the results table to find a list of results ordered chronologically. Note: At least one TestStand result must be uploaded to SystemLink.
   1. In the Query window, enter SELECT id, program_name, started_at, operator, status_type to display the id, program_name, started_at, operator and status_type columns from the table.
   2. Press Enter to add a new line.
   3. Enter FROM "TestMonitor".results to specify which table the columns should be pulled from.
   4. Add a new line.
   5. Enter ORDER BY started_at DESC; to specify that the started_at column should be used as a reference for ordering, and rows should be in descending order.

SELECT id, program_name, started_at, operator, status_type
FROM "TestMonitor".results
ORDER BY started_at DESC;

 

5. Run the query by pressing the Execute button.

6. The results will appear in the Data Output window.

 

Querying Step Results Using pgAdmin 4

1. Open pgAdmin 4.
2. From the Object Explorer, expand Servers >> <server name> >> Databases >> <database name> >> Schemas >> TestMonitor >> Tables.
   • For the purposes of this demonstration, a query will be created that finds step information for a particular result.
   • If the TestMonitor branch is not present, this is because you have not executed any TestStand sequences since connecting to this PostgreSQL database. Follow the instructions in Integrating Test Monitor with TestStand and then run a TestStand sequence.
3. Right-click the results table and select View/Edit Data >> All Rows.

4. In the table view, copy one of the values in the id column by double-clicking on the cell and copying.
   • Each result uploaded to the SystemLink Test Monitor has a unique id. The id will be used to find the steps for this particular result.
5. From the Object Explorer, click the Query Tool to create a new query.

 

6. Add the lines shown in the snippet to the Query window. Where:
   • SELECT step_id, parent_id, name, status_type defines that the step_id, parent_id, name and status_type columns should be displayed.
   • FROM "TestMonitor".steps specifies which table the columns should be pulled from.
   • WHERE  result_id = '<Your Copied ID>' specifies which result to pull the steps table from, where <Your Copied ID> is the id copied from step 4.
   • ORDER by CAST(step_id as INT) ASC; ensures that the output data is displayed in ascending order based on the step_id.

 

SELECT step_id, parent_id, name, status_type
FROM "TestMonitor".steps
WHERE result_id = '<Your Copied ID>'
ORDER by CAST(step_id as INT) ASC;

 

7. Run the query by pressing the Execute button.

 

Additional Information

When performing a query, the status_type values are represented by integers. The definition of each status number is illustrated in the table below.