Using QuerySurge Integration with Azure DevOps (Versions: 8.2+)

Versions: 8.0+

QuerySurge and Build Automation / Continuous Integration

Data Warehouse/Big Data teams often use Build Automation and/or Continuous Integration tools as part of their development and build processes. Frequently, as teams mature in their development processes, there is interest in including data testing and/or data verification in the automated build process. Organizations using Azure DevOps Pipelines to automate their builds can take advantage of the QuerySurge Integration with Azure DevOps extension from the Visual Studio Marketplace to integrate QuerySurge with their build processes.

In This Section

• Requirements
• Installation
• Integrating QuerySurge with Azure DevOps Pipelines
  • Part 1: Create a new pipeline
  • Part 2: Add the Run Test Suite Task in YAML
  • Part 3: Add the Get Scenario Results Task in YAML
  • Part 4: Add the Publish Test Results Task in YAML
  • Part 5: Save and Run the Pipeline
• HTTPS Configuration
• Exporting Certificates
• Certificate Storage for Azure Pipelines
• Configuring CA Certificate Bundle Location in the Pipeline
• Troubleshooting
• Task Parameter Reference
  • Common Parameters
  • Run Test Suite task
  • Get Scenario Results task

Build Automation with Azure DevOps Pipelines and QuerySurge Testing

Integrating QuerySurge into a new or previously created pipeline is achieved using the QuerySurge Pipeline Tasks extension from the Visual Studio Marketplace. Once the extension is installed in your Azure DevOps organization, two tasks will be visible in the pipeline editor, under the list of available build tasks:

• The QuerySurge Run Test Suite task calls in to QuerySurge to execute the specified test test and offers flexible execution and pipeline integration options
• The QuerySurge Get Scenario Results task retrieves scenario detailed results from QuerySurge and attaches them to the build for inclusion under the Tests and Coverage section, as well as several other formats.

Requirements

Several pre-requisites must be met to deploy and use the QuerySurge Integration with Azure DevOps extension:

• Installation must be performed by a Project Collection Administrator or an organization owner
• Network connectivity from the build agents to the QuerySurge App Server:
  • Self-hosted agents (recommended): It must be possible to connect from the agent machine to the QuerySurge App Server at its configured HTTP port (this can be tested with a web browser on the agent machine).
  • Microsoft-hosted agents: Your QuerySurge server must be accessible over the internet from IP ranges used by Azure services. Microsoft publishes a weekly-updated listing of such IP addresses. (This option is not generally recommended for security reasons - it requires exposing the QuerySurge server to external Azure networks outside the corporate network.)
• QuerySurge version 8.2 and higher
• For QuerySurge deployments with HTTPS configured, additional configuration is required in the pipeline to supply the certificate chain, as described below.
• A QuerySurge user with the appropriate project role for executing tests (Standard user or Project Admin user) in the desired QuerySurge Projects. (See more info on users and roles and adding users to projects.)
• Knowledge of Azure DevOps and YAML pipelines (legacy pipelines are not currently supported)
• A source code repository supported by Azure Pipelines (examples below use Azure Repos Git)

Installation

To deploy the QuerySurge Integration with Azure DevOps extension in the cloud-hosted Azure DevOps service, follow these steps (for slight variations when installing in on-premises Azure DevOps Server, see Microsoft's documentation):

1. Navigate to the Visual Studio Marketplace page for the QuerySurge Integration with Azure DevOps extension.
2. Click the Get it free button
3. Select your Azure DevOps organization (if more than one organization is listed, ensure the correct one is chosen here).
4. Click Install.
5. After installation completes, click Proceed to organization. You can verify installation by navigating to Organization Settings -> Extensions.

Integrating QuerySurge with Azure DevOps Pipelines

In this example, we will walk through the steps to create a new YAML pipeline that runs a test suite of QueryPairs as a scenario, waits for the scenario to complete, retrieve detailed scenario results, and publish the results as test results in the pipeline.

Part 1: Create a new pipeline

As a starting point, we will create a new pipeline that is connected to an existing Git repo in our Azure DevOps project. Follow these steps to create the pipeline:

1. In the Azure DevOps project, go to the Pipelines view.
2. Click New Pipeline or Create Pipeline.
   
3. Under Where is your code?, select Azure Repos Git.
   
4. Select the repository that will store the YAML code for this pipeline.
   
5. Under Configure your pipeline, select Starter pipeline. You will see the pipeline editor populated with sample YAML code.
   
6. You should now see the pipeline editor populated with sample YAML code.
   

Part 2: Add the Run Test Suite Task in YAML

Next, we will work on the execution logic for our pipeline by adding the QuerySurge Run Test Suite task.

1. Clear the sample YAML code from the pipeline editor.
2. Paste the following snippet into the editor. Replace AgentPoolName with the name of a pool of agents in your Azure DevOps organization that can access your QuerySurge instance.
   

   trigger: none
   
   pool:
     name: 'AgentPoolName'
   
   steps:
   
   

3. If the task list on the right-hand side is not visible, click Show Assistant to open it.
   
4. Type QuerySurge in the search box, then click the QuerySurge Run Test Suite task.
   
5. Fill out these values in the Server/Login Details section:
   QuerySurge hostname: Hostname/FQDN/IP address of QuerySurge server, such that it is accessible from build agent(s) (ex. qs.mycorp.com)
   QuerySurge port: HTTP Port of QuerySurge server (ex. 80, 8080, 443, 8443)
   Note: To connect to the QuerySurge server via HTTPS (TLS/SSL), additional configuration is required, as described below.
6. We will use variables to securely store the QuerySurge credentials. Click Variables, and then New Variable.
   
   
   
7. For Name, enter qs_username; for the Value, enter your QuerySurge username.
8. Check the box for Keep this value secret, then click OK.
   
   
9. Click the plus icon for New Variable. Repeat the process for a variable named qs_password, with your password as the value, and again specifying that this is a secret value.
10. Click Save
11. Fill in the remaining fields in the Server/Login Details section with references to the variables:
    QuerySurge username: Enter $(qs_username)
    QuerySurge password: Enter $(qs_password)
12. Populate the Execution Options section as follows (leave the remaining options as default for now):
    Project ID #: ID number of the QuerySurge project where the test suite to be executed resides (a Global Admin user can find this value in Global Administration -> Projects)
    
    Test Suite ID #: ID number of the test suite to execute (can be found in the Scheduling Dashboard, underneath the test suite's name in the left-hand side list)
    Scenario Name: Name of scenario (to identify the scenario in the Run Dashboard)
13. Place the cursor in the pipeline editor on a new line immediately after steps:, and click Add. You'll see a new querysurge-run-suite-task step added to the YAML code.
    
14. Place the cursor before inputs: and press enter. On the new empty line, ensuring that the line is indented with (exactly) two spaces, add the text name: runTestSuiteTask.
    

Part 3: Add the Get Scenario Results Task in YAML

After the scenario execution completes, our pipeline should retrieve the detailed scenario results from QuerySurge.

1. Above the tasks list, again search for QuerySurge, then click the QuerySurge Get Scenario Results task.
2. Fill in the Server/Login Details section as done in Part 2.
3. Populate the Execution Options section as follows:
   Project ID #: ID number of the QuerySurge project where the executed test suite and scenario reside (a Global Admin user can find this value in Global Administration -> Projects)
   Scenario Execution ID: Enter the text $(runTestSuiteTask.execId). This expression references an output variable named execId set by the Run Test Suite task, which we named runTestSuiteTask above.
   
4. In the pipeline editor, ensure the cursor is placed on a new line after the previous task, and then click Add. You'll see a new querysurge-scenario-results-task step added to the YAML code.
   

Part 4: Add the Publish Test Results Task in YAML

The previous task added to the pipeline retrieves results from QuerySurge in a format that Azure Pipelines can understand, but it is not yet processed. Now we will use the built-in Publish Test Results task to instruct the pipeline to publish the results for display in the build results.

1. Again, above the tasks list, search for Publish Test Results, then click that task in the results.
   
2. Fill in the configuration as follows (leave all other fields at their default values):
   Test result format: Select JUnit
   Test results files: Enter the text **/qs-scenarioresultsdetails*.xml
   Search folder: Enter the text $(Agent.TempDirectory)
   
3. In the pipeline editor, ensure the cursor is placed on a new line after the last task, and then click Add. You'll see a new PublishTestResults step added to the YAML code.
   

Part 5: Save and Run the Pipeline

Finally, commit the pipeline code to the source control repository and run the pipeline.

1. Click Save and run. You may enter a commit message describing your work; for example, Add new QuerySurge pipeline. Click Save and Run in the popup.
   
2. On the Summary page, you may be prompted to permit the agent pool to execute this pipeline. Follow the prompts to grant this permission.
   
3. Wait until the run completes.
4. On completion, if the scenario completed successfully, you will see under the Tests and Coverage heading the percentage of passing QueryPairs. Clicking on either the percentage or the Tests tab will bring you to the test summary and list of tests. (The second and third screenshots below show the list filtered to only Failed tests.) You can explore the tests and drill in to failures, and use the other available tools for test and failure analysis.
   
   
   
5. If errors occur, see the Troubleshooting section below.

HTTPS Configuration

To protect communications with the QuerySurge instance, many organizations configure HTTPS/TLS in the QuerySurge app server and other components. In most of these deployments, the QuerySurge Pipeline Tasks must also be configured appropriately in order to securely connect to QuerySurge. 

In this section we will outline the the required format for the HTTPS certificates used by the QuerySurge app server, options for storing the certificates where they can be read by the pipeline tasks, and finally, how to configure the tasks to use the chosen storage location. 

Exporting Certificates

The typical HTTPS setup involves a certificate for the QuerySurge app server itself plus one or more CA certificates, which together form a chain to the root CA. All of these certificates are then stored in a Java truststore on the QuerySurge server. In order to connect the QuerySurge pipeline tasks, the "bundle" of CA certificates must be exported to the OpenSSL-recognized PEM format. Due to the complexities of certificate management, you will most likely need to consult with your internal security organization for assistance.

Following is a sample command sequence based on one specific implementation of HTTPS on Windows (the critical parameter is -rfc, which requests output in PEM format; the type command concatenates the two individual certificates into our single "bundle" file):

"C:\Program Files\QuerySurge\java\bin\keytool" -export -keystore C:\tomcat-tls\my-truststore.jks -alias ca -rfc -file C:\tomcat-tls\ca-export.pem

"C:\Program Files\QuerySurge\java\bin\keytool" -export -keystore C:\tomcat-tls\my-truststore.jks -alias root -rfc -file C:\tomcat-tls\root-export.pem

type C:\tomcat-tls\ca-export.pem C:\tomcat-tls\root-export.pem > bundle-export.pem

Certificate Storage for Azure Pipelines

There are two main possibilities for storing the CA certificate bundle: Other options may be preferred based on your organizations policies.

1. Store CA certificates locally on all build agents: Copy the certificate bundle file to a common path on all machines used as build agents. This option is only available for self-hosted agents, since the filesystem of Microsoft-hosted agents is not available (for pre-deploying the certificates) outside of pipeline runs.
2. Store CA certificates in the source code repository: This is one possible option for using Microsoft-hosted agents. Here the certificate bundle file will be committed to the source code repository. Since the build agents checks out the code on each pipeline execution, the certificate bundle will be placed in the local code checkout directory, whose location is available through the variable Build.Repository.LocalPath.

Other options may be preferred based on your organizations policies. For example, using custom scripts in the pipeline, you may be able to to download the certificate bundle from another location, save it to disk, and reference that location.

Configuring CA Certificate Bundle Location in the Pipeline

After obtaining the CA certificate bundle and storing it in an accessible location, we can now configure the pipeline to read the bundle from the appropriate file path. Add the following snippet to the pipeline YAML, before the steps: section:

variables:
- name: NODE.EXTRA.CA.CERTS
  value: 'path/to/bundle-export.pem'

For option 1 above, the value can be a standard Windows or Linux path that is accessible by all build agents. For example, on Windows, C:\https-conf\bundle-export.pem; on Linux: /opt/https-conf/bundle-export.pem. For option 2, the value should be a location in the source code repository after being checked out by the build agent; for example, $(Build.Repository.LocalPath)/https-conf/bundle-export.pem. This addition will cover all QuerySurge tasks in the pipeline, even if there are multiple Run Test Suite and Get Scenario Results tasks. After saving the code, the pipeline should now be able to connect to QuerySurge over HTTPS.

Troubleshooting

Following are some common error messages and typical resolutions:

• getaddrinfo ENOTFOUND hostname hostname:80
  The specified QuerySurge app server hostname could not be resolved in DNS. Possible causes: Incorrect hostname, using only a machine name instead of the fully-qualified host name, using a Microsoft-hosted build agent when QuerySurge is only accessible from an internal network, DNS issues on the build agent.
• connect ETIMEDOUT hostname:80
  The QuerySurge app server could be reached, but the server did not respond. Possible causes: Incorrect port, QuerySurge app server service not started.
• unable to get local issuer certificate
  Use HTTPS option enabled, but CA certificates not deployed or incorrectly exported.
• Request failed with status code 400 (in job logs: Bad Request This combination of host and port requires TLS.)
  QuerySurge instance has HTTPS enabled and correct HTTPS port is specified, but Use HTTPS option is not enabled.
• Request failed with status code 401 (in job logs: USER ID OR PASSWORD IS INVALID)
  QuerySurge username and/or password are incorrect.
• Request failed with status code 404 (in job logs: Project not found)
  Project with the specified ID does not exist or your QuerySurge user does not have access to it.

Task Parameter Reference

Common Parameters

Parameter Name	YAML Parameter name	Description
QuerySurge hostname	host: string	Hostname/FQDN/IP address of QuerySurge server, such that it is accessible from build agent(s) (ex. qs.mycorp.com)
QuerySurge port	port: integer	HTTP Port of QuerySurge server (ex. 80, 8080, 443, 8443)
Use HTTPS	useHttps: boolean	Whether to connect to the QuerySurge server via HTTPS (TLS/SSL). Additional configuration is required in the pipeline, as described below.
QuerySurge username	user: string	Username of QuerySurge user
QuerySurge password	password: string	Password of QuerySurge user
Project ID #	projectId: integer	ID # of Project (found in Global Administration -> Projects)

Run Test Suite task

Parameter Name	YAML Parameter name	Description
Test Suite ID #	suiteId: integer	ID number of the test suite to execute (can be found in the Scheduling Dashboard, underneath the test suite's name in the left-hand side list)
Scenario Name	scenarioName: string	Name of scenario (to identify the scenario in the Run Dashboard)
Append Time	appendTime: boolean	Whether the current date/time will be appended to the scenario name (time is local to the QuerySurge app server).
Poll until scenario completion	pollingEnabled: boolean	If true, wait until the scenario completes execution, and pass/fail the task based on the option selected for Scenario outcome action. If false, the task will only schedule the scenario.
Scenario outcome action	scenarioOutcomeAction: enum	Whether to affect pipeline execution based on QuerySurge scenario outcome. Only applicable if Poll until scenario Completion is enabled. Available options: Succeed with issues on failed/warning/error/killed outcome: Report "Succeeded with issues" when scenario does not pass Fail on failed/warning/error/killed outcome: Report task failure when scenario has QueryPairs with failures/warnings or scenario is killed Fail on error/killed outcome: Report task failure when scenario has QueryPairs/Staging Queries with errors or scenario is killed Task always succeeds: Pipeline execution not affected by scenario outcome (however, errors connecting to QuerySurge or starting scenario execution will still cause task failure)
Timeout (in seconds)	timeoutSecs: integer	Timeout for scenario execution in seconds. If execution exceeds the timeout, the task will fail. Minimum of 10 seconds; set to 0 or leave blank to disable (i.e. wait indefinitely, up to the pipeline timeout).

Get Scenario Results task

Parameter Name	YAML Parameter name	Description
Scenario Execution ID	execId: string	Execution ID of scenario. Execution ID should be set to the output variable provided by the Run Test Suite Task. For example, if the Run Suite Task's name is executeRevenueTests, the value which should be entered into Scenario Execution ID is $(executeRevenueTests.execId)