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

[jsimonet · Accepted solution]

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

[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

[jsimonet]

Hello Ganesh,

In your case it seems the login is incorrect...as per log file

First use the ftp link in the log file to try to download the file on the target machine. Try it also on the source machine.

source machine = client. target machine = server (Alfresco).

Usually on target machine it will ask for permission to access. It will be an internet explorer pop up. Give the permission. Same on source machine may be asked. Do it on both to make sure. Once you do this once it should work in script.

If ever the link works on target machine and not on source machine then you have something blocking it. For my case it was the Symantec endpoint security. Found out by checking the logs. Disabled it on source machine and it all worked out.

Try these out and let me know if it works.

Regards

Jeremie

[ganesh_batwal]

Thanks for an update..Now the ftp connectivity issue stand resolved after installation of "Alflytics", But now the error message are for FTP Access denied while publication.

2017/09/21 17:51:33 - Pentaho Reporting for pdf.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/21 17:51:33 - Dummy (do nothing).0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/09/21 17:51:33 - Put into Alfresco - Starting entry [Put a file with FTP]
2017/09/21 17:51:33 - Put a file with FTP - Using PDIFTPClient override for exists() fix.
2017/09/21 17:51:33 - Put a file with FTP - ERROR (version 7.0.0.0-25, build 1 from 2016-11-05 15.35.36 by buildguy) : Error putting to FTP : Access denied
2017/09/21 17:51:33 - Put a file with 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 Access denied
2017/09/21 17:51:33 - Put a file with FTP -     at com.enterprisedt.net.ftp.FTPControlSocket.validateReply(FTPControlSocket.java:1194)
2017/09/21 17:51:33 - Put a file with FTP -     at com.enterprisedt.net.ftp.FTPClient.password(FTPClient.java:1855)
2017/09/21 17:51:33 - Put a file with FTP -     at com.enterprisedt.net.ftp.FTPClient.login(FTPClient.java:1777)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.entries.ftpput.JobEntryFTPPUT.execute(JobEntryFTPPUT.java:598)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:724)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:865)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.Job.execute(Job.java:609)
2017/09/21 17:51:33 - Put a file with FTP -     at org.pentaho.di.job.entries.job.JobEntryJobRunner.run(JobEntryJobRunner.java:69)
2017/09/21 17:51:33 - Put a file with FTP -     at java.lang.Thread.run(Thread.java:748)
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Put a file with FTP] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Create target file from prpt] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [HTTP] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Set tmp files] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpPath from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpPassword from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpLogin from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpPort from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get ftpHost from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get outputType from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Get httpCall from query] (result=[false])
2017/09/21 17:51:33 - Put into Alfresco - Finished job entry [Set queryReportInfo] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Put into Alfresco] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Get rows from query] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Set queryReportIds] (result=[false])
2017/09/21 17:51:33 - Report all - Finished job entry [Check Db connections] (result=[false])
2017/09/21 17:51:33 - Report all - Job execution finished

[fcorti]

Did you setup the correct endpoint, username and password for the reports into the DWH_REPORTS table?
Please check the Alflytics/Alflytics-Manual.pdf at master · fcorti/Alflytics · GitHub at page 44.

[amar7295]

hello @ganesh_batwal 

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