Alfresco Guide: ALFRESCO AUDIT ANALYSIS AND REPORTING (AAAR)

ALFRESCO AUDIT ANALYSIS AND REPORTING (AAAR)

By:

Sandeep Reddy,

Contents:

1. Prerequisites

2. Step by Step Process of AAAR Reports

2.1 Install Alfresco Enterprise 5.0

2.2 Create Data Mart

2.3 Install Pentaho Data Integration

2.4 Set Pentaho Data Integration Repository

2.5 Install the Pentaho Business analytics platform

2.6 Install AAAR from Pentaho Marketplace

2.7 Extract Data

2.8 Publish To Alfresco

2.9 Analyze data

3. Errors and Resolutions

4. Followed Links

What is AAAR Reports?

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.

But in this We are using Pentaho Bi server for Extract, publish and analytics.

Pre-Requisite:

Ø Alfresco Enterprise 5.0

Ø MySQL / PostgreSQL

Ø Pentaho Bi server (biserver-ce-5.3.0.0-213)

Ø Pentaho Data Integration (pdi-ce-5.3.0.0-213)

Ø Pentaho Report Designer (prd-ce-5.3.0.0-213)

Ø AAAR v1.3 on MySQL

NOTE: I Have Tested with Alfresco Enterprise 5.0 and MYSQL DB in Windows 8

Step by step process:

Step-1:

Install Alfresco Enterprise 5.0

· 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

Example:

Type this link on browser

http://localhost:9090/alfresco/service/api/audit/control

After this following code be shown

The result should be a Json similar to the one described below.

{

"enabled" : true,

"applications":

[

{

"name": "Alfresco Tagging Service",

"path" : "/tagging",

"enabled" : true

}

{

"name": "alfresco-access",

"path" : "/alfresco-access",

"enabled" : true

}

]

}

Step-2:

Create Data Mart

· We need to create AAAR_DataMart

· Run cmd as administrator.

· 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

Example:

è C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin

è For MySQL : C:\Users\sandeep\Desktop\AAAR\biserver-ce-5.3.0.0-213\biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\src\Alfresco_5_0\MySql\AAAR_DataMart.sql

è For PostgreSQL: C:\Users\sandeep\Desktop\AAAR\biserver-ce-5.3.0.0-213\biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\src\Alfresco_5_0\PostgreSql\AAAR_DataMart.sql

· Execute this command mysql –u root –p<password> “<AAAR folder>\AAAR_DataMart.sql” OR you can execute this entire script from your MySql editor.

i.e

C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin> mysql.exe –u root –p < “C:\Users\sandeep\Desktop\AAAR\biserver-ce-5.3.0.0-213\biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\src\Alfresco_5_0\MySql\AAAR_DataMart.sql “

Download the AAAR Reports Zip file and Extract it

Run cmd

C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin> mysql.exe –u root –p < “C:\users\sandeep\Desktop\AAAR Reports\AAAR_DataMart.sql

· Check aaar_datamart database has been created.

· Extract ‘reports.zip’ in the ‘data-integration’ folder in kettle. ‘report.zip’ contains 11 files with ‘prpt’ extension, each one containing one Pentaho Reporting Designer report.

i.e C:\Users\sandeep\Desktop\AAAR\pdi-ce-5.3.0.0-213\data-integration\reports

· Update ‘dm_dim_alfresco’ table with the proper environment settings. Each row of the table represent one Alfresco installation and for that reason the table is defined with a unique row by default, as described below.

desc with value ‘Alfresco’.

password with value ‘admin’.

url with value ‘http://localhost:9090’.

url_audit_suffix with value ‘/alfresco/service/api/audit/query/alfresco- access?verbose=true&limit=100000’.

url_cmis_suffix with value ‘/alfresco/service/api/cmis’.

is_active with value ‘Y’.

SQL query:

UPDATE dm_dim_alfresco SET url=’http://localhost:9090’ WHERE id=1;

· Update ‘dm_reports’ table with our reports entries.

Step-3:

Install Pentaho Data Integration/Kettle :

· Make sure you have java7 installed

· And set java path for Pentaho in system environment variables

i.e name: PENTAHO_JAVA

Variable: C:\Program Files\Java\jre7

Otherwise Pentaho not started properly.

· To run it with MySql add MySql driver jar file to data-integration/lib folder

· Unzip the CMIS_Input_plugin.zip in the <data-integration> folder to install the plugin.

· PDI is composed of Spoon, Kitchen and Pan

· Execute spoon.bat located at pdi-ce-5.3.0.0-213\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

· Go to MySql bin folder using cd C:/Program Files (x86)/MySQL/MySQL Server 5.5/bin

Example:

è C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin

è For MySQL : C:\Users\sandeep\Desktop\AAAR\biserver-ce-5.3.0.0-213\biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\src\Alfresco_5_0\MySql\AAAR_Kettle.sql

è For PostgreSQL: C:\Users\sandeep\Desktop\AAAR\biserver-ce-5.3.0.0-213\biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\src\Alfresco_5_0\PostgreSql\AAAR_DataMart.sql

· Execute this command mysql –u root –p<password> “<AAAR folder>\ AAAR_Kettle.sql” OR you can execute this entire script from your MySql editor.

i.e

Download the AAAR Reports Zip file and Extract it

Run cmd

C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin> mysql.exe –u root –p < “C:\users\sandeep\Desktop\AAAR Reports\ AAAR_Kettle.sql”

· Check AAAR_Kettle database has been created

Step-3:

Set Pentaho Data Integration repository :

· Next step is to set PDI data repository to store ETL data.

· Go to pdi-ce-5.3.0.0-213\data-integration and run Spoon.bat

Example: C:\Users\sandeep\Desktop\AAAR\pdi-ce-5.3.0.0-213\data-integration\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.

· Set the ID an Name fields and press the ‘ok’ button.

· Connect with the login ‘admin’ and password ‘admin’ to test the connection

· 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

Step-4:

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

· Make sure you have java7 installed

· Download the Pentaho BI-Server 5(biserver-ce-5.3.0.0-213) package Unzip biserver-ce-5.3.0.0-213.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/ (Optional)

· Start this application by running - start-pentaho.bat located at biserver-ce-5.3.0.0-213\biserver-ce OR from startup.bat of tomcat located at - biserver-ce-5.3.0.0-213\biserver-ce\tomcat\bin

· Once its started then go to - http://<server>:8080/pentaho and login with admin user. UID: admin PWD: password

Step-4:

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 : 1121

§ 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.3.0.0-213/data-integration

· Go To AAAR Reports Home

· Configure Alfresco with Following Details and Save

· Configure A.A.A.R Data Mart with following details and Save

· Configure Pentaho Data Integration with Following Details

i.e <Pentaho data integration folder>/data-integration

· Save your data

· Click Install AAAR tab then give Pentaho login=admin and Pentaho password=password click on Install

· Check your logs

· After successful installation, Go to Tools -> Refresh -> CDA cache

· Click Use

· Here you will find

§ Extract: Get audit data from alfresco to Data mart

§ Publish: Upload report to alfresco

§ Analyze: Analyze data from dashboard

Step-5:

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

i.e C:\Users\sandeep\Desktop\AAAR\biserver-ce-5.3.0.0-213\biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\script\AAAR_Extract.bat

see the following directory:

( 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

i.e

C:\Users\sandeep\Desktop\AAAR\pdi-ce-5.3.0.0-213\data-integration> kitchen.bat /rep:"AAAR_Kettle" /job:"Get all" /dir:/Alfresco /user:admin /pass:admin /level:Basic

See directory:

Step-6:

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

i.e

C:\Users\sandeep\Desktop\AAAR\biserver-ce-5.3.0.0-213\biserver-ce\pentaho-solutions\system\AAAR\endpoints\kettle\script\AAAR_Publish.bat

See Directory:

( 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

i.e

C:\Users\sandeep\Desktop\AAAR\pdi-ce-5.3.0.0-213\data-integration> 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

Observe Following Screenshots in Alfresco: