Description

In some cases, a list List action may does not return all the fields that you would like require in your report.
If the required fields you want are available from another list List action, then it is possible to you can create a new report with the combined data. There are multiple
Multiple ways to do this includinginclude:

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 to what you want perhaps also using . You might  want also to use a program script.
3. Use a simple SQL database query to get exactly the report you want - this is what we discuss here

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 will need to configure your database access information in your acli.properties ACLI configuration similar to how you configure site access.
For example add , you can:

Add this line:

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

OR

Or run 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 ***"

Examples

...

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.

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

...