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!

[karmo]

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
‍‍‍‍‍‍‍‍

[karmo]

any more about this? Please

[romschn]

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.

[lina]

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
      }