Pentaho-solutions System Logs Audit Pentaho Audit Log.log

1. Audit user login and report usage

   Is there a simple way to get the user login and logout time and see which reports the user opens and at what time? I want to put this data in an audit table and report over it.

   I've looked at the PentahoAuditLog.log in pentaho-solutions/system/logs/ . But that's not very clear and simple...
   I've also read that the function to audit directly in the database is not in the CE server.
   http://wiki.pentaho.com/display/Serv...uditEntry+Node

   

   ---

2. 

   In the CE you'll have to parse that file.

   The Enterprise Edition logs auditing information to database and has a large set of predefined auditing reports.

   

   ---

3. 

   Is there no way to configure something like the AuditSQLEntry as described in http://wiki.pentaho.com/display/Serv...uditEntry+Node

   There's an audit_sql.xml file in biserver-ce/pentaho-solutions/system which seems to indicate that it's possible.

   I tried changing
   <bean id="IAuditEntry" class="org.pentaho.platform.engine.services.audit.AuditFileEntry" scope="singleton" />

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

   in pentahoObjects.spring.xml but then it won't start up (probably because there's no PRO_AUDIT table in my hibernate / Hypersonic DB

   

   ---

4. parsing audit file

   well I can parse that file, but I did a quick look and it wasn't very clear what all that lines mean... If the PRO version simply parse that in a table, it isn't very usefull to me. Is there some documentation available about the codes and colums used in the file?

   

   ---

5. 

   I don't know what the column types need to be,

   BUT...

   By changing
   biserver-ce\pentaho-solutions\system\pentahoObjects.spring.xml

   Code:

   --- pentahoObjects.spring.xml     +++ pentahoObjects.spring.xml @@ -27,3 +27,3 @@    <!-- <bean id="IRuntimeRepository" class="org.pentaho.platform.repository.runtime.RuntimeRepository" scope="session" /> --> -  <bean id="IAuditEntry" class="org.pentaho.platform.engine.services.audit.AuditFileEntry" scope="singleton" />  +  <bean id="IAuditEntry" class="org.pentaho.platform.engine.services.audit.AuditSQLEntry" scope="singleton" />     <bean id="IUITemplater" class="org.pentaho.platform.web.http.WebTemplateHelper" scope="singleton" />

   And changing audit_sql.xml appropriately, it *WILL* populate a table.

   I created a table in my Hibernate as:

   Code:

   CREATE TABLE PRO_AUDIT (   JOB_ID VARCHAR(60) , INST_ID VARCHAR(60) , OBJ_ID VARCHAR(60) , OBJ_TYPE VARCHAR(60) , ACTOR VARCHAR(60) , MESSAGE_TYPE VARCHAR(60) , MESSAGE_NAME VARCHAR(60) , MESSAGE_TEXT_VALUE VARCHAR(60) , MESSAGE_NUM_VALUE VARCHAR(60) , DURATION VARCHAR(60) , AUDIT_TIME VARCHAR(60) ) ;

   And then made the above change to biserver-ce\pentaho-solutions\system\pentahoObjects.spring.xml

   and did not receive errors in startup, and the table has entries in it.

   Now... That said, there's several entries for every action, and I haven't found a way to filter what gets logged... so this table will get VERY LARGE, VERY FAST.

   eg.

   Code:

   JOB_ID    INST_ID    OBJ_ID    OBJ_TYPE    ACTOR    MESSAGE_TYPE    MESSAGE_NAME    MESSAGE_TEXT_VALUE    MESSAGE_NUM_VALUE    DURATION    AUDIT_TIME org.pentaho.reporting.platform.plugin.ReportContentGenerator    93D77E711371135BEA36D7EC21602FB9    bi-developers/reporting/NamesCalls.prpt    org.pentaho.reporting.platform.plugin.ReportContentGenerator    joe    instance_start    REPORT: null            0    2009-09-23 11:13:52.372000000 org.pentaho.reporting.platform.plugin.ReportContentGenerator    93D77E711371135BEA36D7EC21602FB9    bi-developers/reporting/NamesCalls.prpt    org.pentaho.reporting.platform.plugin.ReportContentGenerator    joe    instance_end    REPORT: null            1.0629999637603759765625    2009-09-23 11:13:53.435000000

   

   ---

6. 

   Originally Posted by drapsag

   Is there a simple way to get the user login and logout time and see which reports the user opens and at what time? I want to put this data in an audit table and report over it.

   Having the data in a table and run a few reports (on the BI server - not reports on the Audit Data), here's what I can tell you:

   * HTTP Session Starts show, but do not have a user tied to it. These can be linkined using a report / PDI process.
   * XActions and PRPT runs are shown.
   * Log Out / Session Timed Out message do not seem to be logged.

   

   ---

7. 

   Looks like it isn't very usefull. I will implement it and see if I can use it.

   

   ---

8. 

   Thanks for the useful information.

   I had to modify the PRO_AUDIT table a little bit by extending the job_id field and converting the duration to a real and audit_time to a timestamp. As follows:

   CREATE TABLE pro_audit
   (
   job_id character varying(120),
   inst_id character varying(60),
   obj_id character varying(120),
   obj_type character varying(120),
   actor character varying(60),
   message_type character varying(60),
   message_name character varying(60),
   message_text_value character varying(60),
   message_num_value character varying(60),
   audit_time timestamp without time zone,
   duration real
   )

   

   ---

9. 

   If it helps, we've just been running the following at our company to see who's been doing what...

   select S.user_id, I.content_id as "xaction", count(*) as "count"
   from INSTANCES I , SESSIONS S
   where S.session_id=I.session_id
   and I.start>='2009-09-29'
   and S.start>='2009-09-29'
   group by S.user_id, I.content_id
   order by S.user_id, "count" desc;

   The pre-canned queries that are available as part of the EE edition in the enterprise console, leave a little to be desired. IMHO, a short sprint/iteration focusing on revamping that is warranted.

   Peter

   

   ---

10. 

    Peter:

    Thanks for the feedback. Are INSTANCES and SESSIONS enterprise edition tables? I don't see them in my hibernate DB.

    Thanks

    Phil

    

    ---

11. 

    Hi,

    I'm using Pentaho CE 3.6.0, I need to Audit user activity, there some documentation about?

    Thanks,

    NL

    

    ---

Pentaho-solutions System Logs Audit Pentaho Audit Log.log

Source: https://forums.pentaho.com/threads/72457-Audit-user-login-and-report-usage/

Share :