topic Re: Help with Auditing SQL in Alfresco Archive

Help with Auditing SQL

karmo — Mon, 26 May 2014 11:05:11 GMT

Hi! I'm Carlos, from Barcelona (sorry about my bad english)I'm triying to find a way for extract audit data from the SQL Database (tables: alf_audit_entry, app and model) but I have no idea how.Anybody have some SQL query from extract that data? (like "Select XXX from XXXX) I'm using pgAdmin III for

Re: Help with Auditing SQL

marsbard — Mon, 26 May 2014 11:43:58 GMT

Have you seen AAAR? It seems like it might do what you are trying to do.

https://addons.alfresco.com/addons/alfresco-audit-analysis-and-reporting-aaar

Re: Help with Auditing SQL

karmo — Mon, 26 May 2014 11:52:37 GMT

I have to install Pentaho???

Re: Help with Auditing SQL

marsbard — Mon, 26 May 2014 13:58:58 GMT

Yes you will need to install Pentaho as described here: http://fcorti.com/alfresco-audit-analysis-reporting/aaar-how-to-install/aaar-prerequites/

Re: Help with Auditing SQL

karmo — Mon, 26 May 2014 14:13:31 GMT

But I want to get de code for launch query's directly to alfresco's BD, no to install another software. It's possible?

For example, I want the query for do something like this:

Select User, Time, etc… from Audit_Table, where Action = Connet (pseudocode…)

For obtatin data like audit-control file.

Re: Help with Auditing SQL

karmo — Mon, 26 May 2014 14:21:42 GMT

I have this tables on Alfresco's BD, connecting by pgAdminIII:

· alf_audit_app
· alf_aduit_model
· alf_audit_entry

I have to make joins with another tables for extact information like "The user XXX have been connect the day XXX at XXXX time".
Or:
"The user XXX have been create the document XXX on XXX SITE"

It is possible?

(Sorry sorry sorry about my very bad english)

Re: Help with Auditing SQL

karmo — Mon, 26 May 2014 16:11:12 GMT

any idea?

Re: Help with Auditing SQL

mrogers — Tue, 27 May 2014 10:36:42 GMT

You should perhaps use the public API for audit instead. I got some of the way accessing via SQL, (so its possible) but its rather involved and will tie your code to the private implementation details of alfresco.

The audit API is the intended interface to use.

Also which events are audited is configurable so make sure you are producing the audit in the first place.

Re: Help with Auditing SQL

karmo — Tue, 27 May 2014 11:05:12 GMT

Thank You @mrogers for the reply.

My problem is that I want to get a query result for then do grahps and plots, like CSV file. When I use the API I only obtatin a plain text like json file, I can't use this source for do plots or filters.

Can you understand me? I'm not sure

Re: Help with Auditing SQL

mrogers — Tue, 27 May 2014 11:07:20 GMT

O.K. I understand. Sorry I can't help with the sql.

Re: Help with Auditing SQL

karmo — Tue, 27 May 2014 11:11:23 GMT

For example, with API I get some like this:


   "count":1,
   "entries": 
   [
      {
         "id":177,
         "application":AuditExampleExtractors,
         "user":admin,
         "time":"2010-09-20T20:18:52.761+01:00",
         "values":
null
      }
‍‍‍‍‍‍‍‍‍‍‍‍‍

But I want to get some like this:


User   | Time       | Action  | File
————————————
admin  | 2010-09-20 | READ    | NewDoc.txt
admin1 | 2010-09-20 | READ    | NewDoc.txt
admin2 | 2010-09-20 | READ    | NewDoc.txt
admin3 | 2010-09-20 | READ    | NewDoc.txt
‍‍‍‍‍‍‍‍

Re: Help with Auditing SQL

karmo — Thu, 29 May 2014 08:58:11 GMT

any more about this? Please

Re: Help with Auditing SQL

romschn — Thu, 29 May 2014 11:16:35 GMT

Take a look at tomcat\webapps\alfresco\WEB-INF\classes\alfresco\ibatis\org.hibernate.dialect.Dialect\audit-common-SqlMap.xml which contains the queries to fetch the audit results from the database.
Take a look at id="select_AuditEntriesWithoutValues" and id="select_AuditEntriesWithValues" for the SQL queries to fetch the audit results.
You may want to take a look at org.alfresco.repo.web.scripts.audit.AuditQueryGet.java - backend class for audit webscript to get understanding of how the values from the query are retrieved and presented as the response of the webscript when executed.

Hope this helps.

Re: Help with Auditing SQL

lina — Thu, 08 Jun 2017 15:37:45 GMT

How could you retreive this data with Api ??

 "count":1,
   "entries": 
   [
      {
         "id":177,
         "application":AuditExampleExtractors,
         "user":admin,
         "time":"2010-09-20T20:18:52.761+01:00",
         "values":
null
      }