AAAR Report Extraction and Publish error

[bagodu]

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 buffer
2018/04/04 15:33:01 - Modified Java Script Value.0 - Stopped while putting a row on the buffer
2018/04/04 15:33:01 - Modified Java Script Value.0 - tran_Status found. Checking transformation status while script execution.
2018/04/04 15:33:01 - Modified Java Script Value.0 - Finished processing (I=0, O=0, R=1, W=13001, U=0, E=0)
2018/04/04 15:33:01 - 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/04 15:33:01 - Init audit staging - Finished job entry [Get Alfresco audit] (result=[false])
2018/04/04 15:33:01 - Init audit staging - Finished job entry [HTTP] (result=[false])
2018/04/04 15:33:01 - Init audit staging - Finished job entry [jsonPath] (result=[false])
2018/04/04 15:33:01 - Init audit staging - Finished job entry [Clean audit staging] (result=[false])
2018/04/04 15:33:01 - Init audit staging - Finished job entry [Get alfresco connection params] (result=[false])
2018/04/04 15:33:01 - Get audit - Finished job entry [Init audit staging] (result=[false])
2018/04/04 15:33:01 - Get all on a server - Finished job entry [Get audit] (result=[false])
2018/04/04 15:33:01 - Get all on a server - Finished job entry [get_audit] (result=[false])
2018/04/04 15:33:01 - Get all - Finished job entry [Get all on a server] (result=[false])
2018/04/04 15:33:01 - Get all - Finished job entry [Get all source id] (result=[false])
2018/04/04 15:33:01 - Get all - Job execution finished
2018/04/04 15:33:01 - Kitchen - Finished!
2018/04/04 15:33:01 - Kitchen - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Finished with errors
2018/04/04 15:33:01 - Kitchen - Start=2018/04/04 15:29:27.642, Stop=2018/04/04 15:33:01.378
2018/04/04 15:33:01 - Kitchen - Processing ended after 3 minutes and 33 seconds (213 seconds total).

Extraction complete!

And also on ./AAAR_Publish.sh, it was showing below error message 

A.A.A.R. - Alfresco Audit Analysis and Reporting
Export procedure version 4.0.
Author: Francesco Corti (all rights reserved)
Date: 07 November 2015

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

Please, enter the <data-integration> path (es.:/opt/data-integration): /data/data-integration/

You are going to execute this command:
./kitchen.sh /rep:"AAAR_Kettle" /job:"Report all" /dir:/Alfresco /user:admin /pass:admin /log="/root/.kettle/logs/AAAR.log" /level:Rowlevel

Press <enter> to start import.

Picked up _JAVA_OPTIONS: -Xms1024m -Xmx4048m -Dhttp.proxyHost=IP ADDRESS -Dhttp.proxyPort=80 -Dhttp.nonProxyHosts="IP ADDRESS|localhost|127.0.0.1|10.*.*.*"
Exception in thread "main" java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)
Caused by: org.pentaho.di.core.exception.KettleException:
Unable to create a logging event listener to write to file '/root/.kettle/logs/AAAR.log'
Error creating output file! Parent directory [/root/.kettle/logs] does not exist.

at org.pentaho.di.core.logging.FileLoggingEventListener.<init>(FileLoggingEventListener.java:79)
at org.pentaho.di.core.logging.FileLoggingEventListener.<init>(FileLoggingEventListener.java:56)
at org.pentaho.di.kitchen.Kitchen.main(Kitchen.java:234)
... 5 more
Caused by: java.io.IOException: Error creating output file! Parent directory [/root/.kettle/logs] does not exist.
at org.pentaho.di.core.vfs.KettleVFS.getOutputStream(KettleVFS.java:268)
at org.pentaho.di.core.logging.FileLoggingEventListener.<init>(FileLoggingEventListener.java:77)
... 7 more

The below error , i have extracted from AAAR.log file ,

2017/12/04 21:00:05 - HTTP - Connecting to URL: http://IP Address:8020/pentaho/api/repo/files/ublic:AAAR:Reports
:audit_login.prpt
2017/12/04 21:00:05 - HTTP - Start reading reply from webserver.
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 t
o save the HTTP result to file because of a I/O error: Connection refused
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) : java.net.Conne
ctException: Connection refused
2017/12/04 21:00:05 - HTTP - at java.net.PlainSocketImpl.socketConnect(Native Method)
2017/12/04 21:00:05 - HTTP - at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
2017/12/04 21:00:05 - HTTP - at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
2017/12/04 21:00:05 - HTTP - at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
2017/12/04 21:00:05 - HTTP - at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
2017/12/04 21:00:05 - HTTP - at java.net.Socket.connect(Socket.java:589)
2017/12/04 21:00:05 - HTTP - at java.net.Socket.connect(Socket.java:538)
2017/12/04 21:00:05 - HTTP - at sun.net.NetworkClient.doConnect(NetworkClient.java:180)
2017/12/04 21:00:05 - HTTP - at sun.net.www.http.HttpClient.openServer(HttpClient.java:432)
2017/12/04 21:00:05 - HTTP - at sun.net.www.http.HttpClient.openServer(HttpClient.java:527)
2017/12/04 21:00:05 - HTTP - at sun.net.www.http.HttpClient.<init>(HttpClient.java:211)
2017/12/04 21:00:05 - HTTP - at sun.net.www.http.HttpClient.New(HttpClient.java:308)
2017/12/04 21:00:05 - HTTP - at sun.net.www.http.HttpClient.New(HttpClient.java:326)
2017/12/04 21:00:05 - HTTP - at sun.net.www.protocol.http.HttpURLConnection.getNewHttpClient(HttpURLConnection.java:
1169)
2017/12/04 21:00:05 - HTTP - at sun.net.www.protocol.http.HttpURLConnection.plainConnect0(HttpURLConnection.java:110
5)
2017/12/04 21:00:05 - HTTP - at sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:999)
2017/12/04 21:00:05 - HTTP - at sun.net.www.protocol.http.HttpURLConnection.connect(HttpURLConnection.java:933)
2017/12/04 21:00:05 - HTTP - at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1
513)
2017/12/04 21:00:05 - HTTP - at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:14
41)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.entries.http.JobEntryHTTP.execute(JobEntryHTTP.java:533)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.Job.execute(Job.java:716)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.Job.execute(Job.java:859)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.Job.execute(Job.java:859)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.entries.http.JobEntryHTTP.execute(JobEntryHTTP.java:533)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.Job.execute(Job.java:716)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.Job.execute(Job.java:859)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.Job.execute(Job.java:859)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.Job.execute(Job.java:598)
2017/12/04 21:00:05 - HTTP - at org.pentaho.di.job.entries.job.JobEntryJobRunner.run(JobEntryJobRunner.java:69)
2017/12/04 21:00:05 - HTTP - at java.lang.Thread.run(Thread.java:745)
2017/12/04 21:00:05 - Report - Finished job entry [HTTP] (result=[false])
2017/12/04 21:00:05 - Report - Finished job entry [Create report info] (result=[false])
2017/12/04 21:00:05 - Report all - Finished job entry [Create and upload report] (result=[false])
2017/12/04 21:00:05 - Report all - Finished job entry [Get all id] (result=[false])
2017/12/04 21:00:05 - Report all - Job execution finished
2017/12/04 21:00:05 - Kitchen - Finished!
2017/12/04 21:00:05 - Kitchen - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Finished with errors

Could you please help us on this.@@@@@#@

[fcorti]

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.

[bagodu]

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

[fcorti]

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.

[bagodu]

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

[fcorti · Accepted solution]

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"

[bagodu]

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.

[fcorti]

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.

[bagodu]

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.

[fcorti]

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