US and Worldwide: +1 (866) 660-7555
Results 1 to 8 of 8

Thread: How to send PentahoAuditLog to a table?

  1. #1
    Join Date
    Aug 2007
    Posts
    151

    Default How to send PentahoAuditLog to a table?

    Guys,


    Is it possible to log the BI Server audit trail to a database? I've been searching for this for a while but found nothing so far. If it is possible (as it seems to be, as this post http://forums.pentaho.org/showthread.php?p=160465 hints it), how is it done? I've checked the pentahoObjects.spring.xml but I'm clueless about how to change anything in there...

    I didn't want to write a PDI job to get data from the audit log, that wouldn't be... elegant enough for Pentaho.

    Best regards,


    Fábio

  2. #2
    Join Date
    Apr 2007
    Posts
    1,997

    Default

    there's an option in the admin section to load your audit logs into the "hibernate" database.

    If you dont see that option, then the platform doesnt think it's in enterprise mode...

    It is a transformation. And you can schedule it.

    But i guess you may be asking about dropping the file alltogether and writing directly to the db?

  3. #3
    Join Date
    Aug 2007
    Posts
    151

    Default AuditLog right into DB - that is it!

    Codek,


    Thanks for the prompt answer. Yes, I would expect there is a setting to write the log right into a db table. On the other hand, I have already created a transformation to do this, but I didn't want to use it. As my BI Server is not "enterprise" I believe I would have to stick to the ktr way.

    BTW, do you know if it is safe to load the audit trough Kettle and empty the file?

    Regards,


    Fábio

  4. #4
    Join Date
    Apr 2007
    Posts
    1,997

    Default

    take a look at pentaho.xml in particular this section:

    <IAuditEntry scope="session">org.pentaho.core.audit.AuditFileEntry</IAuditEntry>

    There is a class called AuditEntry which may or may not log direct to db. Thats the area to be looking in though.

    Thats about all i know i'm afraid.

  5. #5
    Join Date
    Aug 2007
    Posts
    151

    Default It is in the pentahoObjects.spring.xml

    Codek,

    Thanks for the hint. Just to make it sure, this line is in the pentahoObjects.spring.xml.

    Best regards,

    Fábio

  6. #6

    Default

    I just spent a day on this, but finally got it to work so I thought I'd share. It's actually very simple:

    1. In your hibernate database, create the following table. (You might want to add some indexes at some point to speed up reporting.)

    CREATE TABLE `PRO_AUDIT` (
    `JOB_ID` varchar(255) NOT NULL,
    `INST_ID` varchar(128) default NULL,
    `OBJ_ID` varchar(128) default NULL,
    `OBJ_TYPE` varchar(255) default NULL,
    `ACTOR` varchar(45) default NULL,
    `MESSAGE_TYPE` varchar(255) default NULL,
    `MESSAGE_NAME` varchar(128) default NULL,
    `MESSAGE_TEXT_VALUE` varchar(128) default NULL,
    `MESSAGE_NUM_VALUE` bigint(20) unsigned default NULL,
    `DURATION` decimal(7,2) default NULL,
    `AUDIT_TIME` datetime
    ) ENGINE=myisam DEFAULT CHARSET=utf8;

    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. Restart your application server (Tomcat, Jboss, etc)


    I found the PRO_AUDIT table HAS to be in the hibernate database. The options to specify the database connection in pentaho.xml and audit_sql.xml are ignored. YMMV

  7. #7

    Default

    Quote Originally Posted by TomWall View Post
    I just spent a day on this, but finally got it to work so I thought I'd share. It's actually very simple:

    1. In your hibernate database, create the following table. (You might want to add some indexes at some point to speed up reporting.)

    CREATE TABLE `PRO_AUDIT` (
    `JOB_ID` varchar(255) NOT NULL,
    `INST_ID` varchar(128) default NULL,
    `OBJ_ID` varchar(128) default NULL,
    `OBJ_TYPE` varchar(255) default NULL,
    `ACTOR` varchar(45) default NULL,
    `MESSAGE_TYPE` varchar(255) default NULL,
    `MESSAGE_NAME` varchar(128) default NULL,
    `MESSAGE_TEXT_VALUE` varchar(128) default NULL,
    `MESSAGE_NUM_VALUE` bigint(20) unsigned default NULL,
    `DURATION` decimal(7,2) default NULL,
    `AUDIT_TIME` datetime
    ) ENGINE=myisam DEFAULT CHARSET=utf8;

    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. Restart your application server (Tomcat, Jboss, etc)


    I found the PRO_AUDIT table HAS to be in the hibernate database. The options to specify the database connection in pentaho.xml and audit_sql.xml are ignored. YMMV
    I was able to log to SQLServer database with pentaho 6.1, changing the following parameters in the audit_sql.xml:

    Also changed the audit table name.

    <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.microsoft.sqlserver.jdbc.SQLServerDriver</driverCLASS>
    <driverURL>jdbc:sqlserver://youServerNameortNumber;DatabaseName=yourDb</driverURL>
    <userid>yourUser</userid>
    <password>yourPassword</password>
    </auditConnection>


    Now I am looking how to ad username to saiku audit records since it always logs "existinguser" to the ACTOR column.

  8. #8

    Default

    I was able to log to SQLServer database with pentaho 6.1, changing the following parameters in the audit_sql.xml:

    Also changed the audit table name.

    <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.microsoft.sqlserver.jdbc.SQLServerDriver</driverCLASS>
    <driverURL>jdbc:sqlserver://youServerNameortNumber;DatabaseName=yourDb</driverURL>
    <userid>yourUser</userid>
    <password>yourPassword</password>
    </auditConnection>


    Now I am looking how to ad username to saiku audit records since it always logs "existinguser" to the ACTOR column.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •