Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Pentaho AuditLog to a table in MySql5

  1. #1

    Default Pentaho AuditLog to a table in MySql5

    I´m using Pentaho Pentaho 7.1.0.0.12. (I have download "pentaho-server-ce-7.1.0.0-12.zip" and unzip on my computer.)

    I want to create a cube accesses and execution times about Pentaho users.

    I checked that it´s possible to create a table with the neccesary data in this post: http://forums.pentaho.com/showthread...Log-to-a-table

    The DDBB origin of my Cube is a MYSQL5 DDBB, so I did this:

    1.-Create the table in MYSQL:

    CREATE TABLE `PRO_AUDIT` (
    `hbm_id` bigint(20) DEFAULT NULL,
    `start` datetime DEFAULT NULL,
    `service_id` varchar(200) DEFAULT NULL,
    `session_id` varchar(200) DEFAULT NULL,
    `content_id` varchar(200) DEFAULT NULL,
    `engine_id` varchar(200) DEFAULT NULL,
    `user` varchar(200) DEFAULT NULL,
    `instance_id` varchar(200) DEFAULT NULL,
    `duration` decimal(19,2) DEFAULT NULL,
    `status` varchar(255) DEFAULT NULL,
    `hour` bigint(20) DEFAULT NULL,
    `dayOfWeek` bigint(20) DEFAULT NULL,
    `message` varchar(255) DEFAULT NULL,
    `dayOfMonth` bigint(20) DEFAULT NULL,
    `year` bigint(20) DEFAULT NULL,
    `month` bigint(20) DEFAULT NULL,
    `count` bigint(20) DEFAULT NULL
    )
    ;

    2.-Update the pentahoObjects.spring.xml file to write the audit info to the database.

    This file is located under pentaho-solution/system. Here's the change...Change this:
    <bean id="IAuditEntry" class="org.pentaho.platform.engine.services.audit.AuditFileEntry" scope="singleton" />


    To This:
    <bean id="IAuditEntry" class="org.pentaho.platform.engine.services.audit.AuditSQLEntry" scope="singleton" />

    3.- In the folder: pentaho-server-ce-7.1.0.0-12\pentaho-server\pentaho-solutions\system\dialects\mysql5

    I have changed the audit_sql.xml file from:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <audit-sql>
    
    
      <auditConnection>
        <insertSQL>
        INSERT INTO PRO_AUDIT (JOB_ID, INST_ID, OBJ_ID, OBJ_TYPE, ACTOR, MESSAGE_TYPE, MESSAGE_NAME, MESSAGE_TEXT_VALUE, MESSAGE_NUM_VALUE, DURATION, AUDIT_TIME) values (?,?,?,?,?,?,?,?,?,?,?)
        </insertSQL>
        <JNDI>Hibernate</JNDI>
      </auditConnection>
    
    
    </audit-sql>

    To this:

    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <audit-sql>
    
    
    <auditConnection>
    <insertSQL>
    INSERT INTO PentahoAuditLogV6 (JOB_ID, INST_ID, OBJ_ID, OBJ_TYPE, ACTOR, MESSAGE_TYPE, MESSAGE_NAME, MESSAGE_TEXT_VALUE, MESSAGE_NUM_VALUE, DURATION, AUDIT_TIME) values (?,?,?,?,?,?,?,?,?,?,?)
    </insertSQL>
    <JNDI>JDBC</JNDI>
    <driverCLASS>com.mysql.jdbc.Driver</driverCLASS>
    <driverURL>jdbc:mysql://192.168.108.4:3306;DatabaseName=Controlling_CM</driverURL>
    <userid>root</userid>
    <password>myPassword</password>
    </auditConnection>
    
    
    </audit-sql>


    According to my Data Source in Pentaho:

    Name:  BBDD.jpg
Views: 135
Size:  18.8 KB

    I have restart Pentaho , and no one insert is done in my MySql DDBB.

    What Im doing bad??

    Thanks a million in advance.

  2. #2
    Join Date
    May 2016
    Posts
    282

    Default

    Read the catalina.out log, but I'm pretty sure your table is not correctly created, you don't have columns named job_id, inst_id, etc with the correct datatypes, or you have not told us some more details about your configuration, you don't call the pro_audit table you created (which doesn't have those columns according with your description) or the PentahoAuditLogV6 table you don't describe lacks some column.
    I would check first the configuration of audit_sql.xml, in 7.1 or 7.0 there was a new column, AUDIT_ID.

    You don't have to modify the audit_sql.xml file in pentaho-server-ce-7.1.0.0-12\pentaho-server\pentaho-solutions\system\dialects\mysql5, you have to take this file (so you use the correct insert statement with the correct columns for your pentaho version) and copy it to pentaho-server-ce-7.1.0.0-12\pentaho-server\pentaho-solutions\system\, is the file in system the one you have to modify so it has the correct table and column names, but you can change the structure of the insert statement, it has to have the correct number of columns with the correct column format.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  3. #3
    Join Date
    May 2016
    Posts
    282

    Default

    This is my pro_audit table structure for Pentaho 7.1:
    Code:
    AUDIT_ID BIGINT
    JOB_ID VARCHAR(300)
    INST_ID VARCHAR(150)
    OBJ_ID VARCHAR(150)
    OBJ_TYPE VARCHAR(300)
    ACTOR VARCHAR(100)
    MESSAGE_TYPE VARCHAR(300)
    MESSAGE_NAME VARCHA(150)
    MESSAGE_TEXT_VALUE TEXT
    MESSAGE_NUM_VALUE BIGINT
    DURATION DECIMAL (7,2)
    AUDIT_TIME DATETIME
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  4. #4

    Default

    Thanks a million, Im checking it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.