Help with Auditing SQL

[karmo]

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 connect whit de Postgress BD.

TY!

[marsbard]

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

[karmo]

I have to install Pentaho???

[marsbard]

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

[karmo]

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.

[karmo]

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)

[karmo]

any idea?

[mrogers]

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. 

[karmo]

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

[mrogers]

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