Command & Audit Logging to Database

Version 2

    If you are Teiid user I am sure you have looked into command logging and/or Audit logging to check what kind of commands that Teiid is receiving and sending to source queries. Typically these log details are captured in file for further processing to check for number of queries processed or average time taken for processing a query etc. This article show a way to log these events into database for further processing or for compliance reasons etc.


    Teiid uses logging framework provided by JBoss EAP, previously the user had to implement the logging handler to perform the task of logging the events to a database.   But now, Teiid has added that ability, so the user no longer has to implement the solution, it is provided from Teiid community as an extension project. To make use of this feature, follow the steps defined below

     

    Pull the code from the git repo: https://github.com/teiid/teiid-extensions

     

    git clone git@github.com:teiid/teiid-extensions.git
    cd teiid-extensions
    mv clean install
    
    


    then unzip the "build/target/build-${version}-dist.zip, on to JBoss EAP 6.1.Alpha installation. Then start JBoss AS server,

     

    Now configure the logging:

     

    1.  make sure property substitution is enabled

     

    - edit bin/jboss-cli.xml and make sure the following property is set to true:

     

    <resolve-parameter-values>false</resolve-parameter-values>

     

    2.  execute script 

     

    cd /bin
    ./jboss-cli.sh --file=scripts/teiid-add-database-logger.cli --properties=scripts.teiid-logger-ds.properties
    
    


    By default the H2 database is chosen to be target database to contain the log entries, however to configure a different than the default,


    * Install the JDBC driver for your database in JBoss EAP.

    * Edit "scripts/teiid-logger-ds.properties", to update properties like jdbc-url, driver-name, user and password

    * Then execute the above script


    How does this work?


    The above solution uses asynchronous logging handler, to delegate a logging event call to a stateless bean which is configured with a JPA entity to handle update of database. Since it uses async-handler for logging the performance of your Teiid query will not be affected by this.


    Since this is using JPA, you can even direct the events to big data data sources. We intend to even provide Flume based handler in near future based community interest.


    We will include the distribution of above as a separate download in next Teiid 8.8 release. Meanwhile you can follow above procedure and let us know if you have any questions.


    Thanks


    Ramesh..