nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Publish error on Select SQL

jsimonet

Hello All,

I am having issues with the AAAR_Publish. It is apparently unable to launch the select statement which I could launch using an Pgadmin with same login.

It seems that the login being used by the AAAR_Publish is not correct...for the extract I had to open the transformations using spoon and then set the connection credentials correctly.

For this one I tried to open the transformation but don't knwo where to check. Can someone help me? Is it a connection issue or something else...

Many thanks

AAAR_Publish log.txt.zip

Find more posts tagged with

Alfresco Content Services

Accepted answers

jsimonet

This is not the correct way to do it you have to set the connections as follows in "[Your pdi installation]\data-integration\simple-jndi\jdbc.properties". Change user name, password, ports,dns and ip accordingly

SampleData/type=javax.sql.DataSource
SampleData/driver=org.h2.Driver
SampleData/url=jdbc:h2:file:samples/db/sampledb;IFEXISTS=TRUE
SampleData/user=PENTAHO_USER
SampleData/password=PASSWORD
Quartz/type=javax.sql.DataSource
Quartz/driver=org.hsqldb.jdbcDriver
Quartz/url=jdbc:hsqldb:hsql://localhost/quartz
Quartz/user=pentaho_user
Quartz/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.hsqldb.jdbcDriver
Hibernate/url=jdbc:hsqldb:hsql://localhost/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Shark/type=javax.sql.DataSource
Shark/driver=org.hsqldb.jdbcDriver
Shark/url=jdbc:hsqldb:hsql://localhost/shark
Shark/user=sa
Shark/password=

# A.A.A.R. - Alfresco Audit Analysis and Reporting - AAAR_DataMart.
AAAR_DataMart/type=javax.sql.DataSource
AAAR_DataMart/driver=org.postgresql.Driver
AAAR_DataMart/url=jdbcostgresql://[Your DNS name or IP]:5433/AAAR_DataMart
AAAR_DataMart/user=postgres
AAAR_DataMart/password=admin

# A.A.A.R. - Alfresco Audit Analysis and Reporting - AAAR_Kettle.
AAAR_Kettle/type=javax.sql.DataSource
AAAR_Kettle/driver=org.postgresql.Driver
AAAR_Kettle/url=jdbcostgresql://[Your DNS name or IP]/AAAR_Kettle
AAAR_Kettle/user=postgres
AAAR_Kettle/password=admin

All comments

fcorti

Hi Jeremie,

The publish task uses a different login/password and IP for Alfresco
This is because you could want to extract informations from one (or more) Alfresco instances and publish reports on different Alfresco instances (or one only).

Please check at the following link to understand where to setup the connection parameters to the target Alfresco instances.

http://fcorti.com/alfresco-audit-analysis-reporting/aaar-faq/#q3

jsimonet

Hello Francesco,

The connections to pentaho and alfresco are ok. However the connection to the Postgres sql database is not working correctly it seems:

11:18:56,507 ERROR [PdfExportTask] Failed
org.pentaho.di.core.exception.KettleException:
There was an unexpected error processing report 'C:\Users\jsimonet\AppData\Local\Temp\\audit_use.prpt' to produce file 'C:\Users\jsimonet\AppData\Local\Temp\\audit_use.pdf' with processor: PDF.
Failed at query: select
a.id as instance_id,
a.desc as instance_name
from
dm_dim_alfresco a
order by
a.desc asc

Is there a place where you specify the Database credentials. Because if I use PGadmin I can connect to database?

Or is it another issue?

fcorti

Mmm... this is caused from the report itself, not able to connect to the DB for some reasons.

You can solve editing the report using Pentaho Report Designer and check that the report is using the AAAR_DataMart connection with valid settings.

ATTENTION: The valid settings ARE NOT the Report Designer settings but the Pentaho Data Integration settings. Because you will use the report through the ETL, so in the PDI environment.

Report should use the JNDI connection but you can try replacing the "explicits" connection settings for your environment.

jsimonet

The report seems to be re-created with wrong credentials...no way to change it through report designer. But its confirmed, connection credentials are all wrong. Will try to re-check the connections which I really messed up as far as I can....

jsimonet

Hello Francesco,

I wrongly understood that I should modify the file generated in the Temp folder using Report Designer. But rather I had to modify the report from the repository itself and save it there using the RD. Its working ok now. Up to the point where it is FTPing the file.

2017/05/17 11:46:33 - Report - Starting entry [Carica file con FTP]
2017/05/17 11:46:33 - Carica file con FTP - Using PDIFTPClient override for exists() fix.
2017/05/17 11:46:35 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error putting to FTP : Connection refused: connect
2017/05/17 11:46:35 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : java.net.ConnectException: Connection refused: connect

I am investigating. Surely FTP is not set well...

If you have a clue where to change let me know. thanks.

Jeremie

fcorti

Yes, I was thinking we were not on the same page... but happy to hear you solved.

To change settings about FTP check into the dm_dim_reports table.

I'll be easy to understand how to setup the credentials.

You are close to the solution...

jsimonet

Yes its working now. Issue was I defined ftp port as 21 on top of my global alfresco file and port 1121 at the bottom of my alfresco file. First occurrence took precedence. Remove top entry restarted. Process is in progress...ftp OK. Yaye!

Waiting for the process to finish.

jsimonet

Hello,

I had to change each and every report to set the connection. I did something wrong during the installation surely. Also one should not forget to publish the Report once it has been modified. The connection to modify here is the Custom one which is set to Oracle and JNBI by default. I hope this can help other people having the same issue.

The A.A.A.R. Manual installation by Vicenzo Sorrentino also helped me a lot to debug connection issues.

The Publish process is still in progress. Will let you know when it succeeds...

fcorti

Good job!
And thank you for sharing the solution that worked for you.

jsimonet

One more thing to consider is that Symantec Endpoint Protection blocks communication. What I did is disable the software on both side and it worked. Not recommended solution but it worked. If someone knows how to configure it please share.

jsimonet

ganesh_batwal

Hello,

We are also getting the below mention errors while executing the " ./AAAR_Publish.sh" script. We have change the ftp port 21 in alfresco global level and during the AAAR configuration also.. But still not sure why we are getting the ftp connection errors, Kindly check and advice.

2017/09/18 16:50:36 - Pentaho Reporting for pdf.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/18 16:50:36 - Dummy (do nothing).0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/18 16:50:36 - Report - Starting entry [Delete prpt]
2017/09/18 16:50:36 - Delete prpt - File [/tmp/audit_use.prpt] deleted!
2017/09/18 16:50:36 - Report - Starting entry [Carica file con FTP]
2017/09/18 16:50:36 - Carica file con FTP - Using PDIFTPClient override for exists() fix.
2017/09/18 16:50:39 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error putting to FTP : Login incorrect.
2017/09/18 16:50:39 - Carica file con FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : com.enterprisedt.net.ftp.FTPException: 530 Login incorrect.
2017/09/18 16:50:39 - Carica file con FTP -    at com.enterprisedt.net.ftp.FTPControlSocket.validateReply(FTPControlSocket.java:1194)
2017/09/18 16:50:39 - Carica file con FTP -    at com.enterprisedt.net.ftp.FTPClient.password(FTPClient.java:1855)
2017/09/18 16:50:39 - Carica file con FTP -    at com.enterprisedt.net.ftp.FTPClient.login(FTPClient.java:1777)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.entries.ftpput.JobEntryFTPPUT.execute(JobEntryFTPPUT.java:598)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:724)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.Job.execute(Job.java:609)
2017/09/18 16:50:39 - Carica file con FTP -    at org.pentaho.di.job.entries.job.JobEntryJobRunner.run(JobEntryJobRunner.java:69)
2017/09/18 16:50:39 - Carica file con FTP -    at java.lang.Thread.run(Thread.java:748)
2017/09/18 16:50:39 - Report - Finished job entry [Carica file con FTP] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [Delete prpt] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [Create report] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [HTTP] (result=[false])
2017/09/18 16:50:39 - Report - Finished job entry [Create report info] (result=[false])
2017/09/18 16:50:39 - Report all - Finished job entry [Create and upload report] (result=[false])
2017/09/18 16:50:39 - Report all - Finished job entry [Get all id] (result=[false])
2017/09/18 16:50:39 - Report all - Job execution finished
2017/09/18 16:50:39 - Kitchen - Finished!
2017/09/18 16:50:39 - Kitchen - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Finished with errors
2017/09/18 16:50:39 - Kitchen - Start=2017/09/18 16:50:26.943, Stop=2017/09/18 16:50:39.171
2017/09/18 16:50:39 - Kitchen - Processing ended after 12 seconds.

Publication complete!

Log file has been generated in the file below:
/opt/pentaho-server/pentaho-solutions/system/AAAR/endpoints/kettle/script/../logs/AAAR.log