topic Re: AAAR Report Extraction and Publish error in Alfresco Forum

AAAR Report Extraction and Publish error

bagodu — Wed, 04 Apr 2018 11:01:22 GMT

Hi Team ,Could you please help me on resolving the below issue , I was Extracting and Publishing the AAAR report , I ran the command manually , but below error was showing while Extracting (./AAAR_Extract.sh) 2018/04/04 15:33:01 - Modified Java Script Value.0 - Stopped while putting a row on the buf

Re: AAAR Report Extraction and Publish error

fcorti — Mon, 09 Apr 2018 07:27:21 GMT

Hi Suhas,

The error is the following one:

2017/12/04 21:00:05 - HTTP - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : I was unable to save the HTTP result to file because of a I/O error: Connection refused

The Pentaho Server cannot reach the Alfresco Server.
It's a networking issue or a problem related to the configuration of the extraction (in terms of IP addresses or port).

I hope that this will help.

Re: AAAR Report Extraction and Publish error

bagodu — Mon, 09 Apr 2018 09:00:31 GMT

Hi Corti,

Thanks for replying , recently network team has changed the firewall port , we received the notice after the change ,So could you please let me know , where i can configure or see the changes in the port details in Pentaho/AAAR files ?

I have opened the firewall port from source to destination to access our Pentaho reporting tool which is default port 8020(http://IP ADDRESS:8020/pentaho/Login). Is there any other configuration do i need to do ?

Please let me know the configuration details.

Regards

Suhas

Re: AAAR Report Extraction and Publish error

fcorti — Mon, 09 Apr 2018 10:49:21 GMT

It has sense.

You have simply to update two tables: dm_dim_alfresco and dm_reports.
The tables are used to host the some configurations and they contains few lines.

Updating them, everything will work properly again.

Re: AAAR Report Extraction and Publish error

bagodu — Mon, 09 Apr 2018 13:18:00 GMT

Hi Corti,

I have pasted the PostgreSql ,dm_dim_alfresco table fields and dm_reports table fields , Please let me know which values i have to change in both files .

Note : I have pasted only one field values.

dm_dim_alfresco table fields

INSERT INTO "dm_dim_alfresco" ("id", "desc", "login", "password", "url", "url_audit_suffix", "audit_limit", "url_nodes_modified_after_suffix", "url_nodes_mo
dified_before_suffix", "node_limit", "url_cmis_suffix", "url_workflow_definitions_suffix", "url_workflow_instances_suffix", "is_active") VALUES (1, 'Defaul
t Alfresco instance', 'admin', 'admin', 'http://localhost:8080', '/alfresco/service/api/audit/query/alfresco-access?verbose=true', 50000, '/alfresco/service
/AAAR/getNodesModifiedAfter', '/alfresco/service/AAAR/getNodeIdsModifiedBefore', 50000, '/alfresco/api/-default-/cmis/versions/1.1/atom', '/alfresco/service
/api/workflow-definitions', '/alfresco/service/api/workflow-instances', 'Y');

dm_reports table fields

INSERT INTO "dm_reports" ("id", "pentaho_url", "pentaho_login", "pentaho_password", "pentaho_path", "prpt_name", "name", "alfresco_ftp", "alfresco_port", "alfresco_logi
n", "alfresco_password", "alfresco_path", "is_active") VALUES (5, 'http://localhost:8181/pentaho', 'admin', 'password', '/public/AAAR/Reports', 'audit_use.prpt', 'audit
_use.pdf', 'localhost', 2121, 'admin', 'admin', 'alfresco', 'Y');

Our Pentaho reporting tool URL address is http://xxx.xxx.xxx.xxx:8020/pentaho/Login

Alfresco Production server URL address is http://xxx.xxx.xxx.xx:8080/share/page/

AAAR Pentaho reporting tool fetching data from our Alfresco Production server.

Regards

Suhas

Re: AAAR Report Extraction and Publish error

fcorti — Tue, 10 Apr 2018 06:34:37 GMT

You have to update:
"dm_dim_alfresco"."login", "dm_dim_alfresco"."password", "dm_dim_alfresco"."url"
"dm_reports"."pentaho_url", "dm_reports"."pentaho_login", "dm_reports"."pentaho_password", "dm_reports"."alfresco_ftp", "dm_reports"."alfresco_port", "dm_reports"."alfresco_login", "dm_reports"."alfresco_password"

Re: AAAR Report Extraction and Publish error

bagodu — Tue, 10 Apr 2018 10:42:50 GMT

After i made the necessary steps you have mentioned , the script (AAAR_Extraction.sh) ran for 11 Min (previously it ran 3 min) , but again it was showing below error , Could you please let me know , Is there any other configuration i need to do.

2018/04/10 15:38:36 - Modified Java Script Value.0 - Stopped while putting a row on the buffer
2018/04/10 15:38:36 - Modified Java Script Value.0 - Stopped while putting a row on the buffer
2018/04/10 15:38:36 - Modified Java Script Value.0 - Stopped while putting a row on the buffer
2018/04/10 15:38:36 - Modified Java Script Value.0 - Stopped while putting a row on the buffer
2018/04/10 15:38:36 - Modified Java Script Value.0 - Stopped while putting a row on the buffer
2018/04/10 15:38:36 - Modified Java Script Value.0 - Stopped while putting a row on the buffer
2018/04/10 15:38:36 - Modified Java Script Value.0 - tran_Status found. Checking transformation status while script execution.
2018/04/10 15:38:36 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=1, W=13001, U=0, E=0)
2018/04/10 15:38:36 - Get Alfresco audit - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Errors detected!
2018/04/10 15:38:36 - Init audit staging - Finished job entry [Get Alfresco audit] (result=[false])
2018/04/10 15:38:36 - Init audit staging - Finished job entry [HTTP] (result=[false])
2018/04/10 15:38:36 - Init audit staging - Finished job entry [jsonPath] (result=[false])
2018/04/10 15:38:36 - Init audit staging - Finished job entry [Clean audit staging] (result=[false])
2018/04/10 15:38:36 - Init audit staging - Finished job entry [Get alfresco connection params] (result=[false])
2018/04/10 15:38:36 - Get audit - Finished job entry [Init audit staging] (result=[false])
2018/04/10 15:38:36 - Get all on a server - Finished job entry [Get audit] (result=[false])
2018/04/10 15:38:36 - Get all on a server - Finished job entry [get_audit] (result=[false])
2018/04/10 15:38:36 - Get all - Finished job entry [Get all on a server] (result=[false])
2018/04/10 15:38:36 - Get all - Finished job entry [Get all source id] (result=[false])
2018/04/10 15:38:36 - Get all - Job execution finished
2018/04/10 15:38:36 - Kitchen - Finished!
2018/04/10 15:38:36 - Kitchen - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Finished with errors
2018/04/10 15:38:36 - Kitchen - Start=2018/04/10 15:27:19.512, Stop=2018/04/10 15:38:36.588
2018/04/10 15:38:36 - Kitchen - Processing ended after 11 minutes and 17 seconds (677 seconds total).

Extraction complete!

From 05-12-2017 to till ,data extraction is pending.

Re: AAAR Report Extraction and Publish error

fcorti — Wed, 11 Apr 2018 06:58:44 GMT

The longer duration of the extraction is due to the bigger amount of data extracted.
AAAR uses an incremental extraction since the last successful.
11 Minutes is not a big deal and has sense.

The error is not detailed here and it is probably in a previous part of the log.

Please double check.

I hope this helps you.

Re: AAAR Report Extraction and Publish error

bagodu — Thu, 12 Apr 2018 11:00:00 GMT

Hi Corti,

I have scheduled the ./AAAR_Extraction.sh and ./AAAAR_Publish.sh , But when script ran on time , The report was not fully updated .

It ran for upto below mentioned date , and after that it was not running , and also , it was not updated in the Reporting tool, So i manually ran both the script , then also same issue.

2018/04/12 14:02:35 - Load file content in memory.0 - {
2018/04/12 14:02:35 - Load file content in memory.0 - "id":1301570,
2018/04/12 14:02:35 - Load file content in memory.0 - "application":"alfresco-access",
2018/04/12 14:02:35 - Load file content in memory.0 - "user":"jyotsna.chitriv",
2018/04/12 14:02:35 - Load file content in memory.0 - "time":"2018-01-15T14:56:03.440-05:00",
2018/04/12 14:02:35 - Load file content in memory.0 - "values":
2018/04/12 14:02:35 - Load file content in memory.0 - {
2018/04/12 14:02:35 - Load file content in memory.0 - "\/alfresco-access\/transaction\/sub-actions":"readContent"
2018/04/12 14:02:35 - Load file content in memory.0 - ,"\/alfresco-access\/transaction\/action":"READ"
2018/04/12 14:02:35 - Load file content in memory.0 - ,"\/alfresco-access\/transaction\/type":"cmerson"
2018/04/12 14:02:35 - Load file content in memory.0 - ,"\/alfresco-access\/transaction\/path":"\/sys:system\/syseople\/cm:jyotsna.chitriv"
2018/04/12 14:02:35 - Load file content in memory.0 - ,"\/alfresco-access\/transaction\/user":"jyotsna.chitriv"
2018/04/12 14:02:35 - Load file content in memory.0 - }
2018/04/12 14:02:35 - Load file content in memory.0 -
2018/04/12 14:02:35 - Load file content in memory.0 - }
2018/04/12 14:02:35 - Load file content in memory.0 - ]
2018/04/12 14:02:35 - Load file content in memory.0 - }
2018/04/12 14:02:35 - Load file content in memory.0 - ]

child index = 0, logging object : org.pentaho.di.core.logging.LoggingObject@21803752 parent=649b82d1-d762-4051-bf02-28c5027966d7
2018/04/12 14:02:38 - Load file content in memory.0 - Finished processing (I=1, O=0, R=0, W=0, U=0, E=1)
2018/04/12 14:02:38 - Get Alfresco audit - Transformation detected one or more steps with errors.
2018/04/12 14:02:38 - stg_audits.0 - Signaling 'output done' to 0 output rowsets.
2018/04/12 14:02:38 - Modified Java Script Value.0 - Signaling 'output done' to 1 output rowsets.
2018/04/12 14:02:38 - AAAR_DataMart - Commit on database connection [AAAR_DataMart]
2018/04/12 14:02:38 - Get Alfresco audit - Transformation is killing the other steps!
2018/04/12 14:02:38 - AAAR_DataMart - Connection to database closed!
2018/04/12 14:02:38 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
2018/04/12 14:02:38 - Get Alfresco audit - Looking at step: Load file content in memory
2018/04/12 14:02:38 - stg_audits.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
2018/04/12 14:02:38 - Get Alfresco audit - Looking at step: Modified Java Script Value
2018/04/12 14:02:38 - Get Alfresco audit - Looking at step: stg_audits
2018/04/12 14:02:44 - Kitchen - Finished!
2018/04/12 14:02:44 - Kitchen - Start=2018/04/12 14:00:02.720, Stop=2018/04/12 14:02:44.886
2018/04/12 14:02:44 - Kitchen - Processing ended after 2 minutes and 42 seconds (162 seconds total).

Extraction complete!

Could you please help me on this.

Re: AAAR Report Extraction and Publish error

fcorti — Sat, 14 Apr 2018 14:50:34 GMT

The log is not complete and cannot see the reason of the error, but it's around there.
It seems that some data are not correctly imported for some reasons.
If you don't find the way to identify the error, you could delete the audit with id 1301570.
In the following post you can find a suggestion but be aware that this will DELETE some audits.

How to solve the audit data explosion in Alfresco

Re: AAAR Report Extraction and Publish error

bagodu — Tue, 17 Apr 2018 12:46:19 GMT

Hi Corti,

Please see below full scheduled script log details, Still i am not able to get the full data in Reporting tool, it was only showing data upto 2017-12-04

Disclaimer:
We can't be responsible for any damage done to your system,
which hopefully will not happen.

You are going to execute this command:
./kitchen.sh /rep:"AAAR_Kettle" /job:"Get all" /dir:/Alfresco /user:admin /pass:admin /param:get_audit=true /param:get_nodes=true /param:get_workflows=true /log="/root/.kettle/extract/AAAR.log" /level:Rowlevel

2018/04/17 14:00:04 - Kitchen - Logging is at level : Rowlevel (very detailed)
2018/04/17 14:00:04 - Kitchen - Repository and username supplied
2018/04/17 14:00:04 - Kitchen - Start of run.
2018/04/17 14:00:04 - Kitchen - Allocate new job.
2018/04/17 14:00:04 - Kitchen - Parsing command line options.
2018/04/17 14:00:04 - Kitchen - Loading available repositories.
2018/04/17 14:00:04 - RepositoriesMeta - Reading repositories XML file: /root/.kettle/repositories.xml
2018/04/17 14:00:04 - Kitchen - Finding repository [AAAR_Kettle]
2018/04/17 14:00:04 - Kitchen - Allocate & connect to repository.
2018/04/17 14:00:05 - Kitchen - Check supplied username and password.
2018/04/17 14:00:05 - Kitchen - Load the repositories defined on this system.
2018/04/17 14:00:06 - Kitchen - Allocate job...
2018/04/17 14:00:16 - Get all - Start of job execution
2018/04/17 14:00:16 - Get all - exec(0, 0, START.0)
2018/04/17 14:00:16 - START - Starting job entry
2018/04/17 14:00:16 - Get all - Starting entry [Get all source id]
2018/04/17 14:00:16 - Get all - exec(1, 0, Get all source id.0)
2018/04/17 14:00:16 - Get all source id - Starting job entry
2018/04/17 14:00:16 - Get all source id - Opening transformation: [null] in directory [/Alfresco/Utility]
2018/04/17 14:00:16 - Get all source id - Loading transformation from repository [Get all source id] in directory [/Alfresco/Utility]
2018/04/17 14:00:16 - Get all source id - Starting transformation...(file=null, name=Get all source id, repinfo=null)
2018/04/17 14:00:16 - Get all source id - Transformation is pre-loaded.
2018/04/17 14:00:16 - Get all source id - nr of steps to run : 2 , nr of hops : 1
2018/04/17 14:00:16 - Get all source id - Dispatching started for transformation [Get all source id]
2018/04/17 14:00:16 - Get all source id - Nr of arguments detected:0
2018/04/17 14:00:16 - Get all source id - This is not a replay transformation
2018/04/17 14:00:16 - Get all source id - I found 2 different steps to launch.
2018/04/17 14:00:16 - Get all source id - Allocating rowsets...
2018/04/17 14:00:16 - Get all source id - Allocating rowsets for step 0 --> Ids
2018/04/17 14:00:16 - Get all source id - prevcopies = 1, nextcopies=1
2018/04/17 14:00:16 - Get all source id - Transformation allocated new rowset [Ids.0 - Copy rows to result.0]
2018/04/17 14:00:16 - Get all source id - Allocated 1 rowsets for step 0 --> Ids
2018/04/17 14:00:16 - Get all source id - Allocating rowsets for step 1 --> Copy rows to result
2018/04/17 14:00:16 - Get all source id - Allocated 1 rowsets for step 1 --> Copy rows to result
2018/04/17 14:00:16 - Get all source id - Allocating Steps & StepData...
2018/04/17 14:00:16 - Get all source id - Transformation is about to allocate step [Ids] of type [TableInput]
2018/04/17 14:00:16 - Get all source id - Step has nrcopies=1
2018/04/17 14:00:16 - Ids.0 - distribution activated
2018/04/17 14:00:16 - Ids.0 - Starting allocation of buffers & new threads...
2018/04/17 14:00:16 - Ids.0 - Step info: nrinput=0 nroutput=1
2018/04/17 14:00:16 - Ids.0 - output rel. is 1:1
2018/04/17 14:00:16 - Ids.0 - Found output rowset [Ids.0 - Copy rows to result.0]
2018/04/17 14:00:16 - Ids.0 - Finished dispatching
2018/04/17 14:00:16 - Get all source id - Transformation has allocated a new step: [Ids].0
2018/04/17 14:00:16 - Get all source id - Transformation is about to allocate step [Copy rows to result] of type [RowsToResult]
2018/04/17 14:00:16 - Get all source id - Step has nrcopies=1
2018/04/17 14:00:16 - Copy rows to result.0 - distribution activated
2018/04/17 14:00:16 - Copy rows to result.0 - Starting allocation of buffers & new threads...
2018/04/17 14:00:16 - Copy rows to result.0 - Step info: nrinput=1 nroutput=0
2018/04/17 14:00:16 - Copy rows to result.0 - Got previous step from [Copy rows to result] #0 --> Ids
2018/04/17 14:00:16 - Copy rows to result.0 - input rel is 1:1
2018/04/17 14:00:16 - Copy rows to result.0 - Found input rowset [Ids.0 - Copy rows to result.0]
2018/04/17 14:00:16 - Copy rows to result.0 - Finished dispatching
2018/04/17 14:00:16 - Get all source id - Transformation has allocated a new step: [Copy rows to result].0
2018/04/17 14:00:16 - Get all source id - This transformation can be replayed with replay date: 2018/04/17 14:00:16
2018/04/17 14:00:16 - Get all source id - Initialising 2 steps...
2018/04/17 14:00:16 - Ids.0 - Released server socket on port 0
2018/04/17 14:00:16 - Copy rows to result.0 - Released server socket on port 0
2018/04/17 14:00:16 - AAAR_DataMart - New database connection defined
2018/04/17 14:00:16 - Ids.0 - Connected to database...
2018/04/17 14:00:16 - Get all source id - Step [Ids.0] initialized flawlessly.
2018/04/17 14:00:16 - Get all source id - Step [Copy rows to result.0] initialized flawlessly.
2018/04/17 14:00:16 - Ids.0 - Starting to run...
2018/04/17 14:00:16 - Copy rows to result.0 - Starting to run...
2018/04/17 14:00:16 - Get all source id - Transformation has allocated 2 threads and 1 rowsets.
2018/04/17 14:00:16 - Ids.0 - SQL query : SELECT id as alfresco_id
2018/04/17 14:00:16 - Ids.0 - FROM dm_dim_alfresco
2018/04/17 14:00:16 - Ids.0 - WHERE is_active='Y'
2018/04/17 14:00:16 - Ids.0 - Signaling 'output done' to 1 output rowsets.
2018/04/17 14:00:16 - Ids.0 - Finished reading query, closing connection.
2018/04/17 14:00:16 - AAAR_DataMart - Connection to database closed!
2018/04/17 14:00:16 - Ids.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
2018/04/17 14:00:16 - Copy rows to result.0 - Signaling 'output done' to 0 output rowsets.
2018/04/17 14:00:16 - Copy rows to result.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2018/04/17 14:00:16 - Get all - Starting entry [Get all on a server]
2018/04/17 14:00:16 - Get all - exec(2, 0, Get all on a server.0)
2018/04/17 14:00:16 - Get all on a server - Starting job entry
2018/04/17 14:00:16 - Get all on a server - Loading job from repository by reference : [23]
2018/04/17 14:00:17 - Get all on a server - exec(3, 0, START.0)
2018/04/17 14:00:17 - START - Starting job entry
2018/04/17 14:00:17 - Get all on a server - Starting entry [get_audit]
2018/04/17 14:00:17 - Get all on a server - exec(4, 0, get_audit.0)
2018/04/17 14:00:17 - get_audit - Starting job entry
2018/04/17 14:00:17 - get_audit - Value to evaluate is true
2018/04/17 14:00:17 - get_audit - Comparing incoming value [true] with value [true]...
2018/04/17 14:00:17 - Get all on a server - Starting entry [Get audit]
2018/04/17 14:00:17 - Get all on a server - exec(5, 0, Get audit.0)
2018/04/17 14:00:17 - Get audit - Starting job entry
2018/04/17 14:00:17 - Get audit - Loading job from repository by reference : [2]
2018/04/17 14:00:17 - Get audit - exec(6, 0, START.0)
2018/04/17 14:00:17 - START - Starting job entry
2018/04/17 14:00:17 - Get audit - Starting entry [Init audit staging]
2018/04/17 14:00:17 - Get audit - exec(7, 0, Init audit staging.0)
2018/04/17 14:00:17 - Init audit staging - Starting job entry
2018/04/17 14:00:17 - Init audit staging - Loading job from repository by reference : [10]
2018/04/17 14:00:17 - Init audit staging - exec(8, 0, START.0)
2018/04/17 14:00:17 - START - Starting job entry
2018/04/17 14:00:17 - Init audit staging - Starting entry [Get alfresco connection params]
2018/04/17 14:00:17 - Init audit staging - exec(9, 0, Get alfresco connection params.0)
2018/04/17 14:00:17 - Get alfresco connection params - Starting job entry
2018/04/17 14:00:17 - Get alfresco connection params - Opening a transformation by reference with id=13
2018/04/17 14:00:18 - Get alfresco connection params - Starting transformation...(file=null, name=Get alfresco connection params, repinfo=null)
2018/04/17 14:00:18 - Get alfresco connection params - Transformation is pre-loaded.
2018/04/17 14:00:18 - Get alfresco connection params - nr of steps to run : 2 , nr of hops : 1
2018/04/17 14:00:18 - Get alfresco connection params - Dispatching started for transformation [Get alfresco connection params]
2018/04/17 14:00:18 - Get alfresco connection params - Nr of arguments detected:0
2018/04/17 14:00:18 - Get alfresco connection params - This is not a replay transformation
2018/04/17 14:00:18 - Get alfresco connection params - I found 2 different steps to launch.
2018/04/17 14:00:18 - Get alfresco connection params - Allocating rowsets...
2018/04/17 14:00:18 - Get alfresco connection params - Allocating rowsets for step 0 --> Alfresco params
2018/04/17 14:00:18 - Get alfresco connection params - prevcopies = 1, nextcopies=1
2018/04/17 14:00:18 - Get alfresco connection params - Transformation allocated new rowset [Alfresco params.0 - Set Variables.0]
2018/04/17 14:00:18 - Get alfresco connection params - Allocated 1 rowsets for step 0 --> Alfresco params
2018/04/17 14:00:18 - Get alfresco connection params - Allocating rowsets for step 1 --> Set Variables
2018/04/17 14:00:18 - Get alfresco connection params - Allocated 1 rowsets for step 1 --> Set Variables
2018/04/17 14:00:18 - Get alfresco connection params - Allocating Steps & StepData...
2018/04/17 14:00:18 - Get alfresco connection params - Transformation is about to allocate step [Alfresco params] of type [TableInput]
2018/04/17 14:00:18 - Get alfresco connection params - Step has nrcopies=1
2018/04/17 14:00:18 - Alfresco params.0 - distribution activated
2018/04/17 14:00:18 - Alfresco params.0 - Starting allocation of buffers & new threads...
2018/04/17 14:00:18 - Alfresco params.0 - Step info: nrinput=0 nroutput=1
2018/04/17 14:00:18 - Alfresco params.0 - output rel. is 1:1
2018/04/17 14:00:18 - Alfresco params.0 - Found output rowset [Alfresco params.0 - Set Variables.0]
2018/04/17 14:00:18 - Alfresco params.0 - Finished dispatching
2018/04/17 14:00:18 - Get alfresco connection params - Transformation has allocated a new step: [Alfresco params].0
2018/04/17 14:00:18 - Get alfresco connection params - Transformation is about to allocate step [Set Variables] of type [SetVariable]
2018/04/17 14:00:18 - Get alfresco connection params - Step has nrcopies=1
2018/04/17 14:00:18 - Set Variables.0 - distribution activated
2018/04/17 14:00:18 - Set Variables.0 - Starting allocation of buffers & new threads...
2018/04/17 14:00:18 - Set Variables.0 - Step info: nrinput=1 nroutput=0
2018/04/17 14:00:18 - Set Variables.0 - Got previous step from [Set Variables] #0 --> Alfresco params
2018/04/17 14:00:18 - Set Variables.0 - input rel is 1:1
2018/04/17 14:00:18 - Set Variables.0 - Found input rowset [Alfresco params.0 - Set Variables.0]
2018/04/17 14:00:18 - Set Variables.0 - Finished dispatching
2018/04/17 14:00:18 - Get alfresco connection params - Transformation has allocated a new step: [Set Variables].0
2018/04/17 14:00:18 - Get alfresco connection params - This transformation can be replayed with replay date: 2018/04/17 14:00:18
2018/04/17 14:00:18 - Get alfresco connection params - Initialising 2 steps...
2018/04/17 14:00:18 - Set Variables.0 - Released server socket on port 0
2018/04/17 14:00:18 - Alfresco params.0 - Released server socket on port 0
2018/04/17 14:00:18 - AAAR_DataMart - New database connection defined
2018/04/17 14:00:18 - Alfresco params.0 - Connected to database...
2018/04/17 14:00:18 - Get alfresco connection params - Step [Alfresco params.0] initialized flawlessly.
2018/04/17 14:00:18 - Get alfresco connection params - Step [Set Variables.0] initialized flawlessly.
2018/04/17 14:00:18 - Get alfresco connection params - Transformation has allocated 2 threads and 1 rowsets.
2018/04/17 14:00:18 - Alfresco params.0 - Starting to run...
2018/04/17 14:00:18 - Set Variables.0 - Starting to run...
2018/04/17 14:00:18 - Alfresco params.0 - SQL query : select
2018/04/17 14:00:18 - Alfresco params.0 - id as alfresco_id,
2018/04/17 14:00:18 - Alfresco params.0 - login as alfresco_login,
2018/04/17 14:00:18 - Alfresco params.0 - password as alfresco_password,
2018/04/17 14:00:18 - Alfresco params.0 - url as alfresco_url,
2018/04/17 14:00:18 - Alfresco params.0 - concat(url,url_audit_suffix,'&fromId=',( select count(*) from dm_fact_actions),'&limit=',audit_limit) as alfresco_audit_url,
2018/04/17 14:00:18 - Alfresco params.0 - concat(url,url_nodes_modified_after_suffix) as nodes_modified_after_url,
2018/04/17 14:00:18 - Alfresco params.0 - concat(url,url_nodes_modified_before_suffix) as nodes_modified_before_url,
2018/04/17 14:00:18 - Alfresco params.0 - node_limit as nodes_limit,
2018/04/17 14:00:18 - Alfresco params.0 - concat(url,url_cmis_suffix) as alfresco_cmis_url,
2018/04/17 14:00:18 - Alfresco params.0 - concat(url,url_workflow_definitions_suffix) as workflow_definitions_url,
2018/04/17 14:00:18 - Alfresco params.0 - concat(url,url_workflow_instances_suffix,'?state=completed&completedAfter=',(select coalesce(max(end_date_id),str_to_date('2000-01-01','%Y-%m-%d')) from dm_dim_workflow_instances where alfresco_id = dm_dim_alfresco.id),'T00:00:00.000+00:00') as workflow_instances_completed_url,
2018/04/17 14:00:18 - Alfresco params.0 - concat(url,url_workflow_instances_suffix,'?state=active') as workflow_instances_active_url
2018/04/17 14:00:18 - Alfresco params.0 - from dm_dim_alfresco
2018/04/17 14:00:18 - Alfresco params.0 - where id = 1
2018/04/17 14:00:19 - Alfresco params.0 - Signaling 'output done' to 1 output rowsets.
2018/04/17 14:00:19 - Alfresco params.0 - Finished reading query, closing connection.
2018/04/17 14:00:19 - AAAR_DataMart - Connection to database closed!
2018/04/17 14:00:19 - Alfresco params.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
2018/04/17 14:00:19 - Set Variables.0 - Setting environment variables...
2018/04/17 14:00:19 - Set Variables.0 - Set variable alfresco_login to value [admin]
2018/04/17 14:00:19 - Set Variables.0 - Set variable alfresco_password to value [admin123]
2018/04/17 14:00:19 - Set Variables.0 - Set variable alfresco_url to value [http://IP Address:8080]
2018/04/17 14:00:19 - Set Variables.0 - Set variable alfresco_audit_url to value [http://IP Adress:8080/alfresco/service/api/audit/query/alfresco-access?verbose=true&fromId=1201553&limit=100000]
2018/04/17 14:00:19 - Set Variables.0 - Set variable nodes_limit to value [60000]
2018/04/17 14:00:19 - Set Variables.0 - Set variable nodes_modified_before_url to value [http//IP address:8080/alfresco/service/AAAR/getNodeIdsModifiedBefore]
2018/04/17 14:00:19 - Set Variables.0 - Set variable nodes_modified_after_url to value [http://IP Adress:8080/alfresco/service/AAAR/getNodesModifiedAfter]
2018/04/17 14:00:19 - Set Variables.0 - Set variable alfresco_cmis_url to value [http://IP Address:8080/alfresco/api/-default-/cmis/versions/1.1/atom]
2018/04/17 14:00:19 - Set Variables.0 - Set variable workflow_definitions_url to value [http://IP Address:8080/alfresco/service/api/workflow-definitions]
2018/04/17 14:00:19 - Set Variables.0 - Set variable workflow_instances_active_url to value [http://IP Address:8080/alfresco/service/api/workflow-instances?state=active]
2018/04/17 14:00:19 - Set Variables.0 - Set variable workflow_instances_completed_url to value [http://IP Address:8080/alfresco/service/api/workflow-instances?state=completed&completedAfter=2000-01-01T00:00:00.000+00:00]
2018/04/17 14:00:19 - Set Variables.0 - Finished after 1 rows.
2018/04/17 14:00:19 - Set Variables.0 - Signaling 'output done' to 0 output rowsets.
2018/04/17 14:00:19 - Set Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2018/04/17 14:00:19 - Init audit staging - Starting entry [Clean audit staging]
2018/04/17 14:00:19 - Init audit staging - exec(10, 0, Clean audit staging.0)
2018/04/17 14:00:19 - Clean audit staging - Starting job entry
2018/04/17 14:00:19 - AAAR_DataMart - New database connection defined
2018/04/17 14:00:19 - Clean audit staging - Running SQL :delete from stg_audits where alfresco_id = 1;
2018/04/17 14:00:19 - AAAR_DataMart - launch DDL statement:
2018/04/17 14:00:19 - AAAR_DataMart - delete from stg_audits where alfresco_id = 1
2018/04/17 14:00:19 - AAAR_DataMart - 1 statement executed
2018/04/17 14:00:19 - AAAR_DataMart - Connection to database closed!
2018/04/17 14:00:19 - Init audit staging - Starting entry [jsonPath]
2018/04/17 14:00:19 - Init audit staging - exec(11, 0, jsonPath.0)
2018/04/17 14:00:19 - jsonPath - Starting job entry
2018/04/17 14:00:19 - jsonPath - Set variable json_path to value [/tmp/AlfrescoAudit_1.json]
2018/04/17 14:00:19 - Init audit staging - Starting entry [HTTP]
2018/04/17 14:00:19 - Init audit staging - exec(12, 0, HTTP.0)
2018/04/17 14:00:19 - HTTP - Starting job entry
2018/04/17 14:00:19 - HTTP - Start of HTTP job entry.
2018/04/17 14:00:19 - HTTP - Connecting to URL: http://IP Address:8080/alfresco/service/api/audit/query/alfresco-access?verbose=true&fromId=1201553&limit=100000
2018/04/17 14:00:19 - HTTP - Start reading reply from webserver.
2018/04/17 14:02:28 - HTTP - Resource type: application/json;charset=UTF-8, last modified on: Thu Jan 01 05:30:00 IST 1970.
2018/04/17 14:02:32 - HTTP - Finished writing 86578256 bytes to result file [/tmp/AlfrescoAudit_1.json]
2018/04/17 14:02:32 - Init audit staging - Starting entry [Get Alfresco audit]
2018/04/17 14:02:32 - Init audit staging - exec(13, 0, Get Alfresco audit.0)
2018/04/17 14:02:32 - Get Alfresco audit - Starting job entry
2018/04/17 14:02:32 - Get Alfresco audit - Opening a transformation by reference with id=10
2018/04/17 14:02:32 - Get Alfresco audit - Starting transformation...(file=null, name=Get Alfresco audit, repinfo=null)
2018/04/17 14:02:32 - Get Alfresco audit - Transformation is pre-loaded.
2018/04/17 14:02:32 - Get Alfresco audit - nr of steps to run : 3 , nr of hops : 2
2018/04/17 14:02:32 - Get Alfresco audit - Dispatching started for transformation [Get Alfresco audit]
2018/04/17 14:02:32 - Get Alfresco audit - Nr of arguments detected:0
2018/04/17 14:02:32 - Get Alfresco audit - This is not a replay transformation
2018/04/17 14:02:32 - Get Alfresco audit - I found 3 different steps to launch.
2018/04/17 14:02:32 - Get Alfresco audit - Allocating rowsets...
2018/04/17 14:02:32 - Get Alfresco audit - Allocating rowsets for step 0 --> Load file content in memory
2018/04/17 14:02:32 - Get Alfresco audit - prevcopies = 1, nextcopies=1
2018/04/17 14:02:32 - Get Alfresco audit - Transformation allocated new rowset [Load file content in memory.0 - Modified Java Script Value.0]
2018/04/17 14:02:32 - Get Alfresco audit - Allocated 1 rowsets for step 0 --> Load file content in memory
2018/04/17 14:02:32 - Get Alfresco audit - Allocating rowsets for step 1 --> Modified Java Script Value
2018/04/17 14:02:32 - Get Alfresco audit - prevcopies = 1, nextcopies=1
2018/04/17 14:02:32 - Get Alfresco audit - Transformation allocated new rowset [Modified Java Script Value.0 - stg_audits.0]
2018/04/17 14:02:32 - Get Alfresco audit - Allocated 2 rowsets for step 1 --> Modified Java Script Value
2018/04/17 14:02:32 - Get Alfresco audit - Allocating rowsets for step 2 --> stg_audits
2018/04/17 14:02:32 - Get Alfresco audit - Allocated 2 rowsets for step 2 --> stg_audits
2018/04/17 14:02:32 - Get Alfresco audit - Allocating Steps & StepData...
2018/04/17 14:02:32 - Get Alfresco audit - Transformation is about to allocate step [Load file content in memory] of type [LoadFileInput]
2018/04/17 14:02:32 - Get Alfresco audit - Step has nrcopies=1
2018/04/17 14:02:32 - Load file content in memory.0 - distribution activated
2018/04/17 14:02:32 - Load file content in memory.0 - Starting allocation of buffers & new threads...
2018/04/17 14:02:32 - Load file content in memory.0 - Step info: nrinput=0 nroutput=1
2018/04/17 14:02:32 - Load file content in memory.0 - output rel. is 1:1
2018/04/17 14:02:32 - Load file content in memory.0 - Found output rowset [Load file content in memory.0 - Modified Java Script Value.0]
2018/04/17 14:02:32 - Load file content in memory.0 - Finished dispatching
2018/04/17 14:02:32 - Get Alfresco audit - Transformation has allocated a new step: [Load file content in memory].0
2018/04/17 14:02:32 - Get Alfresco audit - Transformation is about to allocate step [Modified Java Script Value] of type [ScriptValueMod]
2018/04/17 14:02:32 - Get Alfresco audit - Step has nrcopies=1
2018/04/17 14:02:32 - Modified Java Script Value.0 - distribution activated
2018/04/17 14:02:32 - Modified Java Script Value.0 - Starting allocation of buffers & new threads...
2018/04/17 14:02:32 - Modified Java Script Value.0 - Step info: nrinput=1 nroutput=1
2018/04/17 14:02:32 - Modified Java Script Value.0 - Got previous step from [Modified Java Script Value] #0 --> Load file content in memory
2018/04/17 14:02:32 - Modified Java Script Value.0 - input rel is 1:1
2018/04/17 14:02:32 - Modified Java Script Value.0 - Found input rowset [Load file content in memory.0 - Modified Java Script Value.0]
2018/04/17 14:02:32 - Modified Java Script Value.0 - output rel. is 1:1
2018/04/17 14:02:32 - Modified Java Script Value.0 - Found output rowset [Modified Java Script Value.0 - stg_audits.0]
2018/04/17 14:02:32 - Modified Java Script Value.0 - Finished dispatching
2018/04/17 14:02:32 - Get Alfresco audit - Transformation has allocated a new step: [Modified Java Script Value].0
2018/04/17 14:02:32 - Get Alfresco audit - Transformation is about to allocate step [stg_audits] of type [TableOutput]
2018/04/17 14:02:32 - Get Alfresco audit - Step has nrcopies=1
2018/04/17 14:02:32 - stg_audits.0 - distribution activated
2018/04/17 14:02:32 - stg_audits.0 - Starting allocation of buffers & new threads...
2018/04/17 14:02:32 - stg_audits.0 - Step info: nrinput=1 nroutput=0
2018/04/17 14:02:32 - stg_audits.0 - Got previous step from [stg_audits] #0 --> Modified Java Script Value
2018/04/17 14:02:32 - stg_audits.0 - input rel is 1:1
2018/04/17 14:02:32 - stg_audits.0 - Found input rowset [Modified Java Script Value.0 - stg_audits.0]
2018/04/17 14:02:32 - stg_audits.0 - Finished dispatching
2018/04/17 14:02:32 - Get Alfresco audit - Transformation has allocated a new step: [stg_audits].0
2018/04/17 14:02:32 - Get Alfresco audit - This transformation can be replayed with replay date: 2018/04/17 14:02:32
2018/04/17 14:02:32 - Get Alfresco audit - Initialising 3 steps...
2018/04/17 14:02:32 - stg_audits.0 - Released server socket on port 0
2018/04/17 14:02:32 - Load file content in memory.0 - Released server socket on port 0
2018/04/17 14:02:32 - AAAR_DataMart - New database connection defined
2018/04/17 14:02:32 - Modified Java Script Value.0 - Released server socket on port 0
2018/04/17 14:02:32 - stg_audits.0 - Connected to database [AAAR_DataMart] (commit=1000)
2018/04/17 14:02:32 - AAAR_DataMart - Auto commit off
2018/04/17 14:02:32 - Get Alfresco audit - Step [Load file content in memory.0] initialized flawlessly.
2018/04/17 14:02:32 - Get Alfresco audit - Step [Modified Java Script Value.0] initialized flawlessly.
2018/04/17 14:02:32 - Get Alfresco audit - Step [stg_audits.0] initialized flawlessly.
2018/04/17 14:02:32 - Load file content in memory.0 - Starting to run...
2018/04/17 14:02:32 - stg_audits.0 - Starting to run...
2018/04/17 14:02:32 - Load file content in memory.0 - Opening file: file:///tmp/AlfrescoAudit_1.json
2018/04/17 14:02:32 - Modified Java Script Value.0 - Starting to run...
2018/04/17 14:02:32 - Get Alfresco audit - Transformation has allocated 3 threads and 2 rowsets.
2018/04/17 14:02:34 - Load file content in memory.0 - Finished opening file file:///tmp/AlfrescoAudit_1.json
child index = 0, logging object : org.pentaho.di.core.logging.LoggingObject@4ca7942f parent=a6e32f8d-6cb6-4c44-8b74-a3987aef95f3
2018/04/17 14:02:37 - Load file content in memory.0 - Finished processing (I=1, O=0, R=0, W=0, U=0, E=1)
2018/04/17 14:02:37 - Get Alfresco audit - Transformation detected one or more steps with errors.
2018/04/17 14:02:37 - Get Alfresco audit - Transformation is killing the other steps!
2018/04/17 14:02:37 - Get Alfresco audit - Looking at step: Load file content in memory
2018/04/17 14:02:37 - stg_audits.0 - Signaling 'output done' to 0 output rowsets.
2018/04/17 14:02:37 - Modified Java Script Value.0 - Signaling 'output done' to 1 output rowsets.
2018/04/17 14:02:37 - AAAR_DataMart - Commit on database connection [AAAR_DataMart]
2018/04/17 14:02:37 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
2018/04/17 14:02:37 - AAAR_DataMart - Connection to database closed!
2018/04/17 14:02:37 - stg_audits.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
2018/04/17 14:02:37 - Get Alfresco audit - Looking at step: Modified Java Script Value
2018/04/17 14:02:37 - Get Alfresco audit - Looking at step: stg_audits
2018/04/17 14:02:44 - Kitchen - Finished!
2018/04/17 14:02:44 - Kitchen - Start=2018/04/17 14:00:04.444, Stop=2018/04/17 14:02:44.300
2018/04/17 14:02:44 - Kitchen - Processing ended after 2 minutes and 39 seconds (159 seconds total).

Extraction complete!

Log file has been generated in the file below:
/root/.kettle/extract/AAAR.log

Check for ERRORS in the log.
If you can't find them, everything has gone alright.
Please, remember to schedule the command periodically or to execute it manually.

Tip:
You can schedule this command using the silent execution with the command below:
/root//data/biserver-ce_new/pentaho-solutions/system/AAAR/endpoints/kettle/script/AAAR_Extract.sh silent

Want to save the reports in pdf format directly in Alfresco?
It's time to execute the 'AAAR_Publish' script.

Enjoy A.A.A.R.!

Could you please help me on this , Is there any limit of data to push to Reporting tool , because , up to 2017-12-04 it was showing data , and after that it was not showing and also not pulling data from Alfresco production server.

Re: AAAR Report Extraction and Publish error

fcorti — Tue, 17 Apr 2018 16:22:44 GMT

The error is there:

2018/04/17 14:02:34 - Load file content in memory.0 - Finished opening file file:///tmp/AlfrescoAudit_1.json
child index = 0, logging object : org.pentaho.di.core.logging.LoggingObject@4ca7942f parent=a6e32f8d-6cb6-4c44-8b74-a3987aef95f3

Probably some data are causing the issue.

My suggestion is to reduce the audit_limit to 10000 and launch the import again.

In this way you will be able to import some data and come closed to the "dirty data".
The reduce the audit_limit again until you find the data causing the issue.

Then you can solve the issue directly in the ETL or updating the database or removing the data from the audit trail or... I don't know, there are several solutions to this.

I hope this helps.

Re: AAAR Report Extraction and Publish error

fcorti — Tue, 17 Apr 2018 16:23:27 GMT

I think the thread is solved.
If you have other issues, please open a new thread.
Thanks.