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

[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.