Basics of Audit Analysis and Reporting in Alfresco

In Alfresco, there is general requirement to have audit data and its report! Alfresco does provide audit feature we can enable it but there is not out-of-box support for audit report.

Here we will discuss on this audit and reporting addons - Alfresco Audit Analysis and Reporting (A.A.A.R.)

What is Alfresco Audit Analysis and Reporting (A.A.A.R.)

This solution provides very detailed report on Alfresco audit data. It fetches audit data from Alfresco stores it into Data Mart and creates the report in pdf, XLS etc… and uploads this report back to Alfresco.

This solution creates AAAR_datamart database to store Alfresco audit data.

Report in different format gets generated from Pentaho Report Designer from this data mart.

Pentaho Data Integration Or Kettle is ETL system which provides powerful capabilities to Extract, Transform and Load data. So here Pentaho Data Integration is used to extract data from Alfresco and load into Data mart and generate Reports and load them back to Alfresco.

How to install AAAR

We need

•  Alfresco 4.X
• MySql/PostgreSQL
• Pentaho Report Designer
•  Pentaho Data Integration

NOTICE: I have tested this with Alfresco 4.2.1EE and MySql DB in win7 

Install Alfresco 4.2.1EE 

Please refer this link to install Alfresco: http://docs.alfresco.com/4.2/concepts/master-ch-install.html

• Once Alfresco is installed enable auditing. Edit - alfresco-global.properties located at <AlfrescoInstallDir>/tomcat/shared/classes

                               ### Audit Configuration

                               audit.enabled=true

                               audit.alfresco-access.enabled=true

                                ### FTP Server Configuration ###

                                ftp.enabled=true

                                ftp.port=21

• Restart server and verify audit is enabled and working OK.
• Hit this webscript which will give you below JSON response - http://<alfresco_url>:<alfresco_port>/alfresco/service/api/audit/control

Create Data Mart :

• We need to create AAAR_DataMart 
• Run cmd
• Go to MySql bin folder using cd C:/Program Files (x86)/MySQL/MySQL Server 5.5/bin
• Execute this script AAAR_DataMart.sql. You can find this script either at biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\src\MySql OR <your AAR installed folder>\AAAR\endpoints\kettle\src\PostgreSql
• Execute this command mysql –u root –p<password> “<AAAR folder>\AAAR_DataMart.sql” OR you can execute this entire script from your MySql editor.
• Check aaar_datamart database has been created

Install Pentaho Data Integration/Kettle :

• Make sure you have java7 installed
• Download pdi-ce-5.0.1-stable.zip from the official website or the sourceforge  - http://sourceforge.net/projects/pentaho/files/Data%20Integration/5.0.1-stable/
• Unzip pdi-ce-5.0.1-stable.zip
• To run it with MySql add mysql driver jar file to data-integration/lib folder
• PDI is composed of Spoon, Kitchen and Pan
• Execute spoon.bat located at pdi-ce-5.1.0.0-752\data-integration to create all configuration folders and files
• Now we need to set PDI/Kettle repository
• As we executed script to create aaar_datamart same way we need to execute AAAR_Kettle_v5.sql to create aaar_kettle database

Set Pentaho Data Integration repository :

• Next step is to set PDI data repository to store ETL data.
• Go to pdi-ce-5.1.0.0-752\data-integration and run Spoon.bat
• Click on the green plus to add a new repository and define a new repository connection in the database.

• Add a new database connection to the repository
• Select “Kettle Database Repository”  which will allow you to select DB connection settings

• Select General
• Give Connection Name – AAAR_Kettle
• Select Connection Type – MySql
• Select Access – Native(JDBC)
• In Settings, provide
• Host Name: localhost
• Database Name: AAAR_Kettle
• Port Number: 3306
• User Name: root
• Password: root
• Once done Test connection and make sure you are able to connect AAAR_Kettle DB
• Click OK

• In Repository Connection you will find AAAR_KETTLE
• User Name: admin and Password: admin
• Click OK
• We are done with AAAR_Kettle DB setup
• Now lets configure AAAR_Datamart
• From the Pentaho Data Integration panel, click on Tool -> Repository -> explore

• Click on the 'Connections' tab and edit the AAAR_DataMart connection.

• Edit this DB connection. Provide all details same as  AAAR_kettle DB setup

• We are done with PDI repository setup

Install Pentaho Business Analytics platform 5 [Pentaho BI-Server 5] :

• Make sure you have java7 installed
• Make sure you have java7 installed
• Download the Pentaho BI-Server 5 package from the official website or the sourceforge  - http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/5.0.1-stable/biserver-ce-5.0.1-stable.zip/download
• Unzip biserver-ce-5.0.1-stable.zip
• This is web application which is deployed in tomcat. 
• To run this application with MySql please refer - http://anonymousbi.wordpress.com/2013/12/15/pentaho-bi-server-5-0-1ce-mysql-installation-guide/
• Start this application by running  - start-pentaho.bat located at biserver-ce-5.0.1-stable\biserver-ce OR from startup.bat of tomcat located at - biserver-ce-5.0.1-stable\biserver-ce\tomcat\bin
• Once its started then go to - http://<server>:8080/pentaho and login with admin user. UID: admin PWD: password

Install A.A.A.R from Pentaho marketplace

• Login as admin user to - http://<server>:8080/pentaho
• Go to Home-> Market Place
• Install:
• Community Data Access
• Community Dashboard Editor
• Alfresco Audit Analysis and Reporting

• Once its installed, Restart your BIServer
• Again login as admin
• Go to Tools -> AAAR
• Click on Configuration
• Provide details for Alfresco, Data Mart and PDI/Kettle
• Alfresco details:
• Protocol : http
• Host: localhost
• Port: 9090
• Login: admin
• Password: admin
• FTP Path : alfresco
• FTP Port : 2121
• Max audit: 50000
• Data Mart details:
• Type: MySql
• Host: localhost
• Port: 3306
• Login: root
• Password: root
• Bin Path [MySql bin dir’s path]:  C:/Program Files (x86)/MySQL/MySQL Server 5.5/bin
• PDI/Kettle details
• Path[Where you have installed your PDI]: D:/Pentaho/pdi-ce-5.1.0.0-752/data-integration

• Save your data
• Click Install tab then click on Install
• Check your logs
• After successful installation, Go to Tools -> Refresh -> CDA cache
• Click Use

Use A.A.A.R

• Login as admin or other user to - http://<server>:8080/pentaho
• Go to Tools -> AAR
• Click on Use
• Here you will find 
• Extract: Get audit data from alfresco to Data mart
• Publish: Upload report to alfresco
• Analyse: Analyze data from dashboard
• Extract data:
• You can schedule this script to run as per your requirement or for testing you can also run it manually
• To run it manually go to - biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\script and execute AAAR_Extract.bat script
• Execute this script OR 
• Go to cmd and change your working directory to data-integration
• Execute this command: kitchen.bat /rep:"AAAR_Kettle" /job:"Get all" /dir:/Alfresco /user:admin /pass:admin /level:Basic

• Publish to Alfresco:
• We can publish extracted report from PDI repository to Alfresco. This will be static report.
• You can schedule this script to run or you can run it manually
• To run it manually go to - biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\script and execute AAAR_Publish.bat script
• Execute this script OR 
• Go to cmd and change your working directory to data-integration
• Execute this command:kitchen.bat /rep:"AAAR_Kettle" /job:"Report all" /dir:/Alfresco /user:admin /pass:admin /level:Basic
• Once reports are published you can go to Alfresco and check reports are generated under Company Home

• Analyze data:
• Go to Analyze tab and click on Analytics and you can analyze real time data from here
• OR you can access through http://<server>:8080/pentaho/api/repos/:public:AAAR:main.wcdf/generatedContent

Reference: https://addons.alfresco.com/addons/alfresco-audit-analysis-and-reporting-aaar