How to combine results from multiple list actions using SQL

Owned by Bob Swift
Last updated: Aug 31, 2022 by Elena Amandola (Deactivated)
3 min read

Description

In some cases, a List action does not return all the fields that you require in your report.
If the required fields are available from another List action, you can create a new report with the combined data.
Multiple ways to do this include to:

  1. Use a program script with logic to combine the results after running the separate actions in the script.
  2. Use CSV Command Line Interface (CLI) actions like convertCsv or copyCsv to manipulate the data. You might  want also to use a program script.
  3. Use a simple SQL database query to get exactly the report you want.

In this article, we discuss using a SQL database query to specify the fields for your report.


Prerequisites 

To create the SQL query, we assume that you have:

 1. Installed ACLI 10.4 or higher.
     Support  for List actions is available in previous versions, however, the last part of the example uses a new action getSqlResultList with Release Notes 10.4.

 2. Created a table and query access to a database like Postgres, MySql, MS SQL Server, Oracle, or similar.
     ACLI has a built-in Postgres JDBC driver, the example uses Postgres.
     If you use another database, configure the dbJar parameter to point to the available JDBC driver for your database either by URL or adding to your libs/jdbc directory in the ACLI installation.


Details

You need to configure your database access information in your acli.properties ACLI configuration similar to how you configure site access.
For example, you can:

Add this line

database-test = --url jdbc:postgresql://db.examplegear.com:5432/test --dbUser automation --dbPassword ***

OR

Run this acli action to configure it

acli -a updatePropertyFile --propertyFile @config --field "database-test = --url jdbc:postgresql://db.examplegear.com:5432/test --dbUser automation --dbPassword ***"

Example

List actions can be used to combine data in Confluence or any app.

The example shows how to combine data from getPageList and getUserList into a custom report in Confluence.

Only run on first day of the month
acli myConfluence -a getPageList --space example --outputFormat 2 --database @database-test --dbTable=page_list --dbCreateTable
...
Table 'page_list' created because of create table request.
3 rows inserted. Table was 'page_list'.
acli myConfluence -a getUserList --outputFormat 2 --database @database-test --dbTable=user_list --dbCreateTable
...
Table 'user_list' created because of create table request.
21 rows inserted. Table was 'user_list'.
acli -a getSqlResultList --database @database-test --outputType text \
    --sql 'select title as "Title" 
             , author as "Author"  
             , (select name from user_list where user_list.user=page_list.author  ) as "Author Name" 
             , modifier as "Modifier" 
             , (select name from user_list where user_list.user=page_list.modifier) as "Modifier Name" 
           from page_list 
         '
3 rows in list
 Title            Author      Author Name  Modifier    Modifier Name  
 Home             automation  Automation   automation  Automation     
 This is title 1  automation  Automation   automation  Automation     
 This is title 7  automation  Automation   automation  Automation

If you prefer, put the SQL in a file and then the action becomes:

acli -a getSqlResultList --database @database-test --outputType text --file page-query.sql
page-query.sql
select 
    title as "Title" 
  , author as "Author"  
  , (select name from user_list where user_list.user=page_list.author  ) as "Author Name" 
  , modifier as "Modifier" 
  , (select name from user_list where user_list.user=page_list.modifier) as "Modifier Name" 
from page_list