nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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!

Find more posts tagged with

Comments

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.

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
      }