Description
In some cases, a list action may not return all the fields you would like in your report. If the fields you want are available from another list action, then it is possible to create a new report with the combined data. There are multiple ways to do this including:
- Use a program script with logic to combine the results after running the separate actions in the script
- Use CSV Command Line Interface (CLI) actions like convertCsv or copyCsv to manipulate the data to what you want perhaps also using program script
- Use a simple SQL database query to get exactly the report you want - this is what we discuss here
Prerequisites
- This assumes you have create table and query access to a database like Postgres, MySql, MS SQL Server, Oracle, or similar. We use Postgres here which is a bit easier as ACLI has a built-in Postgres JDBC driver. If you have another database, you need to configure the
dbJarparameter to point to a JDBC driver available for you database either by URL or adding to yourlibs/jdbcdirectory in the ACLI installation. - ACLI 10.4 or higher. Database support for list actions has been supported for many versions, however, the last part of the example uses a new action available starting with Release Notes 10.4:
getSqlResultList.
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 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 ***"
Examples
We are going to use a Confluence example here, but this can be done with any list actions. We are going to combine data from getPageList and getUserList into a custom report
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